Solved

Is there a way to Select from 2 MsAccess DBs?

Posted on 2004-09-24
6
189 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…

791 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