Link servers to access

[dwight1]

Hi All,

I want to link/access mdb tables using the link server feature in SQL. I used the enterprise manager to create a link to the Access tables. But when ever i want to access the tables it gives me an error.

"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 '\\cbfiledal\DataSources\db1.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: ]."

Any inputs please.

Dwight

 

[DrSql]

Rather than using linked server from EM use the following sytax from QA.
[tt]
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'
[/tt]

Set the path correctly. And run this on the SQL server. If the access db is on the network, the
use the network shared path

\\servername\sharedfolder\NWind.mdb

Dr.Sql
Good Luck.