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?
LVL 1
rfedykAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BelieverConnect With a Mentor Commented:
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.
0
 
MikeRenzCommented:
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.
0
 
BrianWrenCommented:
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


Brian


0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
rfedykAuthor Commented:
Believer:

Thanks again

MikeRenz:

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.

BrianWren:

Sorry buddy,  Mike got there before you

0
 
MikeRenzCommented:
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
        Else
            ReturnValue = SysCmd(SYSCMD_SETSTATUS, "Linking Table " & tdf.name)
            DoCmd.TransferDatabase acLink, "Microsoft Access", MyPath, acTable, tdf.name, tdf.name
        End If
    Next

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

and at the end add:      End Sub


sorry for not double checking before posting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.