Converting DAO to ADO

Question

I am working with an Access 2003 database that has a subroutine using DAO code. This code loops through the table definitions and refreshes the ODBC connection string. I would like to convert this to ADO so I do not have to reference the DAO object library. Here is the code ...

Public Sub RefreshODBCLinks(newConnectionString As String)

    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing

    MsgBox "New connection string is " & newConnectionString, vbOKOnly, "ODBC Links refreshed"

End Sub

The part I am unsure of is how to loop through the tables and get/set their connection strings.

Answer 1

DAO is really best for that, you cannot refresh the link with ADO rather you would need to use ADOX (Some relevant code here).

You can acces the connection string via Jet OLEDB:Link Provider String

Answer 2

If avoiding a reference for DAO is your goal, you could just modify your existing procedure to use late binding for DAO. As an example, this sub should work without a reference set for DAO.

Public Sub DAO_without_reference()
    Dim db As Object
    Dim td As Object
    Set db = CurrentDb
    For Each td In db.TableDefs
        Debug.Print td.Name
    Next td
    Set db = Nothing
End Sub

You would not have Intellisense to help you with DAO properties, methods, and constants while writing the code, but the code can still work with late binding.

I think this would be your easiest alternative if you are determined to avoid a DAO reference. However, I have never developed an Access project without a DAO reference, and I don't understand why you are opposed to adding it.

Edit: Also if you use late binding and any DAO constants, your code must use the constant value rather than the name.