I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file). (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).
What are the proper connection string settings? (This is after right clicking linked servers in ssms and selecting "add new linked server") The below settings don't work:
Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider
Product name: Access
Data source: C:\foopath\foo.mdb --also doesn't work for foo.accdb
Provider string: Microsoft.ACE.OLEDB.12.0
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303)
I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver).
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa
View Complete Post