SQLServerPedia

Q: How can I link a SQL Server database to MS Access using link tables in MS Access?

Bryan Oliver says: Here’s how to use Microsoft Access to query SQL Server Data – check it out:

Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task, Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)

Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.

You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.

Linked server

You can add a Access database to a SQL Server database. Following steps are -

(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.

– OR –
Issue this statement in QA of SQL Server-

EXEC sp_addlinkedserver @server = ‘DBName’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’

You have added it as linked server now. Then, use full qualified name to issue your statements.

More Information on Access and SQL Server

Here’s two links to articles on our wiki and blogs:

• Using Linked Servers to Query Access Databases in SQL Server
• How can I synchronize data between MS Access and SQL Server databases?