Linked Server - Access 97 problem

I've consistently run into this problem trying to use a
SQL 2000 linked server to Access 97 using the Jet 4.0
provider. The linked server connection works successfully
for a limited amount of time (1-4 hours), but eventually
this error appears:
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: Cannot open
database ''. It may not be a database that your
application recognizes, or the file may be corrupt.]
Once this error message appears, I cannot query any linked
Access97 database through linked server or
OPENROWSET/DATASOURCE commands without getting the same
message. Interestingly enough, you can still query
Access2K databases with no problem. The only solution is
to stop/restart the SQLServer service, which obviously is
a problem.
For various reasons I won't go into, I can't simply
upgrade the 97 databases to 2K. I don't believe it's a
permission problem since the link does work for a while
before getting hosed. There's plenty of disk space
available on the SQL Server.
Any ideas? Everything is running Jet4.0 SP7, and I
believe it's SQL 2K SP3 on a Win2K box.
- timI'm afraid you're going to continue to experience difficulties as long
as you have to stick with Access 97. It's not really compatible with
SQLS 2000, and in fact, msft is discontinuing support for 97 by the
end of the year. FWIW, Access 2000 isn't so great either -- when you
get around to upgrading, you'll have far fewer problems going with the
most recent version of Access available, where they've shaken out most
of the interoperability bugs with SQLS 2000.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Thu, 4 Dec 2003 08:29:18 -0800, "Tim Schulze"
<tschulze_nospam@.ppmsi.com> wrote:
>I've consistently run into this problem trying to use a
>SQL 2000 linked server to Access 97 using the Jet 4.0
>provider. The linked server connection works successfully
>for a limited amount of time (1-4 hours), but eventually
>this error appears:
>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: Cannot open
>database ''. It may not be a database that your
>application recognizes, or the file may be corrupt.]
>Once this error message appears, I cannot query any linked
>Access97 database through linked server or
>OPENROWSET/DATASOURCE commands without getting the same
>message. Interestingly enough, you can still query
>Access2K databases with no problem. The only solution is
>to stop/restart the SQLServer service, which obviously is
>a problem.
>For various reasons I won't go into, I can't simply
>upgrade the 97 databases to 2K. I don't believe it's a
>permission problem since the link does work for a while
>before getting hosed. There's plenty of disk space
>available on the SQL Server.
>Any ideas? Everything is running Jet4.0 SP7, and I
>believe it's SQL 2K SP3 on a Win2K box.
> - tim|||I experienced the same issue trying to link (via code or ODBC) an Access97
EDI app to sql 2K back-end. Finally backed up the whole thing, imaged the
hard drive (in case I had to go back) and upgraded to Access2K The
conversion and upgrade took less than 15 minuts and it worked 100%
immediately and has been chugging along for over two weeks without an issue.
This was after two weeks of horrible hassles & headaches.
M
"Tim Schulze" <tschulze_nospam@.ppmsi.com> wrote in message
news:0b3c01c3ba83$c3e765f0$a501280a@.phx.gbl...
> I've consistently run into this problem trying to use a
> SQL 2000 linked server to Access 97 using the Jet 4.0
> provider. The linked server connection works successfully
> for a limited amount of time (1-4 hours), but eventually
> this error appears:
> 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: Cannot open
> database ''. It may not be a database that your
> application recognizes, or the file may be corrupt.]
> Once this error message appears, I cannot query any linked
> Access97 database through linked server or
> OPENROWSET/DATASOURCE commands without getting the same
> message. Interestingly enough, you can still query
> Access2K databases with no problem. The only solution is
> to stop/restart the SQLServer service, which obviously is
> a problem.
> For various reasons I won't go into, I can't simply
> upgrade the 97 databases to 2K. I don't believe it's a
> permission problem since the link does work for a while
> before getting hosed. There's plenty of disk space
> available on the SQL Server.
> Any ideas? Everything is running Jet4.0 SP7, and I
> believe it's SQL 2K SP3 on a Win2K box.
> - tim