Solved

Is there a way to Select from 2 MsAccess DBs?

Posted on 2004-09-24
6
186 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
Comment Utility
You may want to join the table first
0
 

Author Comment

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

Accepted Solution

by:
EDDYKT earned 125 total points
Comment Utility
Sorry I mean link table. You have to do it from access
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…

771 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

13 Experts available now in Live!

Get 1:1 Help Now