Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-05-09
6
Medium Priority
?
441 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

604 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