We help IT Professionals succeed at work.

RecordSource and external MDB table

rfedyk
rfedyk asked
on
394 Views
Last Modified: 2009-12-16
I want a form's RecordSource to be a table from an external MDB.  What is the syntax to achieve this?
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
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


Brian


Author

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

Commented:
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)

Commented:
ooops...make that:       MyPath As String

and at the end add:      End Sub


sorry for not double checking before posting.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.