Connection strings on linked tables do not refresh properly!!

[Edwinah63]

We use SQL Server 2k as the database and MS Access 2k as the client.
We have two versions of the client, Training and production.

Because each user's permissions are different we refresh the links to
the database with the following code:

(rs contains a list of tables to be relinked)

ConnectString = "ODBC;DRIVER={SQL
SERVER};SERVER=myserver;DATABASE=Training;UID=" & Usr & ";PWD=" & Pwd
& ";"

Set db = CurrentDb
rs.MoveFirst

Do While Not rs.EOF
Debug.Print rs(0)
Set td = db.TableDefs(rs(0))
With td
.Connect = ODBC_STR
.RefreshLink
End With
db.TableDefs.Refresh

Debug.Print td.Connect
rs.MoveNext
Loop


What we have observed is that the link is not refreshing to point to
the training system - when I look at the sysobjects table, the links
on the tables still state "production" in stead of training but has
refreshed the user's name.

so instead of being:

database=training;user=smithj


it reads

database=production;user=smithj

I have tried manually relinking the tables to the training system and
checked the sysobjects table and these links have been correct, but
often, when the user logs in again, it changes the links back to
production!!! despite the program connection string explicitly stating
training.

Does anyone know what is happening and how I can fix it? Is there
anyway I can edit the connection strings in msysobjects directly to
fix them up?

Or is there a better way?

Thanks in advance

Edwinah63

 

[storrboy]

Just so I understand...

(rs contains a list of tables to be relinked)

Here you use the variable ConnectString

ConnectString = "ODBC;DRIVER={SQL
SERVER};SERVER=myserver;DATABASE=Training;UID=" & Usr & ";PWD=" & Pwd
& ";"

Here you use ODBC_STR

With td
.Connect = ODBC_STR
.RefreshLink
End With
db.TableDefs.Refresh

Where does ODBC_STR originate from and what does it represent?

 

[Edwinah63]

Hi Storrboy,

It should read:

ODBC_STR = "ODBC;DRIVER={SQL
SERVER};SERVER=myserver;DATABASE=Training;UID=" & Usr & ";PWD=" & Pwd
& ";"

Thanks for picking up the typo in the post.

 

[Edwinah63]

Can noone help me here?

 

[storrboy]

Can noone help me here?

Post the full code for doing the re-linking. You could have other
errors that cause it fail.