Access as the front end and sql server as the backend

Question

I have some Access tables with many number of fields. I have migrated each access table to 6 or 7 sql server tables. I am using sql server 2008. Now I want to use Access as the front end so that I can enter the data in access but it will be stored in sql server. I know that I have to make a ODBC connection. But I am not sure of how to create a access form to use it as a front-end. I am sorry if it's a basic question...

Answer 1

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

First step is to establish an ODBC connection to your SQL Server database. Then you will "link" the tables in SQL Server to your Access database.

Now, you have an Access database with all the tables that you linked from SQL Server. Those tables still "live" in SQL Server and when you edit them in Access the data will be stored in SQL Server.

You can then build Access forms and reports using these tables just as if the tables were native to Access.

Answer 2

The most versatile way is to use ODBC links to your SQL Server tables and views. That approach allows you the flexibility to 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, and so forth. You can incorporate a broad range of data sources.

If you choose ADP, you will be limited to OLE DB connection to a single SQL Server instance. And you will be essentially locked in to SQL Server. You would not be able to switch the application to a different client-server database without a major re-development effort.

Regarding deployment overhead with ODBC, although you may find it convenient to use a DSN during development, you should convert your ODBC links to DSN-less connections before deployment. That way your user's won't each require the DSN. See Doug Steele's page: Using DSN-Less Connections