DSN pointing to old server?

[Fermon]

Hi,

I have an Access 2k3 db that has linked tables and runs pass-thru queries
against an Oracle database. Everything used to work fine until they moved the
Oracle db to a new server. They kept everything the same: databases names and
so on, except the server has a new name and is in a new ip address.

I changed the tnsnames.ora file to point to the new server but Access
refuses to connect to it. Everytime I try to connect whether it is a linked
table or a pass-thru query, Access tries to connect to the old server. I
tried closing Access and then deleting the old DSN and creating a new one
with a different name. That did not fix the problem.

I tried connecting from VBA and it worked at first but now it also tries to
connect to the old server. Somehow Access bypasses the tnsnames.ora file and
connects to the old server. I have read something about Access having its own
database of connections but I can't find anything on resetting it or editing
it.

Can you please help me understand what is going on and how to fix it, or
avoid it in the future? Any help is appreciated,

Fermon

 

[Mary Chipman [MSFT]]

Did you try (in code) deleting the existing TableDef and QueryDef
objects and then supplying the connection string when recreating them?
Removing the objects entirely should remove any cached connection
information.

--Mary

 

[Fermon]

Mary,

Thanks for your help. I had not tried to recreate the tables and queries
from code. However, I did not have too many tables and queries in place, so I
went ahead and created a brand new file and linked the tables using a new
DSN. I also created the queries by copying the text and then recreating the
connection with the builder.

This fixed the problem, which tells me you were right about the cached
connections lurking in the background. I wish there was an easy way to reset
the connections cache completely by using code, is this something that was
addressed in 2k7?

Thanks again,

Fermon

 

[Mary Chipman [MSFT]]

There is a way that works in all versions. You delete the
tabledef/querydef objects and recreate them. This does not affect the
tables on the server. Access stores metadata and connection
information for each linked ODBC object, so all you need to do is wipe
them out by iterating through the TableDefs/QueryDefs collections,
then recreate the links. You can put code for this in your startup
form so it happens automatically when someone opens your database.

--Mary

 

[Fermon]

Thanks again Mary. I will give it a try. It seemed a bit overwhelming at
first, but it can save so much time later on that it is worth the effort.