When the program starts, use the OpenDatabase command to open a DAO Database object connected to the first database. Examine its TableDefs collection to see if a link to the table in the other database already exists. If one does not, create a TableDef object describing the table and add it to the connection.

This makes a link to the other tables inside the database itself not just the Database object. That means you can close the Database object and the link is still there. In fact, other programs can now see the link. The link makes it seem as if the foreign table is in the local database to the programs.

Now connect the Data control to the database containing the link and set its RecordSource property to a query that performs the join.

Private Sub Form_Load()
Dim db_path As String
Dim db As Database
Dim table_def As TableDef
Dim query As String

    ' Get the database path.
    db_path = App.Path
    If Right$(db_path, 1) <> "\" Then db_path = db_path & _
        "\"

    ' Open the Employees database.
    Set db = OpenDatabase(db_path & "Employees")

    ' See if the link already exists.
    On Error Resume Next
    Set table_def = db.TableDefs("Salaries")
    If Err.Number <> 0 Then
        On Error GoTo 0
        ' The link does not yet exist. Create it.
        Set table_def = New TableDef

        ' Create a TableDef defining the Salaries tables
        ' in the second database.
        table_def.Connect = ";Database=" & db_path & _
            "EmployeeSalaries"

        ' The name in the foreign database.
        table_def.SourceTableName = "Salaries"

        ' Give it the same name in the local database.
        table_def.Name = "Salaries"

        ' Add the table definition describing the table
        ' in the other database.
        db.TableDefs.Append table_def

        MsgBox "Link created", vbInformation Or vbOKOnly, _
            "Link Created"
    End If
    On Error GoTo 0

    ' Close the database.
    db.Close

    ' Create the query treating both tables as local.
    query = "SELECT * FROM People, Salaries " & _
        "WHERE People.LastName  = Salaries.LastName" & _
        "  AND People.FirstName = Salaries.FirstName"

    ' Connect Data1 to the Employees database.
    Data1.DatabaseName = db_path & "Employees"

    ' Update the Data control.
    Data1.RecordSource = query
End Sub

Warning: When you remove the TableDefs entry for a link, the actual data still remains in the foreign database. On the other hand, if you delete the entry for a local table, the table is instantly and permanently removed with all of its data! Be sure you know whether you're deleting a table or a link.

This program removes the link when its form unloads. To test the Form_Load code to see what it does if the link already exists, comment this code out and run the program twice.

' Remove the database link.
Private Sub Form_Unload(Cancel As Integer)
Dim db_path As String
Dim db As Database

    ' Get the database path.
    db_path = App.Path
    If Right$(db_path, 1) <> "\" Then db_path = db_path & _
        "\"

    ' Open the Employees database.
    Set db = OpenDatabase(db_path & "Employees")

    ' Delete the link.
    db.TableDefs.Delete "Salaries"
    db.Close

    MsgBox "Link deleted", vbInformation Or vbOKOnly, "Link " & _
        "Deleted"
End Sub

See also Join tables in two different databases using an ADO join. The linking method described here is simpler, particularly if you want to leave the link in the database for later use possibly in other programs. Using an ADO join is easier if you just want to perform this one operation and you don't want to leave a link lying around.

For information on database programming using ADO.NET (in VB .NET), see my book Visual Basic .NET Database Programming.