Union query - two Access databases - from within Excel

[Katerine]

Ok. Fun one...

(working with Office 97)
I have an Excel file which automatically (via SQL) queries a time-tracking Access database and gets total time for all employees, for each cost code:

--- Begin Code ---
Dim dtBegin As Date, dtEnd As Date
Dim dbsDTDB As Database

Set dbsDTDB = OpenDatabase(strDTDBFileName)

' dtBegin and dtEnd are derived from an input
' box asking the user for the month
strSQL = "SELECT [Cost Code], [Description], " & _
"Sum([Time Spent]) AS [Total Minutes] " & _
"FROM [qryTimeEntryList-ForExportToTracie] " & _
"WHERE ([Date Worked] >= #" & Format(dtBegin, "Short Date&quot & _
&quot;# AND [Date Worked] <= #&quot; & Format(dtEnd, &quot;Short Date&quot & _
&quot;#) &quot; & _
&quot;GROUP BY [Cost Code], [Description] &quot; & _
&quot;ORDER BY [Cost Code]&quot;

Set rstTotals = dbsDTDB.OpenRecordset(strSQL, dbOpenDynaset)
--- End Code ---

After querying the database, it creates a new Excel file, populates it with the query results, and automatically generates charts of the data.

All this works well.

I would now like to expand this functionality, to combine these results with the results of a totally different Access time-tracking system (different employees use different systems). The fields are pretty comparable (or I can make it so they will be).. I just can't figure out how to generate a union query between these two totally unlinked and unrelated databases. I would just link them.. except if possible, I would like to do everything from within the Excel file's code, without ever having to manually go into the Access files each month.

Thanks in advance for any ideas/code you may have!

 

[DarkSun]

I'm not sure if this is what you want but have a look at this site.

http://support.microsoft.com/support/kb/articles/Q113/5/92.asp

 

[DarkSun]

If that doesn't help you can post the message on the Microsoft: Access Queries and Access/SQL Forum. I'm sure someone there could help you.

 

[Katerine]

Thanks for your help, DarkSun!

That article kind of led me to be able to answer my own question. FYI, I ended up linking the first database to the pertinant tables in the second, and then I changed the query &quot;qryTimeEntryList-ForExportToTracie&quot; (the query referenced in the Excel SQL statement) to a union query, so I didn't even have to change the Excel code at all..

Thanks again!
Katie