Change to DSN-less tables

[DataMiner]

HI,

I have a function (included below) whose purpose is to change all of the odbc-linked tables in a database to "dsn-less" links, as is explained in MS KB article 892490: http://support.microsoft.com/kb/892490

The idea here is to code the SQL Server user name and password into each table's connection string. (Yes, I know this poses a security risk. Don't want to discuss that right now.)

Problem is, sometimes the tabledefs.append statement changes the connect string back to what I DON'T want. For example, just after the set td statement, If I do a debug.print td.connect, I get:

"ODBC;DRIVER=SQL Server;SERVER=myserver;DATABASE=mydb;UID=myusername;PWD=mypwd"

But just after the tabledefs.append statement

"ODBC;DRIVER=SQL Server;SERVER=myserver;UID=vikcha;APP=Microsoft Office XP;WSID=DTMEVTPC1648;DATABASE=mydb;Trusted_Connection=Yes

(vikcha is my windows login, and DMTEVTPC1648 is the PC I'm running from)

But sometimes it works OK. It's driving me crazy. I keep thinking I've got it fixed, and then it quits working again.

Any ideas on why this only works intermittently?

Function ChangeToDSNLessTable(strDB As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As DAO.TableDef, DB As DAO.Database, RS As DAO.Recordset
Dim stConnect As String, stLocalTableName As String, stRemoteTableName As String
Dim stServer As String, stDatabase As String, stUserName As String, stPassword As String
Dim lngUserNameLen As Long

stServer = "myserver"
stDatabase = "mydb"
stUserName = "myusername"
stPassword = "mypwd"
lngUserNameLen = Len(stUserName)

Set DB = OpenDatabase(strDB)
With DB
'========
.Execute "delete * from linkupdates;"

Set RS = .OpenRecordset("linkupdates", dbOpenDynaset)


'For Each td In .TableDefs ' find all the database filenames for linked tables
' If Len(tdfLinked.SourceTableName) > 0 Then ' its a linked table


'========
For Each td In .TableDefs
If Left(td.Connect, 4) = "odbc" And Left(td.Name, 1) <> "~" Then
RS.AddNew
RS!TableName = td.Name
RS!oldsource = td.SourceTableName
RS.Update
End If
Next td

Stop
RS.MoveFirst
Do Until RS.EOF
.TableDefs.Delete RS!TableName


'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword

Set td = .CreateTableDef(RS!TableName, dbAttachSavePWD, RS!oldsource, stConnect)

.TableDefs.Append td
If Mid(td.Connect, InStr(1, td.Connect, "UID="+4), lngUserNameLen) = stUserName Then
Debug.Print td.Name & " changed successfully"
Else
Debug.Print td.Name & " Unsuccessful, username= " & Mid(td.Connect, InStr(1, td.Connect, "UID=")+4, lngUserNameLen)
GoTo AttachDSNLessTable_Err
End If
RS.MoveNext
Loop


End With
DB.close
Set DB = Nothing
MsgBox "all tables in " & strDB & " updated to dsn-less"
Exit Function
AttachDSNLessTable_Err:

ChangeToDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function