Runtime error 3151 - only for one mdb

[Guest]

We just moved our SQL Server install to a new machine, and suddenly ONE of
our access apps no longer works. We have about 5 apps we use daily, two are
ADP's and three MDB's. In all cases they connect through a single DSN, which
we simply moved from OLDSERVER to NEWSERVER.

After this change four of the apps work perfectly on all of the machines.
The fifth reports various ODBC connection errors, sometimes simply "ODBC
Connection Failed", sometimes a long error about a problem in the connection
string, and others 3151's.

If you go to the Linked Table Manager and update ANY table, the app starts
working again. At least for one run, once you close it it's back to the
original problem again.

Any ideas?

 

[Guest]

Further information:

The problem actually occurs in any long-running query. However "long
running" in this case is only about 10 seconds or so. Generally it seems that
the queries that fail are using UNION's (this is MDB remember) but not always.

We're baffled!

 

[Dirk Goldgar]

Further information:

The problem actually occurs in any long-running query. However "long
running" in this case is only about 10 seconds or so. Generally it
seems that the queries that fail are using UNION's (this is MDB
remember) but not always.

We're baffled!

Can we eliminate the possibility of a timeout on the query? What
happens if you take one of those queries in that failing app, open it in
design view, bring up its property sheet (for the query, not a field or
table), and set its ODBC Timeout property to some large value? If you
save it and try to run it, does it still fail? If it doesn't, does it
fail if you exit the DB and then go back in and run it?

Also, have you demonstrated that this happens on various different
machines, not just on one?