Create Linked Table in Access database using VB

I need to create linked tables in MS Access databases.

Problem the line giving me errors is the "catdb.tables.Append tblLink" saying "-2147467259 Could not find installable ISAM". I think the Provider string is simply wrong. I need it to go through a SQL or Oracle ODBC to the database of choice.

Here's the whole function

Function CreateLinkedExternalTable(strTargetDB As String, strProviderString As String, strSourceTbl As String, strLinkTblName As String) As String
    'strTargetDB = Source Database Name     'strProviderString = Not used, currently hard coded     'strSourceTbl = Source Table name in the database we are linking too.
    'strLinkTblName = Table name we would like to see in the Access Database.
    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table
    Set catDB = New ADOX.Catalog
    ' Open a Catalog on the database in which to create the link.
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSourceDBFile
    If catDB.ActiveConnection.State = adStateClosed Then GoTo ErrHandler
   
    Set tblLink = New ADOX.Table
    With tblLink
        ' Name the new Table and set its ParentCatalog property to the open Catalog to allow access to the Properties collection.
        .Name = strSourceTbl
        Set .ParentCatalog = catDB
        'Set the properties to create the link.
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = "Provider=ODBC;DSN=TestODBC;DATABASE=TestDB;UID=SA;Password=xxxxx" 'strProviderString
        .Properties("Jet OLEDB:Remote Table Name") = "dbo." & strLinkTblName
    End With
    ' Append the table to the Tables collection.
    On Error Resume Next 'If table doesn't exist continue.
    catDB.Tables.Delete strSourceTbl
   
    On Error GoTo ErrHandler
    catDB.Tables.Append tblLink
    Set catDB = Nothing
   
    Exit Function
ErrHandler:
    MsgBox Err.Number & "  " & Err.Description
    Set catDB = Nothing
End Function

I would like to get either the fix to this function or a comparible piece of code.
Thank youStart Free Trial