Linked Server to Access DB (pllleeeaasseee help) :) -- sql server programming

jpeterson NO[at]SPAM adecco.com.au
6/23/2004 11:12:05 PM

Hi!
I have searched the archives to find the answers but i still dont
understand! :) Im trying to link a secure access db in SQL 2000 using
the following sp's:

EXEC sp_addlinkedserver
@server = 'Global_UAT',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\GlobalTesting\Jbglobal.mdb'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'Global_UAT', @useself =
false,
@locallogin = 'sa', @rmtuser ='JBUser', @rmtpassword = '<password>'

But when i try to view the tables in enterprise manager i get "Error:
7399....Authentication Failed"

How can i specify the mda file to use? I have checked the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB entry
and the value of the SystemDB is the same name as the mda file we use.
Am i totally on the wrong track?

John Bell
6/24/2004 10:46:09 AM

Hi

At a guess! as this is a secured database you will need to specify where the
system database is, therefore I think you need a provider string (@provstr )
parameter that will contain this information.
There is an example (E) of using a provider string in Books Online in the
sp_addlinkedserver topic. An example from http://www.connectionstrings.com/
of a connection that uses a system database is
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet
OLEDB:System Database=system.mdw;"

John

[quoted text, click to view]

sql server programming : Linked Server to Access DB (pllleeeaasseee help) :)