SQL to Access linked server

Question

I am trying to add a linked server to a Access database. I am using the following SQL code to do this.

exec sp_addlinkedserver 
@server = 'Test',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\\srv\public$\CM Database\Data\sysConfig_dat.mdb'
go
EXEC sp_addlinkedsrvlogin Test, FALSE, Null, Admin, Null

but when i run this...

select * from Test...tblProduct

i get this 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. what am i doing wrong?

I am using SQL Server Management Studio 2008 on a 32 bit system.

score 6 · Accepted Answer · answered May 06 '11 at 16:50

6

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.

answered May 06 '11 at 16:50

dcinadr

665
2
7
24

i don't understand what you are trying to do? – dcinadr Mar 22 '13 at 15:46
I've SQL Server 2008 on 64Bit machine and Accecc 2007 ... I want `Microsoft.Jet.OLEDB.4.0`... – Sachin Mar 23 '13 at 10:08
1

The typical drivers did not work with a 64 bit machine. I receded to use microsoft ACE OLEDB instead. – dcinadr Mar 26 '13 at 03:26

BradBrening · Answer 2 · 2011-05-04T12:57:46.153

5

Try using the Microsoft ACE OLEDB provider:

EXEC sp_addlinkedserver
   @server = 'Test'
  ,@provider = 'Microsoft.ACE.OLEDB.12.0'
  ,@datasrc = '\\srv\public$\CM Database\Data\sysConfig_dat.mdb'
  ,@srvproduct='Access'
GO

edited May 04 '11 at 12:57

answered May 03 '11 at 21:27

BradBrening

5,418
25
30

that gives me this... '(null)' is an invalid product name. – dcinadr May 03 '11 at 21:35

SQL to Access linked server

2 Answers2

Linked