Access 03 & MySQL

[john.papa]

I am trying to use Access 03 as frontend and MySQL as backend in a DSNless connection. So far I have managed


to use ADO to connect

    Dim Conn1 As New ADODB.Connection
    Conn1.Open "DRIVER=MySQL ODBC 5.1 Driver;" & _
                   "SERVER=localhost;" & _
                   "DATABASE=sakila;" & _
                   "USER=root;" & _
                   "PASSWORD=1234;" & _
                   "OPTION=3;"

I now need to loop through the linked tables and update the tables. I used ADOX

  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
'      adoTbl.Properties("Jet OLEDB:Link Provider String") = XXXXXXXXXX
    End If
  Next

I can successfully loop through all linked tables, but need to assign the appropriate value to the liked table
according to the params I used in the ADO code above.

If I hover the cursor on any of the linked tables I receive
"ODBC;DSN=sample-MySQL;TABLE=actor" where "sample-MySQL" = the name of the DSN
and "actor" is the name of the table.

Does anyone know what the linked table properties should be set to? Should it be something like
adoTbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=sample-MySQL;TABLE=" & adoTbl.Name

Many thanks,
John

[john.papa]

I believe I found a solution to my problem. The code below includes hardcoded parameters.

Private Sub Form_Open(Cancel As Integer)
Dim adoCat As New ADOX.Catalog
Dim adoTbl As New ADOX.Table
'Define attachment to FE database
Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = CurrentProject.Connection
Set adoTbl.ParentCatalog = adoCat
'Refresh Linked Tables
For Each adoTbl In adoCat.Tables
If adoTbl.Type = "PASS-THROUGH" Then
adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
End If
Next
'Clean up the connection to the database
Set adoTbl = Nothing
Set adoCat = Nothing
End Sub

Public Property Get MSysObjectsConnectString() As String
MSysObjectsConnectString = "Driver=MySQL ODBC 5.1 Driver;Server=localhost;Database=sakila;UID=root;P WD=1234;"
End Property