Windows Server 2008 R2 Standard 64bit.
Sql Server 2008 R2 Express with Advanced Services 64bit.
I am converting an Access 2000 database to SQL Server. I developed a script that creates a linked server to import the data into SQL Server and manipulate it into the new structure. All works well on my 32 bit developement machine.
We just had the above new server built. The linked server is created without error, but trying to access the data in the Access database results in the 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."
In searching, it seems that there is no 64 bit provider for jet.
Once the new system is up and running, the application also requires some regular imports of Access and Excel files, so this is not a one-time problem.
I can convert all Access and Excel files to 2007 version (ACE) if needed. However, I did try adding a linked server using the ACE provider and got the following error when trying to access the linked server
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. (Microsoft SQL Server, Error: 7403)
What are my options?
Do I need to uninstall 64bit SQL and install 32bit?
View Complete Post