I am trying to create a linked server to an 32 bit 2010 access database inside of SQLEXPRESS 2008 R2 64bit. Everything I've read has pointed me at the download : Access Database Engine
I've downloaded the 32 bit version (64 bit tells me my office is 32 bit) and installed it; however, the drop down in SQLEXPRESS > Create New Linked Server does not come back with an option for Microsoft Office 12.0 Access Database Engine OLD DB Provider.
I tried createing the linked server via sp_createlinkedserver but when I run a test connection on that server I get the error
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)
Does this mean that it found the driver I needed? If so how do I get it to run with the linked server?
Best Answer
For a 64bit build of SQL to use the Access drive you'll need to install the 64bit version of the Access drive. If you have the 32bit version of Office installed you'll need to either remove that and install the 64bit version of office or uninstall the 64bit build of SQL and install the 32bit build.