ODBC Relink via File DSN

[Charles Tam]

Hi Everybody,

I've an Access application which links to an ODBC data source.
Currently, I'm using File DSN for ODBC access.
I've opened the DSN file and changed the server IP address, from
'SERVER=192.168.0.2' to 'SERVER=192.168.0.1' . Unfortunately, the Access
application is still refering to the old server IP address.

Does anyone know I've done wrong?

 

[david]

You should relink, using the linked table wizard, to a new file DSN.

Because if you just open the DSN and make a change, it doesn't work.

This is because an Access Application is based inside a database,
and stores connection information inside the database. It reads
connection information from a file DSN, and you need to make
it do that again. You do that by re-linking the database. As well
as closing the cached connections, this gets rid of other stuff.

If you had an executable file written in VB or C++, it would not
necessarily have a local database to store connection information
in, and so you might choose to use a file DSN to store the
connection information, and it would read the file DSN every
time it needed to make a connection.

Because Access stores the connection information inside the
database, there is no need to use a DSN at all. However, the
linked table wizard works with files, so if you want to use the
linked table wizard, you start with a DSN. If you want to use
a DSN'less connection, look at some of the sample code available
on the Web, for example at the Access Web site.

(david)

 

[Charles Tam]

Thanks for your reply.

You should relink, using the linked table wizard, to a new file DSN.

Because if you just open the DSN and make a change, it doesn't work.

This is because an Access Application is based inside a database,
and stores connection information inside the database. It reads
connection information from a file DSN, and you need to make
it do that again. You do that by re-linking the database. As well
as closing the cached connections, this gets rid of other stuff.

If you had an executable file written in VB or C++, it would not
necessarily have a local database to store connection information
in, and so you might choose to use a file DSN to store the
connection information, and it would read the file DSN every
time it needed to make a connection.

Because Access stores the connection information inside the
database, there is no need to use a DSN at all. However, the
linked table wizard works with files, so if you want to use the
linked table wizard, you start with a DSN. If you want to use
a DSN'less connection, look at some of the sample code available
on the Web, for example at the Access Web site.

(david)

 

[Charles Tam]

Hi David,
Thanks for your reply. Unfortunately, pass-through queries which uses File
DSN aren't refreshed.
What's are my options?

 

[david]

Well, I think you are going to have problems just switching the IP
address in the DSN, so I don't think you have any real choice.

However, to get it to work, what you can try is making sure
that Access is closed when you change the DSN, then doing
a compact/repair using the command line switch.

If that doesn't work:
(1) Modfy the Application so that no code or form is run when it opens
(2) Close Access
(3) block the old server
(4) Modify the DSN
(5) Open the application
(6) Compact
(7) Refresh the table links
(8) Open each linked query in design view and save it.

Long term, get some of the sample code for refreshing links and
doing DSN'less connections, and add to it to handle the pass
through queries.

Linked tables are handled by TableDefs, and you will see sample
code that loops through all the tabledefs, checking the connect property,
(Or other sample code has a table full of tablenames, and loops through
that) and updating it or replacing the tabledef.

Linked queries are handled by QueryDefs, and you'll have to modify
any sample code you find to handle QueryDefs as well as Tabledefs,
a fairly simple modification if you are familiar with VB.

(david)