Use DAO to create links to all tables in another database

[PRMiller2]

I am trying to create links to all tables in another database, but I do not know all of the table names. Following is the code that I am using successfully to link to tables for which I DO know the names:

Function LinkDatabase(strTableName As String)

    Dim db As DAO.Database
    Dim strConnectionString As String
    Dim tdf As DAO.TableDef
    
    If DCount("*", "msysobjects", "Type = 4 AND name='" & strTableName & "'") = 0 Then
        strConnectionString = "ODBC;Driver={SQL Server}; Database=dbname;Server=servername;" & _
                              " Trusted_Connection=Yes; UID=userid; "
        
        Set db = CurrentDb
        Set tdf = db.CreateTableDef(strTableName)
        
        tdf.Connect = strConnectionString
        tdf.SourceTableName = strTableName
              
        db.TableDefs.Append tdf
        
        Set tdf = Nothing
        Set db = Nothing
    End If
    
End Function

I'm having some trouble finding the correct way to write this, and have run up against a wall. Any help would be greatly appreciated.

Thanks,
Paul

 

[TheAceMan1]

How are ya paulrmiller . . .

Here's a straight-up DAO method for linking all tables from an external DB. You may have to modify it a little ... but I'm thinking not. At the very least you can use it as a model. In my testing it worked like a charm:

[blue]Public Function LinkTables(dbFile As String)
   [green]'dbFile includes the entire path or UNC![/green]
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Name, Type " & _
         "FROM MSysObjects IN '" & dbFile & "' " & _
         "WHERE (Left([Name],4)<>'MSys') AND " & _
               "([Type] IN (1,6));"
   Debug.Print SQL
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   Do Until rst.EOF
      DoCmd.TransferDatabase acLink, _
                             "Microsoft Access", _
                             dbFile, _
                             acTable, _
                             rst!Name, _
                             rst!Name
      DoEvents [green]'allow time for the transfer to occur[/green]
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set db = Nothing
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PRMiller2]

Thanks AceMan... and on the 4th, no less! This works great except for an issue I have with connectivity. I'd like to specify the connection string within the function. I struggled with the best way to adapt your code to that need and, after some searching, changed it to the following:

Public Function LinkTables()
'###########################################
'#
'# Module written by TheAceMan1 at TekTips
'#
'#
'###########################################

    Dim cn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strConnectionString As String
    Dim strSQL As String
    
    strConnectionString = "ODBC;Driver={SQL Server}; Database=dbname;Server=servername;" & _
                          " Trusted_Connection=Yes; UID=userid; "
    
    cn.ConnectionString = strConnectionString
    cn.Open strConnectionString
    
    strSQL = "SELECT Name, Type" & _
             " FROM MSysObjects IN 'dbname' " & _
             " WHERE (Left([Name],4)<>'MSys')" & _
             " AND ([Type] IN (1,6));"
          
'    Debug.Print strSQL
    
    rst.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
    
    Do Until rst.EOF
        DoCmd.TransferDatabase acLink, _
                               "Microsoft Access", _
                               "dbname", _
                               acTable, _
                               rst!Name, _
                               rst!Name
        DoEvents 'allow time for the transfer to occur
        rst.MoveNext
    Loop
    
    Set rst = Nothing
    Set cn = Nothing
    
End Function

However, I now receive this error: "Data source name not found and no default driver specified."

I suspect I'm overcomplicating things, and that a small modification to what you wrote would be all that was necessary. However, wanted to see if I could do it on my own... and apparently not yet!

Your suggestions?

Thanks,
Paul