Solved

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

Posted on 2009-05-09
6
360 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 65

Accepted Solution

by:
rockiroads earned 500 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

839 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