How to Link a SQL Server to Microsoft Access

by Lysis

Overview

SQL Server and Access databases are both products from Microsoft. Because both of them are developed by the same software developer, it's simple to link the two together. Microsoft SQL Server has an internal stored procedure that allows you to link to an Access database. Once you link the databases, you can use a SQL Server query to retrieve information from the Access database. This makes it easy for developers to use other sources for stored procedures.

Step 1

Open the Microsoft SQL Server Management Studio application. This is installed on client machines or the computer that contains the SQL Server database. The software shortcut is found in the Windows Start menu in the "SQL Server" directory.
Step 2

Log in to the SQL Server with the administrator user name and password. You need admin rights on the SQL Server to link other databases.
Step 3

Right-click the database server name on the left of the window and select "New Query." This opens a new command line window.
Step 4

Enter the following code to link the Access database: exec sp_addlinkedserver '<servername>', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\myAccessDB.mdb' The "<servername>" variable is the name of the machine that has the Access database stored. The next two lines are the ODBC driver information for Microsoft Access, which translates the code used in stored procedures to Access. The last line is the location of the Access database.
Step 5

Press the F5 button to run the link stored procedure. If the link is successful, the following statement is returned by the SQL Server: "Server added."