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?
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.
How about OPENROWSET().
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)
