locked
SQL 2005 Linked Server to MS Access - Stops working - Start and Stop SQL Server RRS feed

  • Question

  •  

    I have created a linked server to an access database. I am able to get the linked server working and I am able to query the database via OPENQUEY. However, after about XX minutes (appears to be about 10), I am no longer able to query the database. I am getting the following error inserted below. I also seeing the error if i browse to the linked server through mgmt studio and try to view the tables in the catalog. I have have made sure the database is not in use or is not corrupt. I discovered that if I stop and restart the SQL service that the queries start working again for another XX minutes.

     

    I would greatly appreciate some assistance with this issue.

     

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<<LINKED SERVER NAME>>" returned message "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.".

    Msg 7303, Level 16, State 1, Line 2

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<<LINKED SERVER NAME>>".

     

     

     

    Tuesday, December 9, 2008 7:27 PM

All replies

  • Follow-Up Post: Any chance this is related to the database being an Access 97 format?

     

    I am working on an ssis package to pull the data into sql server but I hate to keep two copies of the information just because the linked server won't work. still hoping for a resolution...thanks for your help.

     

    Wednesday, December 10, 2008 2:28 AM
  • You'd better repair and compact your mdb file first, because sometime Access can still open most corrupted MDB files, but after running sometime, you don't know what will happen.

    This link may help you: http://support.microsoft.com/default.aspx/kb/310937.
    Monday, December 22, 2008 9:32 AM