1

I've written a web app in ASP.NET that stores all of it's data in SQL Server 2008r2 and runs everything via stored procedures. The app is collecting data for a department who wants 2-3 pieces of that data pushed to an old app they use which has a Microsoft Access 2003 backend (.MDB file). My thought was to create a linked server to Access so my app can push everything to SQL server and it can handle it from there (seemed simple enough). Based on info from MSDN (here and here), I was using the following SQL command to create the linked server:

EXEC sys.sp_addlinkedserver  @server = N'CMPtesting' , 
    @datasrc = N'\\srv.local\SHAREDOCS\MPS\CMPdata.mdb' ,
    @srvproduct = N'Access' , 
    @provider = N'Microsoft.Jet.OLEDB.4.0'

This completes successfully, and I can see my CMPtesting server listed under Server Objects -> Linked Servers. However, if I try to verify the server using this:

SELECT name FROM [CMPtesting].master.sys.databases

        or even just view the tables, I get an 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)

Researching that error led me to "SQL to Access linked server" which suggested installing some different Access drivers and then using @provider = N'Microsoft.ACE.OLEDB.12.0' instead of the Jet driver. Again, this completes successfully, but attempting to view the tables produces a different error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CMPtesting". (Microsoft SQL Server, Error: 7302)

I know this is not simply an invalid driver error, because if I just make up a provider name I get this instead:

The OLE DB provider "foobar" has not been registered. (Microsoft SQL Server, Error: 7403)

I'm not sure what else to try because all my searches just turn up more rehashing of how to run sp_addlinkedserver without any additional details or help with these errors.

Community
  • 1
  • 1
techturtle
  • 2,519
  • 5
  • 28
  • 54
  • First off, I'm sorry you have to work with Access 2003...But have you looked into alternatives, maybe SSIS? You might have some better luck. – Joe Enos Feb 22 '13 at 20:47
  • Have not looked into SSIS. With as minor as these changes should be, adding in a new tech that is not used in any other part of my app seems like overkill. With all this trouble, I'll probably just end up going direct from the app to the MDB, but even then it'd be nice to write and test my queries in SSMS rather than Access's messy query builder. – techturtle Feb 22 '13 at 21:07
  • Microsoft.Jet.OLEDB.4.0 is not compatible with 64-bit, this could be causing issues for you assuming you are running the 64-bit version of SQL 2008 R2. [This post](http://stackoverflow.com/questions/122583/64-bit-alternative-for-microsoft-jet/3049332#3049332) has some links that dive into the issue a bit, and possible workarounds. – supergrady Feb 23 '13 at 02:45

0 Answers0