Linking to all of the tables in another database (via VBA)

Hi Andrew, the following command in vba can be used to link tables

DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\myremotedb.mdb", acTable, "tablenameonremote", "tablenameonlocal"

Now if you have a list of tables you want linking, you could maybe create a recordset and link manually
eg your table is called mylistoftables and column holding name is called tblName

public sub LinkTables

    dim rs as dao.recordset
    dim sRemoteDBPath as string

    sRemoteDBPath =  "c:\myremotedb.mdb"
    set rs=currentdb.openrecordset("select * from mylistoftables")
    do while rs.eof = false

        'link table
        DoCmd.TransferDatabase acLink, "Microsoft Access", sRemoteDB, acTable, rs!tblName, rs!tblName

        'check next record
        rs.movenext
    loop

    rs.close
    set rs=nothing
end sub

Ideally we should check to see if table already exists before dropping it. Thus we check msysobjects

eg

public sub LinkTables

    dim rs as dao.recordset
    dim sRemoteDBPath as string
    Dim lID As long
   
    sRemoteDBPath =  "c:\myremotedb.mdb"
    set rs=currentdb.openrecordset("select * from mylistoftables")
    do while rs.eof = false

        'Check table exists and drop
        lID = Nz(DLookup("Id", "MSysObjects", "[Name]='" & rs!tblName & "' and [Type]=1"), 0)
        if lID >  0 then docmd.deleteobject actable, rs!tblName

        'link table
        DoCmd.TransferDatabase acLink, "Microsoft Access", sRemoteDB, acTable, rs!tblName, rs!tblName

        'check next record
        rs.movenext
    loop

    rs.close
    set rs=nothing
end sub

Thanks Rockiroads - but that's pretty much what I have.

I am currently testing the following concept:

1) Link to [MSysObjects] in the required database and call it [zzzMSysObjects]
2) I then create a recordset based on the following query, which may not be perfect but seems to have everything that I need:

SELECT zzzMSysObjects.Name, zzzMSysObjects.Flags, zzzMSysObjects.LvProp FROM zzzMSysObjects WHERE (((zzzMSysObjects.Name) Like 'dd*') AND ((zzzMSysObjects.Flags)=0) AND ((zzzMSysObjects.LvProp) Is Not Null)) OR (((zzzMSysObjects.Name) Like 'tbl*') AND ((zzzMSysObjects.Flags)=0) AND ((zzzMSysObjects.LvProp) Is Not Null)) OR (((zzzMSysObjects.Name) Like '00*') AND ((zzzMSysObjects.Flags)=0) AND ((zzzMSysObjects.LvProp) Is Not Null));

3) I then use this recordset to give me the tables to link to.

What do you think?

the above code opens the remote database and reads msysobjects to get the list of tables. this saves you from linking in the remote msysobjects

Perfect - thank you as always.