This tutorial will cover how to create and reference a Linked Server in SQL Server that allows you to query an Access database. So what is a “Linked Server”? A Linked Server is simply a reference in SQL Server to an outside data source (usually another instance of SQL Server, but in this case…an Access DB).

In this walkthrough I’m using SQL Server 2008R2 and the Access Database was created in Office 2010.

The first thing you need to do in order to reference an Access database is ensure that the proper drivers are in place, so SQL Server and Access know how to talk to each other. You’ll want to download the “Microsoft Access Database Engine 2010 Redistributable” and install it. The Download and installation instructions are located at http://www.microsoft.com/download/en/details.aspx?id=13255 . This will need to be installed on the computer that SQL Server is installed on. I found the install to be very straightforward, consisting of something like NextàNextà Nextà J. Make sure you get the correct version though (x86 vs. 64 bit).

Once installed, Open up SQL Server Management Studio (SSMS) and connect to your instance of SQL Server that you want to create the Linked Server on. Once there, navigate to Server Objects à Linked Servers à Providers à Microsoft.ACE.OLEDB.12.0 (as shown highlighted below)

Right click on the “Microsoft.ACE.OLEDB.12.0” provider and select “Properties” to open up the properties window. Check the box for “Allow inprocess” as shown below and then click OK.

Now all we have left to do is create our linked server. Right click on the Linked Servers folder in the Object Explorer in SSMS and select “New Linked Server”. This should open up a wizard to help us along. Enter the same information shown in the image below, making sure to select the “Microsoft Office 12.0 Access..blah blah blah” as your provider. You’ll also want to change the value of “Data Source” to be the location of your Access db.

Once finished just click ok and if all has gone according to plan, you should see your linked server in the “Linked Servers” folder in SSMS (you might need to refresh the folder).

Now…how do we query our Access DB using T-SQL? As shown above, I named my Linked Server “TEST” and in the Access database that TEST references, there is a table called “People”. If I wanted to query that table I could do so like this:

SELECT * FROM OPENQUERY(TEST, 'SELECT * FROM People')

Now if I wanted to make this available to something like PerformancePoint, I could simply make the above query into a View and reference it like I would any other SQL Server table.

Well, I think that about does it. Hope this helps.