Nothing changed when i modify the uid and passwd

[lianghua19]

Public Sub reLink()
   Dim connectString As String
   connectString = "ODBC;DSN=aaa;Server=aaa;Uid=bbb;Pwd=bbb"
   Call createLink("TABLE_NAME", connectString)
End Sub

Private Function createLink(tbLName As String, connectString As String)

    DoCmd.TransferDatabase TransferType:=acLink, DatabaseType:="ODBC  Database", DatabaseName:=connectString, ObjectType:=acTable,  Source:=tbLName, Destination:=tbLName, StructureOnly:=False,  StoreLogin:=True
    
End Function

There is no compile error in above code, and execute perfectly, but when i change the uid and pwd(which point to another database in the same server), nothing change to the table's data(the other database has the same table with the name TABLE_NAME, and it did execute the VBA code). However it will change only i modify the code and close, re-open the access file.

I have no idea about that, it troubles me for hours, i am wondering if there is some data store in cache or something?

Any answer would be appreciate.Thanks in advance.

[boblarson]

You say you change the UID and PWD but have you changed the database that the DSN points to? If not you must do that as the DSN can only point to one database at a time. You may want another DSN so you can then substitute the other DSN name quickly with the UID and PWD change.

[lianghua19]

I got Data Source Name: aaa, and TNS Service Name: aaa, and i can test connection with both database1(usrName1=ccc,pwd1=ccc) & database2(usrName2=ddd,pwd2=ddd )without any problems,but when come into the code, nothing changed if i replace database1 by database2, the result always the database i execute the code at first time i open the .mdb file.

add another dsn works fine for me, thanks. could i using code to interrupt the link which my previous execution generate, so i don not need another dsn for that?

Thank you so much..

[lianghua19]

You say you change the UID and PWD but have you changed the database that the DSN points to? If not you must do that as the DSN can only point to one database at a time. You may want another DSN so you can then substitute the other DSN name quickly with the UID and PWD change.

I got Data Source Name: aaa, and TNS Service Name: aaa, and i can test connection with both database1(usrName1=ccc,pwd1=ccc) & database2(usrName2=ddd,pwd2=ddd )without any problems,but when come into the code, nothing changed if i replace database1 by database2, the result always the database i execute the code at first time i open the .mdb file.

add another dsn works fine for me, thanks. could i using code to interrupt the link which my previous execution generate, so i don not need another dsn for that?

Thank you so much..

[boblarson]

I'm not understanding what you are saying about the usrName2 being able to access a different database simply by using a different user name and password. That makes no sense whatsoever because of the way the DSN's work. It doesn't just select one database if you use one user name and password and a separate database if you use a different user name and password. It only selects the database that was selected at the time you set up the DSN.

So yes, you would need two DSN's - one for each database. Then you can modify your procedure to pass it the values you need when you need them:

Public Sub reLink(strDSN As String, strUID As String, strPWD As String) 
Dim connectString As String
connectString = "ODBC;DSN=" & strDSN & ";Server=aaa;Uid=" & strUID & ";Pwd=" & strPWD 
Call createLink("TABLE_NAME", connectString) 
End Sub
 
Private Function createLink(tbLName As String, connectString As String) 
 
DoCmd.TransferDatabase TransferType:=acLink, DatabaseType:="ODBC Database", DatabaseName:=connectString, ObjectType:=acTable, Source:=tbLName, Destination:=tbLName, StructureOnly:=False, StoreLogin:=True
 
End Function