Solved

Is there a way to Select from 2 MsAccess DBs?

Posted on 2004-09-24
6
187 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 125 total points
ID: 12145334
Sorry I mean link table. You have to do it from access
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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.
0
 
LVL 13

Assisted Solution

by:Michael_D
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
            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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

929 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

12 Experts available now in Live!

Get 1:1 Help Now