I'm running SQL Server 2008 SP2
(No idea what version of Access wrote the mdb file, but should be either 2008 or the version just prior)
I have an mdb file (historic data) that I need to read. Rather than go out and purchase MS Access, I'd like it if I can connect it to SQL Server as a Linked Server.
I used the following code:
@server = 'access_test',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Users\Public\Documents\xxxxxx.mdb'
Which seemed to connect to the file with no issue. However when I click on the folder that should display the files, I get the following error:
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. (Microsoft SQL Server, Error: 7308)
How do I connect an MS Access database to SQL Server as a Linked Server?