• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

RecordSource and external MDB table

I want a form's RecordSource to be a table from an external MDB.  What is the syntax to achieve this?
1 Solution
There really isn't any "syntax."  What you need to do is link tables from the other database.  There's several ways to get this done, but I prefer to:
Right-click in the database window and select "Link tables..."  You will be prompted to designate the name of the database where the table(s) reside, then you can select which tables in that database you want to attach to this database.  Linked tables are generally treated the same as tables which are local to the database; you can put the tables in queries, bind forms to them, etc.
If you want to link the table programmatically you can do this:

 DoCmd.TransferDatabase acLink, "Microsoft Access", MyMDB, acTable, MyTableName1, MyTableName2

where MyMDB is a string representing a path to you databas, MyTableName1 is the name of the table linking to in your database, and MyTableName2 is what you want to name the table in your current database.

From there you can get your recordsource from you newly linked table.
On Open, run this code

Private Sub Form_Open(Cancel As Integer)

    DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\SomePath\SomeDB.MDB", acTable, "<SourceTableName>", "<LocalTableName>"

End Sub

Then, if you only want to use the table, but not keep it,

Private Sub Form_Close()

    Dim d as Database
    Set d = CurrentDB
    d.tableDefs.Delete "<LocalTableName>"

End Sub


Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

rfedykAuthor Commented:

Thanks again


I'm going to use Believer's suggestion because there are over 70 tables from different MDB that are needed for this application.  I'll use your suggestion in the future so I'm going to post a "Points for MikeRenz" question.


Sorry buddy,  Mike got there before you

I didn't realize you wanted every table.  Here is some good code for linking every table from another database...

Private Sub AttachAllTables(MyPath a String)
Dim MyDatabase As Database
Dim tdf As TableDef
Dim ReturnValue As Integer

    Set MyDatabase = DBEngine.OpenDatabase(MyPath) ' Open database.
    For Each tdf In MyDatabase.TableDefs
        If tdf.Attributes And dbSystemObject Then
            ReturnValue = SysCmd(SYSCMD_SETSTATUS, "Linking Table " & tdf.name)
            DoCmd.TransferDatabase acLink, "Microsoft Access", MyPath, acTable, tdf.name, tdf.name
        End If

    ReturnValue = SysCmd(SYSCMD_REMOVEMETER)
ooops...make that:       MyPath As String

and at the end add:      End Sub

sorry for not double checking before posting.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now