Connection String Madness

[Phil Smith]

I have a database, that when I open, has a bunch of tables with the
following Connection string:

ODBC;DATABASE=pavintheway;DRIVER={MySQL ODBC 3.51
Driver};OPTION=4+16384;PORT=3306;SERVER=192.168.0.254;

Now, I have two versions of code, modified slightly from Mr. Steeles
DSNless connection code, (example of one at the end) which change the
connection string to either:

ODBC;DATABASE=pavintheway;DRIVER={MySQL ODBC 3.51
Driver};OPTION=4+16384;PWD=wr1tepass;PORT=3306;SERVER=192.168.0.254;UID=ptwwrite
or
ODBC;DATABASE=pavintheway;DRIVER={MySQL ODBC 3.51
Driver};OPTION=4+16384;PWD=r3eadpass;PORT=3306;SERVER=192.168.0.254;UID=ptwread

After opening the databse, I can run either set of code, and
successfully set that connection string for each table.

After that, I cannot change the connection string again. Running the
other piece of code makes no difference.
If I close and reopen the database, the connection string returns to the
original version.

Why is Access changing it when I close and reopen, and why can't I
change it? The only errors caught by the code are 3270.

Code Follows. The other version is identical except for the connection
string makeup:
Function FixConnections(ServerName As String, DatabaseName As String)
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This subroutine looks for any TableDef objects in the
' database which have a connection string, and changes the
' Connect property of those TableDef objects to use a
' DSN-less connection.
' This specific routine connects to the specified SQL Server
' database on a specified server. It assumes trusted
connection.
'
' Inputs: ServerName: Name of the SQL Server server (string)
' DatabaseName: Name of the database on that server (string)
'

On Error GoTo Err_FixConnections

Dim dbCurrent As DAO.Database
Dim prpCurrent As DAO.Property
Dim tdfCurrent As DAO.TableDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strDescription As String
Dim typNewTables() As TableDetails

intToChange = 0

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

' Build a list of all of the connected TableDefs and
' the tables to which they're connected.

For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
Debug.Print tdfCurrent.Connect
ReDim Preserve typNewTables(0 To intToChange)
typNewTables(intToChange).Attributes = tdfCurrent.Attributes
typNewTables(intToChange).TableName = tdfCurrent.name
typNewTables(intToChange).SourceTableName =
tdfCurrent.SourceTableName
typNewTables(intToChange).IndexSQL =
GenerateIndexSQL(tdfCurrent.name)
typNewTables(intToChange).Description = Null
typNewTables(intToChange).Description =
tdfCurrent.Properties("Description")
intToChange = intToChange + 1
End If
Debug.Print tdfCurrent.Connect
Next

' Loop through all of the linked tables we found

For intLoop = 0 To (intToChange - 1)

' Delete the existing TableDef object

dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName

' Create a new TableDef object, using the DSN-less connection

Set tdfCurrent =
dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;" & _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"Server=192.168.0.254;" & _
"Port=3306;" & _
"Option=4+16384;" & _
"Stmt=;" & _
"Database=pavintheway;" & _
"Uid=ptwread;" & _
"Pwd=r3adpass;"

tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

' Where it existed, add the Description property to the new table.

If IsNull(typNewTables(intLoop).Description) = False Then
strDescription = CStr(typNewTables(intLoop).Description)
Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText,
strDescription)
tdfCurrent.Properties.Append prpCurrent
End If

' Where it existed, create the __UniqueIndex index on the new table.

If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
Next