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

I have an Access database that acts as a control centre for several other Access databases.  When I need to connect to one of the databases, I delete all linked tables, use a local table containing a list of the actual tables that I want to link to and then link.

However, is there a way that I can just bring in (link to) all of the tables (excluding system) into the control centre - through VBA.
Andy BrownDeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rockiroadsConnect With a Mentor Commented:
I see. ok, I thought you had meant you had the table holding tablenames.

No problem. you can use opendatabase to open the database

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sRemoteDB As String
   
    sRemoteDB = "C:\myremotedb.mdb"
    Set db = DBEngine(0).OpenDatabase(sRemoteDB)
    Set rs = db.OpenRecordset("select * from MSysObjects where type=1 and left$(name,1) <> '~' and left$(name,4) <> 'MSys'")
    Do While rs.EOF = False
       
        DoCmd.TransferDatabase acLink, "Microsoft Access", sRemoteDB, acTable, rs!Name, rs!Name
       
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
   
    db.Close
    Set db = Nothing
0
 
rockiroadsCommented:
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

0
 
rockiroadsCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Andy BrownDeveloperAuthor Commented:
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?
0
 
rockiroadsCommented:
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
0
 
Andy BrownDeveloperAuthor Commented:
Perfect - thank you as always.
0
All Courses

From novice to tech pro — start learning today.