Linked Table -- Database name linked to table

[jonesda]

Hi,

I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.

I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.

If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.

I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.

Can anyone point me in the right direction??

Thanks

 

[Pat Hartman]

You'll need to define a separate DSN for each database you want to link to. Then use the linked tables manager as you have done, check the "always prompt" box and choose the new DSN.

Whether you saved the password in the link or not doesn't affect how you change the link to a different database.

 

[jonesda]

Thanks Pat,

At the moment I don't use a DSN. When I was upsizing the database I selected to create a new database and entered a username and password in the wizard that had permission to create a database.

These details seemed to be stored somewhere in the database in order for it to be able to make a connection. This is useful at the moment because it means that I don't have to setup a DSN on each user's PC.

Is there anyway I can modify these settings in Access without having to setup a DSN to connect to a new database?

Many Thanks

 

[jonesda]

I eventually found where this configuation data is stored: in the Connect field in the MSysObjects table.

However I am unable to update this field. I get the message: "Control can't be edited;it's bound to replication system column 'Connect' "

Is there any way that I can unbind this column in order to change it? Or is it possible to update the connect field in the MSysObjects table.

I just want to be able to change the name of the database in the following string in each column for each table:
DRIVER=SQL Server;SERVER=NRD;UID=cu;PWD=cu;APP=Microsoft Office 2003;WSID=POINTPC;DATABASE=CSUIO

 

[Pat Hartman]

You may be able to change the connection property if you update it via the TableDefs collection.