Linked Server to MS Access 97 Database Problem

Question

Linked Server to MS Access 97 Database Problem

VA123

SSCarpal Tunnel

Points: 4272
More actions
December 29, 2008 at 5:23 am

#126683

I created a linked server to a MS Access 97 database. The linked server is used in a proc that is executed once a day from a scheduled job. The linked server fails approximately 50% of the time (or more) with the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Inv" 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, Procedure cspWeight, Line 8
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Inv".
Deleting and recreating the linked server will not fix the problem. What does fix the problem (at least temporarily) is to restart the SQL Server instance using SQL Server Configuration Manager. Of course I want to have a permanent fix and to not have to restart the SQL instance.
Does anyone have any suggestions?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

Hmm try to put the servername, login name and password in correct way, once again!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 2

Is the Access file with password!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 3

Thanks, Dugi.

The Access database is not password protected. The linked server works sometimes (once a day), but it fails often.

Dugi SSCoach Points: 17998 More actions · Answer 4

Patrick Womack (12/29/2008)
Thanks, Dugi.
The Access database is not password protected. The linked server works sometimes (once a day), but it fails often.

So do you have still problem with linking ...?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 5

Thanks, Dugi.

I create a linked server to MS Access. The linked server works for a day or two, then it fails. Deleting and re-creating the linked server does not fix the problem. Restarting the SQL instance does fix the problem.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

Hmmm - I wonder if it has to do with the LDB lock file. I haven't tried setting up a linked server to MS Access 97. But we have had a situation where Access 2003 users and Access 97 users would share an Access 97 database. If a '97 user opened it first, ie. created the ldb, they locked out all of the 2003 users. If a 2003 user opened first then things were OK. From what I recall, there is an incompatibility in the locking mechanism used in the different versions. Our ultimate resolution of the problem was to upgrade users to 2003. I wonder if something similar is occurring in your situation if you have MS Access 97 users accessing the file when the SQL server is trying to connect.

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 7

Thanks, Ed. The Access 97 database is not a database that users open. It's a reporting database with tables that is used by our reporting system. It is possible that a different Access database or report process has locked the problem database via linked tables. All report processes are on a schedule. The linked server worked yesterday, but failed again today. I'll look into other processes that may be using the database.