SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create linked server to MS Access database


Create linked server to MS Access database

Author
Message
SQLdba-617800
SQLdba-617800
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 165
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
Sakthivel Chidambaram
Sakthivel Chidambaram
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 838
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
SQLdba-617800
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 165
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 Sidelnikov
Glen Sidelnikov
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 804
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
Sakthivel Chidambaram
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 838
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
SQLdba-617800
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 165
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 Sidelnikov
Glen Sidelnikov
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 804
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
SQLdba-617800
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 165
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 Sidelnikov
Glen Sidelnikov
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 804
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
SQLdba-617800
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 165
Yes. I am able to access that folder from sql server. But the linked server connection to the mdb file gives the error
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search