SQL linked server to Access Database

Question

I am trying to create a a Linked Server in SQL (SQL 2016) to an access database (2013). I do not want to do anything in access - just simply query a table from SQL Studio Management. I have tried to create an ODBC connection - but I only have the option of 32bit - not 64. But if I create a 32bit (Microsoft Access Driver (*.mdb) and then create a linked server using Microsoft OLE DB Provider for ODBC Driver - Data source I am using the System DSN name I get this message:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry) at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection() at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)

I do have a few other linked servers - but to Oracle DB's. Am I missing something?

Does this answer your question? SQL Server 2012 querying Access 2007 data using OPENROWSET error — June7
– June7, Commented Dec 14, 2020 at 23:45

Albert D. Kallal · Accepted Answer · 2020-12-15 08:05:49Z

2

Well, if your SQL server is running as x64 bits, then you have to install and use a x64 bit copy of Access data engine (ACE).

You can't use MS-access x32 with a x64 bit version of SQL server.

While a x32 or x64 bit client can easy connect to SQL server? That works because that is a socket connection.

But with Access, it is NOT a socket connection FROM sql server to the ACE data engine. There is no "service" you connect to. So this is a in-process external .dll that is consumed and used directly by SQL server to OPEN the accDB file.

Hence the bit size of that process that consumes the ACE data engine MUST match. You can install ACE x64 bits data engine on that server, and then this can work. But SQL server x64 can't use external x32 bit dll's of any kind - including the ACE data engine.

answered Dec 15, 2020 at 8:05

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL linked server to Access Database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related