SQL Server Performance Forum – Threads Archive

Linked server to MS Access….

Hello,
I have the following problem: On server S1 I’ve made linked server MS to Microsoft Access database with the following scripts:
EXEC sp_addlinkedserver @server = ‘MS’, @provider = ‘Microsoft.Jet.OLEDB.4.0’, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘\serveruserCorpMS.mdb’
Then added login: EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘MS’, @useself = false, @locallogin = ‘sa’, @rmtuser =’admin’, @rmtpassword = NULL When I’m trying to connect to MS from S1 then all work fine. The problem appears when I try to query MS from other place through QA connected to S1 or from other registered servers… for IE: I login to S2 (other sql server) then open QA then connect to S1 and my queries to MS are not successfull. Any ideas? Thanks for your help in advance

Thought to provide more info. On EM I get error : Error 7399: OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ reported an error.
OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IDBInitialize::Initialize returned 0x80004005: ]. Trying to execute select statement through QA I get the following: Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file ‘\serveruserCorpMS.mdb’. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IDBInitialize::Initialize returned 0x80004005: ].
I checked the following KB about error 7399, but it didn’t helped
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398 I remember we had the same issue with linked sql servers, but we solved that by entering some values in provider string on the linked server like: server name, uid, pwd and network. But on access linked server I cann’t find any parameters to enter that ‘provider string’ Any ideas?

Are you providing four part names to access the Linked server? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Not sure what you mean by "four part names"… The database MS.mdb reside on the following location: ‘\serveruserCorpMSMS.mdb’ If you are talking about other vendor products then we are not using them. Only SQL server and directly created linked server to ms.mdb
And we cannot connect to it either EM nore QA from other locations. It works only when I login directly to that server which has that linked server.

]]>