?
Solved

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

Posted on 2009-05-09
6
Medium Priority
?
405 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Andy Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24344156
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24344193
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
 

Author Comment

by:Andy Brown
ID: 24344218
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24344369
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24344372
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
 

Author Closing Comment

by:Andy Brown
ID: 31579772
Perfect - thank you as always.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question