Problem linking ACCESS to SQL SERVER

• Jan-155192

  SSCrazy

  Points: 2613

  More actions

  January 31, 2006 at 3:59 am

  #170315

  Hi,

  I have an access database that I want to link to SQLServer. For this, I want the service where SQLServer is running under, to only have rights to READ the mdb file. The NTFS and share settings are set accordingly.

  This works fine on one server:

  EXEC sp_addlinkedserver

  @server = 'TEST',

  @provider = 'Microsoft.Jet.OLEDB.4.0',

  @srvproduct = 'OLE DB Provider for Jet',

  @datasrc = '\\myserver\myshare\test.MDB'

  GO

  EXEC sp_serveroption 'TEST','use remote collation','FALSE'

  GO

  SELECT * FROM TEST...[my access table]

  BUT... on another server this doesn't work, and I receive the following 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 '\\myserver\myshare\test.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 am 100% sure that no one is using or locking this database.

  Does anyone have some ideas?

  Thanks,

  Jan

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  January 31, 2006 at 6:49 am

  #617916

  Hello Jan,

  Please go through one of the earlier posts depicting the same problem of yours.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=185045

  MS KB Article.

  http://support.microsoft.com/default.aspx?scid=285833

  Thanks and have a nice day!!!

  
  Lucky