E
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
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