ODBC Connection within Access



External to Access, through Administrative tools, I set-up an ODBC connection
to our QA server. Our Marketing Department uses an Access application to
retrieve and actually change some data that's within a third party SQL Server

After I set-up the ODBC to our QA server, we went back into the Access
Database and it is still using the old Production ODBC connection. How can I
change the ODBC connection and where and how this Access application is
connecting to???

Thanks in advance.





You need to delete the current link tables and then relink them using the new
ODBC connection.

Tom Ellison

Dear Wnfisba:

Did you create a new DSN File Data Source, or did you modify the existing
one? I strongly recommend creating a new one and not interrupting the
function of the existing one.

If a new DSN, did you create new linked tables though it? Specifically, did
you drop the previous linked tables and recreate each one through the new

There would be specific steps to do this. You do not detail the steps you
have used, and so I cannot tell just where you may have gone wrong.

Let me suggest the steps I would attempt.

I would create an entirely new DSN to the ODBC data source. I would then
link a test table in the application through that and make sure it is
working. In a COPY of the database, I would then begin dropping and
recreating the former ODBC tables using the new DSN. The copy of the
application can then be tested, at the same time testing the new ODBC data.
If all this succeeds, you may be close to the point where it can be

The process of setting up DSN and testing it needs to be performed to some
degree on each workstation.

Simply changing the existing DSN could be much easier, but it is still a
computer by computer change.

An alternative would be to have this all done at some "down time" in which
you leave all the workstations alone and carefully replace the database at
the server with the new database. This would then be tested on at least a
few systems. Keep the old database in case you need to replace it if the
new one fails in some respect.

Caution is the watchword! When a substantial amount of new operations have
been recorded in the new database, finding a problem with it means disaster.
Make sure everything is perfect before the first full day's work in the new

Did this help at all?

Tom Ellison


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question