VBA SQL Query of Multiple Databases

[VBA_Rookie]

Hello:

I have done some VBA coding in the past, but this is my first time working with the DAO 3.6 object model. The HMI software I'm using for a distributed control system incorporates VBA. In my application, I have several Access databases that each contain a single table (Stringtable) with the same field names. I want to be able to query this single table in all of the databases looking for certain events and display these in a listbox control on a user form. What would be the syntax of the SQL query to include the records from multiple databases into a single record set? Here is some of my code (???????? is my issue, but maybe I am not thinking about this correctly):

Dim ws_DL As Workspace
Dim db_Log1 As Database
Dim db_Log2 As Database
Dim db_Log3 As Database
Dim rs_Combined As Recordset
Dim sQuery As String

sQuery = "SELECT DateAndTime, Val FROM StringTable WHERE " & _
"(TagIndex = 1 AND Marker = 'S') ORDER by DateAndTime"

Set ws_DL = Workspaces(0)
Set db_Log1 = ws_DL.OpenDatabase("C:\ACCESS_Data\Log1.mdb")
Set db_Log2 = ws_DL.OpenDatabase("C:\ACCESS_Data\Log2.mdb")
Set db_Log3 = ws_DL.OpenDatabase("C:\ACCESS_Data\Log3.mdb")

Set rs_Combined = ????????.OpenRecordset(sQuery)

If I put the names of the database variables in place of ????????, the recordset is only from that database, but I'd like the recordset to contain the records from all of the databases. Then I can loop through the recordset to the .EOF, create strings, and add them to my listbox which already works OK. I know I can create a recordset for each database, but then I would have to merge them into some type of data structure, and sort them myself (which I may end up having to do anyway).

Can anyone help? Thanks -- David O.

p.s. --

 

[KenHigg]

Can you simply attach to the database/tables and use a union query?

 

[pbaldy]

Welcome to the site. This is a total WAG, but you may be able to use a UNION query along with the IN clause to put the values together. More info on both in Help.

 

[VBA_Rookie]

Thanks Ken and Paul -- I am a real novice with SQL. How would the sQuery string look to do what either of you suggest?

David

 

[pbaldy]

A UNION query is basically:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2

Ken's suggestion, and it's a good one, is to link to the various database tables so you can simply refer to each. If that's not feasible, then my idea involved using the IN clause to specify:

SELECT Field1, Field2
FROM Table1 IN <insert path here>

Check in help for the proper syntax of the IN clause. I've only used it in testing to help others, so I'm not really up to speed on it.