Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Create linked server to MS Access database Expand / Collapse
Author
Message
Posted Monday, September 8, 2008 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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
Post #565482
Posted Monday, September 8, 2008 10:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 27, 2014 11:17 PM
Points: 309, Visits: 814
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
Post #565593
Posted Monday, September 8, 2008 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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
Post #565600
Posted Monday, September 8, 2008 10:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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.
Post #565604
Posted Monday, September 8, 2008 11:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 27, 2014 11:17 PM
Points: 309, Visits: 814
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
Post #565608
Posted Monday, September 8, 2008 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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.
Post #565609
Posted Monday, September 8, 2008 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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.
Post #565614
Posted Monday, September 8, 2008 11:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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
Post #565636
Posted Monday, September 8, 2008 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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
Post #565649
Posted Monday, September 8, 2008 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:30 AM
Points: 42, 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

Post #565692
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse