Query access database from SQL management studio without using linked servers

Question

How do I query a MS Access database directly from SQL Management Studio, without using a linked server?

Ie. something like

SELECT * FROM ["C:\Data\Accessdb.mdb"].[SomeTableInAccessDB]

Obviously this won't work but is there a away to specify the access database details within a sql query?

Gyrus

Answer 1

You can use OPENROWSET or OPENQUERY. For example (per Microsoft's Northwind):

 SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
             'admin';'',Customers)

Adding a linked server just allows ease of configuration, so different processes can use the connection without having to specify connection details. I don't believe a Linked Server actually adds any functionality that can't be obtained through one of the two OPEN options.

Readily

Answer 2

How about OPENROWSET().

Persson

Answer 3

If using 64bit server, use Microsoft.ACE.OLEDB.12.0 as provider:

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
             'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
             'admin';'',Customers)

Shawannashawl