Solved

RecordSource and external MDB table

Posted on 2000-03-13
6
340 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?
0
Comment
Question by:rfedyk
6 Comments
 
LVL 7

Accepted Solution

by:
Believer earned 100 total points
ID: 2612728
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2612764
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
 
LVL 9

Expert Comment

by:BrianWren
ID: 2613632
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:rfedyk
ID: 2614359
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2614416
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
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2614420
ooops...make that:       MyPath As String

and at the end add:      End Sub


sorry for not double checking before posting.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now