Create linked server to MS Access database

Question

Post reply

Create linked server to MS Access database

SQLdba-617800

SSCommitted

Points: 1586
More actions
September 8, 2008 at 8:50 am

#92479

I am trying to Create a linked server to MS Access Databse that is in a different computer . SQL Server and MS Access Database are in the same network but different machines.
How do I connect and refer the tables in MSAccess from my SQL server

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sakthivel Chidambaram SSCertifiable Points: 5525 More actions · Answer 1

Hi,

You have not specified which SQL Server you are using... Anyways the method is same...

Hope you can go till create new linked server...

Now in the provider name select Microsoft.Jet.OLEDB.4.0 Provider (OLEDB is faster then ODBC AFAIK)

Specify a linked server name you wish

Datasource will the Path to mdb file Ex: "c:\test.mdb"

Product Name will be "Access"

Else you can try below T-SQL too

exec sp_addlinkedserver @server='Access',

@srvproduct='Access',

@provider='Microsoft.Jet.OLEDB.4.0',

@datasrc='c:\test.mdb'

exec sp_addlinkedsrvlogin @rmtsrvname='Access',

@useself='false',

@rmtuser='Admin',

@rmtpassword=''

Regarding login use Username Admin and NULL as password when you trying in GUI.

Now to get the remote table run below T-SQl

sp_tableex N'Access'

For more explanation on T-SQL and Linked Servers, Refer to BOL...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 2

I am trying to connect to MS Access 2003 from SQL server 2000.

I followed this procedure

Exec sp_addlinkedserver

@server = N'VIMachine',

@provider = N'Microsoft.Jet.OLEDB.4.0',

@srvproduct = N'OLE DB Provider for Jet',

@datasrc = N'\\AMPCSFDKW080\A.I.T Co., LTD\CMT-SR2000NWR 7.0\SamInfo.mdb'

Go

EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'VIMachine',

@useself = N'false',

@rmtuser = N'Admin',

@rmtpassword = ''

GO

But when I try to see the table in Enterprise manager I am getting the error

Error 7399: OLE DB Provider 'Microsoft.Jet.OLEDB.4.0' reported an Erroor

thanks

Sakthivel Chidambaram SSCertifiable Points: 5525 More actions · Answer 3

You can check this by copying the mdb to local drive and use Admin as username and see whether your linked server is working...

Thanks Glen for your inputs...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 4

yes. You are right. I gave the username as the userid used to logon to the MS Access machine. Still getting the same error.

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 5

Its giving the same error.

Actually the Access database is residing under c:/ProgramFiles/A/B/C.mdb

So I am giving the data sourse name as \\AMPCSFDKW080\A\BC.mdb . Is there anything wrong here?

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 6

Yes. I am able to access that folder from sql server. But the linked server connection to the mdb file gives the error

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 7

I am logging into SQL Server with SQL server authentication

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 8

If I create a DTS, I am able to conenct to this Access file on different server and able to see the Data tables.

Why the linked server is not working?

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 9

Thanks much. This works. There is a small problem with this. If I log on to Query Analyser using sa user id and if I run the query

select * from OPENQUERY(VIMachine, 'Select * from DataFile') is not working. What would be the reason for this?

Thanks again for your help.

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 10

Here is the scenario .

I am connecting to the MSaccess database from the production server. If I logon to the server , I am able to see the database and table. If, however, within enterprise manager on my machine I register my production server and try to look at the linked server that I created on the production server I get the following message:

[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB Provider returned message: The Microsoft Databse engine cannot open the file '//ampcds080/VI/info.mdb'. It is already opened exclusively by another user, or you need permission to view its data]

I know this file isn't opened by anyone. I gave Full control for everybody on the share..

I am not able to figure out what's wrong. Going crazy . I am new to SQL server..

thanks

bindueldo

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 11

I followed the same procedure , and created the share on Access machine with Everyone Full Control.

After that I ran the query Exec SP_TABLES_EX 'VIMachine'

it gives me the same error . Microsoft jet database engine cannot open the file '\\ampss04\VI\Sam.mdb'. It is already opened exclusively by another user or you need permission to view its data

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 12

Again I am stuck at EXEC sp_tables_ex 'VIMachine'

It is giving me the error Microsoft Jet database engine cannot open the file '\\ampcs04\Test\SamInfo.mdb' , It is already opened exclusively by another user or you need permission to view its data.

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 13

I am getting the same error message for select * also.

I gave the sharing security for everyone and my login name full control . But if I go to the server where SQL server is installed , I can see the data . But if use the enterprise manager in my pc(where this server is added in the server group ) I am getting the error message.

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 14

The linked server I am creating (SQL Server 2000 MAchine) is our Production server. Nobody cannot logon to that machine (remote desktop ) except administrators.

If other engineers needs to see the data , they have Enterprise manager in their local Pcs and registered this production server in their local enterprise manager as a SQL Server Group.

Like I said , I am fairly new to SQL server. What is the suggested workaround for this problem?

thanks