Is there a way to Select from 2 MsAccess DBs?

Posted on 2004-09-24
Last Modified: 2010-05-02
How can I do something like:
    sSel = "SELECT * FROM T1, T2" _
              & " WHERE T1.objid = T2.objidT1"
    rst.Open sSel, cnACL, adOpenKeyset, adLockOptimistic

but T1 and T2 are in different MsAccess DBs?
thank you
Question by:spoowiz
LVL 26

Expert Comment

ID: 12145131
You may want to join the table first

Author Comment

ID: 12145164
Can you give me a sample syntax? thanks
LVL 26

Accepted Solution

EDDYKT earned 125 total points
ID: 12145334
Sorry I mean link table. You have to do it from access
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

LVL 76

Assisted Solution

by:David Lee
David Lee earned 125 total points
ID: 12145528
In general you can do it like this:

    Dim adoCon1 As New ADODB.Connection, _
        adoCon2 As New ADODB.Connection, _
        adoRS1 As New ADODB.Recordset, _
        adoRS2 As New ADODB.Recordset
    adoCon1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB1.Mdb;Persist Security Info=False"
    adoCon2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB2.Mdb;Persist Security Info=False"
    adoRS1.Open "Select * from T1", adoCon1, adOpenDynamic, adLockOptimistic
    adoRS2.Open "Select * from T2 Where objID = " & adoRS1.Fields("objID"), adoCon2, adOpenKeyset, adLockOptimistic

You'll need to modify the code to meet your specific situation.  For example, if objID is a character string, then you'll need to enclose it in quotes in the select statement.
LVL 13

Assisted Solution

Michael_D earned 125 total points
ID: 12145535
You can link tables from VB. Here the code I am using to create linked table in Access:

Sub ADOCreateAttachedJetTable(ByVal tablepath As String, ByVal tableName As String, ByVal fileName As String)

   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table

   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source='c:\temp\db2.mdb';"

   ' Set the name and target catalog for the table
   tbl.Name = tableName
   Set tbl.ParentCatalog = cat

   ' Set the properties to create the link
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Link Datasource") = tablepath
   tbl.Properties("Jet OLEDB:Link Provider String") = "text;database= " & tablepath & ";FMT=Delimited;HDR=Yes"
   tbl.Properties("Jet OLEDB:Remote Table Name") = fileName

   ' Append the table to the collection
   cat.Tables.Append tbl

   Set cat = Nothing

End Sub
Sub ADORefreshLinks(ByVal tableName As String)

   Dim cat As New ADOX.Catalog
   Dim tbl As ADOX.Table

   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source='c:\temp\db2.mdb';"

   For Each tbl In cat.Tables
      ' Check to make sure table is a linked table.
      If tbl.Type = "LINK" Then
         Debug.Print tbl.Name
         'table name is case sensitive ...
         If UCase(tbl.Name) = UCase(tableName) Then
            cat.Tables.Delete tbl.Name
            ADOCreateAttachedJetTable "c:\", "newTable", "Buildlog1"
         End If
      End If

End Sub

This is for linking text file but you can easy convert to another database

Private Sub Form_Load()
    ADORefreshLinks "newTable"
End Sub

Author Comment

ID: 12145612
Wow. Great tips from all of you. I'm increasing points to 375 so each can get 125.
I will accept Eddykt's solution because it was first, simple (i already know how, didn't think of it), and works.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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