How do I query an Access 2013 (v15.0) database from SSMS (SQL Server 2008 R2)?

Question

How do I query an Access 2013 (v15.0) database from SSMS (SQL Server 2008 R2)?

Ancient Coder

SSC-Addicted

Points: 404
More actions
November 3, 2015 at 4:00 pm

#74440

Can anyone help me figure out how to query a table in an Access 2013 database from within SSMS?
I can use SSIS to import an Access 2013 table into my SQL Server 2008 R2 database, so I know SQL Server 2008 R2 can query Access ok. Maybe I can somehow use the DTS connection string from my SSIS pkg?
I have tried querying a table in the Access database using OPENROWSET but get the dreaded error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I'm on a Win7 x64 workstation with Office 2013 installed, but I don't want to rely on any Office binaries to make my query because I'll need to replicate the query on an actual server as well. Also, I've seen other people having mixed results using sp_configure*, but that's not an option for me either since it's not allowed on our production servers.
* Example: sp_configure 'Ad Hoc Distributed Queries', 1;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HiTechCoach SSC Veteran Points: 228 More actions · Answer 1

If the data is in the new ACE (.accdb) format yu must install the "Microsoft Office 12.0 Access Database Engine" which is a free download if Access 2007 or later is not installed. The older JET (.mdb format) installed automatically with Windows.

I recommend using the option for: Linking Servers (Click Here) You can link the cces data to the SQL Server/

Ancient Coder SSC-Addicted Points: 404 More actions · Answer 2

Same error when using a linked server.

Odd that SSIS (using a DTS connection string) works but SSMS doesn't. Both are using "Provider=Microsoft.Jet.OLEDB.4.0", too.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Ancient Coder (11/4/2015)
Same error when using a linked server.
Odd that SSIS (using a DTS connection string) works but SSMS doesn't. Both are using "Provider=Microsoft.Jet.OLEDB.4.0", too.

Regardless of what SSIS does, if the SQL Server is a 64 bit machine, then the JET drivers will NOT work. You need to use the ACE drivers in the 64 bit mode on 64 bit machines.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ancient Coder SSC-Addicted Points: 404 More actions · Answer 4

What you say certainly make sense, but then how is the following DTS connection string working (taken from the SSIS pkg saved to file)?

<DTS:Property DTS:Name="ConnectionString">Data Source=C:\Databases\Access\myfile.mdb;OLE DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;</DTS:Property>

The SSIS pkg works and it definitely looks like it's using Jet. Perhaps Jet works under certain conditions?

Ancient Coder SSC-Addicted Points: 404 More actions · Answer 5

More digging, tried using sp_configure to allow ad hoc queries, got a little closer...

Now I'm just getting a generic error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

Here are the 2 queries I've tried (trying oledb and jet):

select top 10 * from opendatasource('Microsoft.ACE.OLEDB.15.0', 'Data Source=C:\Databases\Access\myfile.mdb;OLE DB SERVICES=0;Microsoft.ACE.OLEDB.15.0;Persist Security Info=False;')...MyTable

select top 10 * from opendatasource('Microsoft.ACE.OLEDB.15.0', 'Data Source=C:\Databases\Access\myfile.mdb;OLE DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;')...MyTable

I know the OLEDB is the correct version as I can use the connection string in a C# console app, no problem. I can't seem to get any more error detail from SSMS, so it appears I'm stuck.

Any thoughts?