Access to server and sql server as backend

Question

I have several access tables with a lot of fields. I moved each access table to 6 or 7 sql server tables. I am using sql server 2008. Now I want to use Access as a front-end so that I can enter data in access, but it will be stored on sql server. I know that I need to make an ODBC connection. But I'm not sure how to create an access form to use it as an interface. I apologize if this is the main question ...

Answer 1

You will probably want to start with an empty Access database (because the table structures and any existing forms and reports will not match what you created on the SQL server).

The first step is to establish an ODBC connection to the SQL Server database. You will then “link” the tables in SQL Server to the Access database.

You now have an Access database with all the tables associated with SQL Server. These tables still live in SQL Server, and when you edit them in Access, the data will be stored in SQL Server.

You can then create access forms and reports using these tables in the same way as if the tables were native to Access.

Answer 2

The most universal way is to use ODBC links for your SQL Server tables and views. This approach allows you to flexibly link to other ODBC data sources, tables in other Jet / ACE database files, create Jet / ACE tables locally in your database, link to Excel spreadsheets, etc. You can use a wide range of data sources.

If you choose ADP, you will be limited to connecting OLE DB to a single instance of SQL Server. And you will essentially be locked in SQL Server. You cannot switch the application to another client-server database without significant re-development efforts.

Regarding the overhead of deploying with ODBC, although it may be convenient for you to use DSN during development, you should convert your ODBC links to DSN-less connections before deployment. Thus, your user will not require a DSN. See Doug Steele Page: Using DSN-Less Connections

Answer 3

Well, you can create an ODBC connection. You can also create an ADODB connection. If your goal is to update or modify the SQL database, both connections will do the trick.

Now, I think you need to familiarize yourself with the relevant objects. These should be tables, queries, commands, etc., which will allow you, for example, to create recordsets from SQL queries ... As soon as you deal with this, you can, for example, assign a recordset to a form via the Set myForm.recordset = myRecordset.open ... method Set myForm.recordset = myRecordset.open ...