SQL to Access Linked Server

SQL to Access linked server

I figured it out...

The server running SQL Server is a 64Bit machine. The typical data connectivity component drivers did not work with this machine (ie the download that installs the Microsoft.Jet.OleDB.4.0 as a provider). I had to download the components for Access 2010 which has a 64Bit option.

Download from here

That installs the Microsoft.ACE.OLEDB.12.0 as a provider and I can use the SQL command that BradBenning mentioned in his post.

SQL linked server to Access Database

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.

SQL Server Linked Server Example Query

The format should probably be:

<server>.<database>.<schema>.<table>

For example:
DatabaseServer1.db1.dbo.table1

---

Update: I know this is an old question and the answer I have is correct; however, I think any one else stumbling upon this should know a few things.

Namely, when querying against a linked server in a join situation the ENTIRE table from the linked server will likely be downloaded to the server the query is executing from in order to do the join operation. In the OP's case, both table1 from DB1 and table1 from DB2 will be transferred in their entirety to the server executing the query, presumably named DB3.

If you have large tables, this may result in an operation that takes a long time to execute. After all it is now constrained by network traffic speeds which is orders of magnitude slower than memory or even disk transfer speeds.

If possible, perform a single query against the remote server, without joining to a local table, to pull the data you need into a temp table. Then query off of that.

If that's not possible then you need to look at the various things that would cause SQL server to have to load the entire table locally. For example using GETDATE() or even certain joins. Others performance killers include not giving appropriate rights.

See http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/ for some more info.

Query tables between two different servers in SQL Server

Another way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. To use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources.

Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function.

You need to provide the provider’s name, the connection string and the query as follows:

OPENROWSET(‘providername’,’datascource,’query)

---

Related Topics