We help IT Professionals succeed at work.

MS ODBC for Oracle - connection error from A2K

MichaelGravelle
on
I have an Access 2k database which uses the Microsoft ODBC for Oracle driver to link several tables from an Oracle 8.0 database. The problem is that every time I reopen the database and try to open a table / run a query etc I get Access error 3151 - ODBC connection to <database
name> failed. If I then relink the tables all is fine unless I close Access and reopen it, in which case the connection is lost again.

The Oracle database is accessed across a WAN (2mb leased line).

I have at least one other Access application, connected to the same Oracle database in the same way, for which this problem does not occur.

Can anyone offer a suggestion why this might be happening?

TIA

Mike G
Comment
Watch Question

Commented:
>I have at least one other Access application, connected to the same Oracle database in the same way

Well there must be some difference.
Anyway, to solve the problem you could just copy the working app, delete all objects except linked tables and import them from your problematic one.

Or try relinking tables from code on your application startup, though it may slow down opening of your application.

One more point, with Oracle8 I had some problems with MS driver, but not the Oracle one (with Oracle 7 it was vice-versa), but this could be my fault, not MS's, I settled with Oracle driver and stopped troubleshooting the MS one after finding that the former worked OK.
Good luck

Author

Commented:
This appears to have worked but doesn't explain why the original database was falling over.

What I did was to make a copy of an A2k database that was connecting reliably to the Oracle BE. I then deleted ALL objects from this copy (linked tables, queries, forms, modules) and copied across the objects from the broken database. This copy now works reliably. But, since the copy contains exactly what the original did and all the linked tables were copied across rather than relinked it suggests that something in the .mdb had become corrupted perhaps?

Thanks for the pointer.

Commented:
Some 10 years ago (DOS times) I'd start investigating the problem. Now, when possible causes of problems are much numerous I abandoned looking for reasons if resolution is satisfactory.

Well, it could be that decompiling-recompiling of the original DB would help (if DB became corrupted, or maybe would not help), but, why loose time testing Access strange behaviour if you are not payed for that by MS? :) Better find workable solution and forget it.
Good luck

Commented:
And thanks for points

Explore More ContentExplore courses, solutions, and other research materials related to this topic.