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 44 total)

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

Sakthivel Chidambaram SSCertifiable Points: 5531 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

Glen SSCertifiable Points: 6737 More actions · Answer 3

Your security on the linked server should use authentication which will allow authenticated account accessing the file share of Access DB.

"Admin" is a default user of Access database, not a Windows login name that have access to the file share where Access DB is located.

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 4

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 5

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

Glen SSCertifiable Points: 6737 More actions · Answer 6

bindueldo,

leave RMTUser name empty. If you are not using custom build security in Access, access user name should be omitted.

If your SQL server account is running under domain account security context, try to use option "Be made current user security's context" and then verify that the folder where Access DB is located have this user's proper security rights.

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

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

Glen SSCertifiable Points: 6737 More actions · Answer 8

Using your Windows Explorer from the server where SQL server is instaleld try to get to the folder you are specifying for the linked server.

If "A" is a name of the share - then you should be able to do it. Otherwise you will receive an eror message in Windows Explorer as well.

http://www.sqlservercentral.com/Forums/Topic347032-20-1.aspx

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

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

Glen SSCertifiable Points: 6737 More actions · Answer 10

Are you logging in to your SQL server using SQL server authentication or windows authentication?

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

I am logging into SQL Server with SQL server authentication

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

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

Glen SSCertifiable Points: 6737 More actions · Answer 13

Because DTS package is executed under Windows security and linked server under you SQL security..

Can you replace this statement with the following:

EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'VIMachine',

@useself = N'false',

@rmtuser = N'Admin',

@rmtpassword = ''

GO

sp_addlinkedsrvlogin 'VIMachine', false, 'YourSQLLogin', 'Admin', NULL

Read this article:

http://msdn.microsoft.com/en-us/library/aa172697.aspx

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

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.