TN WW133 How to query an Access database from MS SQL Server

Description

This article shows to how query a Microsoft Access Database from Microsoft SQL.

• Author: Mike Viteri
• Published: 11/12/2015
• Applies to: Microsoft SQL 

Details

You can only query Access from MSSQL if they are the same architecture. Such as Access and MSSQL both have to be 32-bit or 64bit. If they are different the query will NOT work.

This guide assumes the above and you also have MSSQL and Access Database.

1. First you will need to open up your SQL Server Management Studio. Then you will need to browses to the Server Objects folder. Expand that folder and then right click on “New Linked Server”
   
2. Next you will fill out the Linked Server. This information will allow you to connect to your Access database. You will have to specify a Linked Server name. I called this example "MYSERVER". Under Data source you will need to put the location and name of your Access database file. Provider and Product name should match below. Once filled out click OK.
   
3. If the connection is successful you will see your Linked Server name under the Server Objects Linked Server.
   
4. You should be able to expand your linked server and see the tables that exist in your Access database.
   
5. Now you will be able to query your Access database from SQL Server.
   
   The Open Query is used below to select from the Linked Server "MYServer" and the table “Cars”