TO CLARIFY: The linked server feature in x64 versions of sql server is NOT working against access/ace/accdb files using ANY driver (ACE 64-or 32-bit).
NOTE: The issue has been confirmed with Jin Chen at Microsoft.
This is on windows 7 x64. I keep getting an error when setting up a linked server in sql server 2008 R2 x64 to a microsoft access database file (mdb or accdb file). I have Access 2010 x64 installed (as part of ms office 2010 x64). (The exact same failure is also caused with 32 bit access 2010 installed, on a win7 x64 system.)
This is after right clicking linked servers in ssms and selecting "add new linked server," or using the sp procedure in "steps to reproduce" below.
These settings do NOT work (they are supposed to):
Provider (from dropdown): Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: Access
Data source: C:\foopath\foo.mdb --also fails with foo.accdb
Provider string: Microsoft.ACE.OLEDB.12.0
The error is:
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 tried the jet 4.0 connection string and got this error 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 apartment mode. (Microsoft SQL Server, Error: 7308)
I simply don't have any connection string that works for adding a linked server to access 2010. Nothing in BOL or on the support threads is working for me.
I can get linked servers to work against *Excel* files, but something seems completely broken with *access* database files (no linked server syntax that I can come up with works against access database files). The working syntax for excel spreadsheet files is as follows: (--using ad hoc distributed query, which can be converted to linked server using the sp in Steps To Reproduce):
SELECT * FROM OPENROWSET
'SELECT * FROM [fooworksheetname$]');
To summarize, the issue can be reproduced in three ways:
1. via the ssms ui for adding a linked server (right-clicking on linked servers in ssms explorer window and selecting add linked server).
2. via the system sproc (sp_addlinkedserver), see below "steps to reproduce".
3. via the SELECT * FROM OPENROWSET method, if the adhocdistributedquery advanced option is turned on with sp_configure.
Note also that in sql server 2008 R2 x64 this works properly against Excel files and fails only against Access mdb or accdb database files (it used to work against access database files in sql server 2005 and possibly also in 2008--I forget if I tested in sql 2008). This issue occurs when access 2010 database engine files are installed, 32bit or 64bit.