ODBC Issue

[graviz]

I have a database that links to some tables using an ODBC connection (Oracle in OraClient11g_home1). Just a couple days ago it said my connection failed when I ran my queries and I had to refresh the links where it prompted me for my password. Once it refreshed everything the data pulled fine. Now every time I open the database I'm getting the same error and have to refresh them all over again. This is also a problem because it's setup as an autojob and fails right when it opens. Any ideas?

 

[TJPoorman]

Sounds like the password is no longer stored with the connection string.

I would recommend deleting the tables and recreating the connections. When selecting the tables be sure to check the "Save Password" box before continuing.

 

[Rx_]

Did you use Access to connect to Oracle as a linked table or is this a SQL Server Linked Server to Oracle that provides a linked table to Access?

 

[graviz]

Did you use Access to connect to Oracle as a linked table or is this a SQL Server Linked Server to Oracle that provides a linked table to Access?

I used access to connect to oracle as a linked table. So I made some progress but have no idea what's causing this:

There are 6 Oracle tables (A,B,C,D,E,F) I'm linking to in a single DB (the connection string is the same except the table name).

When I open my access DB, if I try to open 4 of the six (C, D, E, F) it says connection to the DB has failed. If I click to refresh the links it works fine. If I close it and reopen the Access DB I have the same issue again. Here's the odd thing. If I try and open the bottom 4 tables (C, D, E, F) I get the same issue. If I try and open the top two (A or B) it lets me and then I can open any of the other ones without having to refresh the links. I made a work around to just have it open table A and then have it run the queries but I'd like to figure out the cause. I hope this makes sense to someone.

 

[BlueIshDan]

The problem is that it isn't automatically initializing the connection on launch of the access database?

 

[BlueIshDan]

If I try and open the bottom 4 tables (C, D, E, F) I get the same issue. If I try and open the top two (A or B) it lets me and then I can open any of the other ones without having to refresh the links.

I'm willing to bet if you do this backwards it will do the same thing. Opening the top two first won't work, then after that opening the bottom 4 will work.

I'm thinking the initial parse to the database is thrown back as a fail, but continues to open the gates to success afterwords.

 

[graviz]

I'm willing to bet if you do this backwards it will do the same thing. Opening the top two first won't work, then after that opening the bottom 4 will work.

I'm thinking the initial parse to the database is thrown back as a fail, but continues to open the gates to success afterwords.

If I open the first two it always works. That's the odd thing. Again the connection string is the same except for the table name.

 

[BlueIshDan]

reinitialize your odbc connection.

remove the old one, or two?, and create it again from a clean slate.

 

[graviz]

reinitialize your odbc connection.

remove the old one, or two?, and create it again from a clean slate.

I tried all that. I tried making a new access DB, creating a new ODBC connection and no luck.

 

[BlueIshDan]

On the load of your initial form you could try turning warnings off and querying one of the tables, then turning warnings on again to see if that initial failed connection allows for a seemingly clean start.

Just a thought for a workaround until you find the fix.