Post reply

Problem linking ACCES to SQL Server

  • January 31, 2006 at 3:53 am

    #67862

    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

  • February 1, 2006 at 1:25 am

    #618054

    Once you link to the MDB from one server, that link has the file open,  since the MDB directory is ReadOnly it cannot create the LDF to share the file.

    You could use OPENQUERY or OPENDATASOURCE instead of sp_addlinkedserver to open the connection dynamically.

    Jet really Wants R/W access to an MDB.

    Andy

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply