By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Changing ODBC Data Source in MS Access 2000

P: n/a
I am a UNIX person (not much experience with MS Access) who during a
recent upgrade on an IBM RS/6000 server had to convert our system due
to an upgrade to a new software revision.

The old software was ISAM-based and relied on a product called
Transoft U/SQL to create "simulated" ODBC connections to the data for
querying and reporting. The new software is using an Informix RDBMS,
and so the ODBC driver must be flipped to use the Informix Client SDK
product.

The problem I am facing is that our company has several reports that
we created using queries and linked tables using MS Access 2000. I
think that I should be able to swap data sources by going into the
Linked Table Manager, selecting the table with the old data source and
then choosing a new data source, and it should allow me to select the
equivalent table in the new data source. But it does not seem to like
the way I am doing it. I get an error: For example: the
“root_products” table could not be found.

I think this may because not only did the data source name change, but
all the tables in the new data source now have a suffix on them
(example: "products" is now "products_tbl"). However, all of the
fields within those tables remained the same.

So my question is this:
Is there any way to look at the data source and table names in an
existing MDB and update their names/links in this type of situation,
or am I S-O-L and have to recode the reports all over again? Does it
store the names/links in VBA code somewhere in the background that
would let me update it?
Sep 24 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sep 24, 2:46*pm, "steven_nospam at Yahoo! Canada"
<steven_nos...@yahoo.cawrote:
I am a UNIX person (not much experience with MS Access) who during a
recent upgrade on an IBM RS/6000 server had to convert our system due
to an upgrade to a new software revision.

The old software was ISAM-based and relied on a product called
Transoft U/SQL to create "simulated" ODBC connections to the data for
querying and reporting. The new software is using an Informix RDBMS,
and so the ODBC driver must be flipped to use the Informix Client SDK
product.

The problem I am facing is that our company has several reports that
we created using queries and linked tables using MS Access 2000. I
think that I should be able to swap data sources by going into the
Linked Table Manager, selecting the table with the old data source and
then choosing a new data source, and it should allow me to select the
equivalent table in the new data source. But it does not seem to like
the way I am doing it. I get an error: For example: the
“root_products” table could not be found.

I think this may because not only did the data source name change, but
all the tables in the new data source now have a suffix on them
(example: "products" is now "products_tbl"). However, all of the
fields within those tables remained the same.

So my question is this:
Is there any way to look at the data source and table names in an
existing MDB and update their names/links in this type of situation,
or am I S-O-L and have to recode the reports all over again? Does it
store the names/links in VBA code somewhere in the background that
would let me update it?
The table connect strings are stored in the system file MSysObjects,
in the Connect and DataBase columns. You can see MSysObjects if you
tick Tools->Options->View Tab->System Objects. The table is write
protected in this view, but writing an update query or using a
recordset to change the values is possible.

Sep 24 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.