Linked Tables from Access to SQL

[StephHansen]

How do you link tables in an access database to tables in a SQL database? I am relatively new to access in this way.

Thanks in advance! Stephanie Nicholas
Software Engineer
i-Net Strategy

 

[WendellB]

It depends on what technology you are using. If you have Access 97, then you are limited to an ODBC connection which you have to setup for each workstation using the database. The ODBC connection is made in Control Panel, and you specify a SQL Server and the database on that server. You then use the linked table manager to select the tables (and views) in that database.

If you are using Access 2000 or later, you have some additional options. You can use ADO, in which case you can either link as above, or you can connect dynamically at the time you want to work with a given table or view. Another option in 2000 or later is to create an ADP. In that case you are working pretty much in native SQL Server mode from a tables, views and stored procedures, and you use forms a reports designed to deal directly with SQL Server.

In my experinece, most developers are still working primarily with ODBC, as that emulates pretty closely the way that Jet/MDB tables are used. If you are looking for top performance then you might want to investigate the later technology, but we use it pretty sparingly.

 

[cmmrfrds]

If this is a new application, the way to go is with an Access Project (adp) instead of an Access (mdb) application. This eliminates the ODBC layer which only slows down the app. It is there in order to be backward compatible for those working on older apps. You can still create access forms and reports much like in an mdb only the connection is to SQL Server tables. Also, you can create stored procedures on the server which will keep the sql processing on the server and only return the results you will need to work with in forms and reports - more efficient. It is a good idea to do some training in ADO and SQL. Also, make sure you get the client tools loaded on your development PC for sql server. Make sure you have Query Analyzer, an sql server tool, it will be a big help in developing stored procedures and testing SQL code.