?
Solved

Is there a way to Select from 2 MsAccess DBs?

Posted on 2004-09-24
6
Medium Priority
?
196 Views
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
0
Comment
Question by:spoowiz
6 Comments
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12145131
You may want to join the table first
0
 

Author Comment

by:spoowiz
ID: 12145164
Meaning?
Can you give me a sample syntax? thanks
0
 
LVL 26

Accepted Solution

by:
EDDYKT earned 500 total points
ID: 12145334
Sorry I mean link table. You have to do it from access
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 500 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.
0
 
LVL 13

Assisted Solution

by:Michael_D
Michael_D earned 500 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
            cat.Tables.Refresh
            ADOCreateAttachedJetTable "c:\", "newTable", "Buildlog1"
         End If
      End If
   Next

End Sub


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

Private Sub Form_Load()
    ADORefreshLinks "newTable"
End Sub
 
0
 

Author Comment

by:spoowiz
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.
Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month17 days, 9 hours left to enroll

830 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