Joining 2 DAO recordsets into 1 recordset or a temporary table MS Access 2010 using VBA

I have 2 similar queries, run on different sets of parameters.
One returns ~ 2700 records (in randomized order), the other I need to return 30% of 2700 randomized as well.
I would like to join the results of these queries into a single DAO recordset to act on using  VBA MS Access 2010.

Here is the code so far:
Option Compare Database
Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim qdf2 As DAO.QueryDef
Dim rst3 As DAO.Recordset
Dim qdf3 As DAO.QueryDef
Dim MusicPath(1 To 6) As Integer
Dim MultiPath(1 To 3) As Integer
Dim SinglePath(1 To 3) As Integer
Dim AdPath As Integer
Dim PROPath As Integer
Dim PSAPath As Integer
Dim NoRepeatDays As Integer
Dim AltFilePath As Integer
Dim AltFilePath2 As Integer
Dim AltFilePath3 As Integer

Dim HowMany As Integer
Dim LogDate As Date
Dim hrx As Integer
Dim hr As String


Private Sub Form_Load()
LogDate = #4/9/2018#

MusicPath(1) = DLookup("[Musicpath1]", "[Settings]")
MusicPath(2) = DLookup("[MusicPath2]", "[Settings]")
MusicPath(3) = DLookup("[MusicPath3]", "[Settings]")
MusicPath(4) = DLookup("[MusicPath4]", "[Settings]")
MusicPath(5) = DLookup("[MusicPath5]", "[Settings]")
MusicPath(6) = DLookup("[MusicPath6]", "[Settings]")
MultiPath(1) = DLookup("[MultiPath1]", "[Settings]")
MultiPath(2) = DLookup("[MultiPath2]", "[Settings]")
MultiPath(3) = DLookup("[MultiPath3]", "[Settings]")
SinglePath(1) = DLookup("[SinglePath1]", "[Settings]")
SinglePath(2) = DLookup("[SinglePath2]", "[Settings]")
SinglePath(3) = DLookup("[SinglePath3]", "[Settings]")
AdPath = DLookup("[AdPath]", "[Settings]")
PROPath = DLookup("[PROPath]", "[Settings]")
PSAPath = DLookup("[PSAPath]", "[Settings]")
NoRepeatDays = DLookup("[NoRepeatDays]", "[Settings]")

Set dbs = CurrentDb
Set qdf = CurrentDb.QueryDefs("[Audio1 Query]")
qdf.Parameters("[Log Date]").Value = LogDate
qdf.Parameters("[a]").Value = MusicPath(1)
qdf.Parameters("[b]").Value = MusicPath(2)
qdf.Parameters("[c]").Value = MusicPath(3)
qdf.Parameters("[d]").Value = MusicPath(4)
qdf.Parameters("[e]").Value = MusicPath(5)
qdf.Parameters("[f]").Value = MusicPath(6)
qdf.Parameters("[dd]").Value = NoRepeatDays
Set rst = qdf.OpenRecordset
HowMany = rst.RecordCount

   Set qdf2 = CurrentDb.QueryDefs("[Audio1 Multi AltMusic]")
   qdf2.Parameters("[Log Date]").Value = LogDate
   qdf2.Parameters("[AltFilePath]").Value = AltFilePath
   qdf2.Parameters("[AltFilePath2]").Value = AltFilePath2
   qdf2.Parameters("[AltFilePath3]").Value = AltFilePath3
   qdf2.Parameters("[dd]").Value = NoRepeatDays
   Set rst2 = qdf2.OpenRecordset
 
rst.Close
Set rst = Nothing
rst2.Close
Set rst2 = Nothing

End Sub

Open in new window


Any help would be appreciated.
Thank you,
Russ
Russ CummingsIT ConsulantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You have two basic choices:

1. Execute each and write to a temp table (INSERT INTO), then open a single recordset on the temp table

2. Use a UNION with the two SQL statements.   Number of output fields however must match, and you can deal with that if they don't.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, on these:

<<MusicPath(1) = DLookup("[Musicpath1]", "[Settings]")>>

  You would be better off to open a recordset with so many Dlookups().

  Probably is not terribly slow given that everything is in the same record as that record will be in the cache, but there is additional overhead as each Dlookup() is equivalent to opening a recordset.

Jim.
0
Russ CummingsIT ConsulantAuthor Commented:
Jim,
Thank you. The option of writing the recordsets out to a single table sounds right, then I can open the single table to select records from - as needed.
Please give me simple example of the code needed.
Do I need to specify each field in the recordset(s), or can I just move records into the table?
Would the code be the same for both recordset, as the fields are the same?
Thanks,
Russ
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
At it simplest, you would take your two exiting querydefs and using the query designer, change them to append queries.   Your code then would be:

Set dbs = CurrentDb

' Delete records in temp table.
' Note on this, you could create a delete query in the query designer and execute just like we are going to do below.
strSQL = "DELETE * FROM <tmpTableName>"
dbs.Execute strSQL, dbFailOnError

' Now fill the table with the required records.
Set qdf = dbs.QueryDefs("[Audio1 Query]")
qdf.Parameters("[Log Date]").Value = LogDate
qdf.Parameters("[a]").Value = MusicPath(1)
qdf.Parameters("[b]").Value = MusicPath(2)
qdf.Parameters("[c]").Value = MusicPath(3)
qdf.Parameters("[d]").Value = MusicPath(4)
qdf.Parameters("[e]").Value = MusicPath(5)
qdf.Parameters("[f]").Value = MusicPath(6)
qdf.Parameters("[dd]").Value = NoRepeatDays

 qrydef.Execute dbFailOnError

Open in new window


 and this would insert the records.  

 after executing the two inserts, then you would simply open a recordset on the table.

Jim.
0
Russ CummingsIT ConsulantAuthor Commented:
Jim,
 I'm slow to pick on what you're suggesting I do.
I understand creating a tmpTable with the fields I need from the queries / recordsets I have; and deleting the contents if necessary.
As I asked earlier how do I use the INTO instruction to copy the recordset(s) into the tmpTable?
You showed me:
Set qdf = dbs.QueryDefs("[Audio1 Query]")
qdf.Parameters("[Log Date]").Value = LogDate
qdf.Parameters("[a]").Value = MusicPath(1)
qdf.Parameters("[b]").Value = MusicPath(2)
qdf.Parameters("[c]").Value = MusicPath(3)
qdf.Parameters("[d]").Value = MusicPath(4)
qdf.Parameters("[e]").Value = MusicPath(5)
qdf.Parameters("[f]").Value = MusicPath(6)
qdf.Parameters("[dd]").Value = NoRepeatDays

 qrydef.Execute dbFailOnError

Open in new window


Which is part of my original code, except for the last line - qrydef.Execute dbFailOnError
Where does the qrydef come from? Is this how I set where the data from the predefined query will go.
Please elaborate.

Thank you for your time & patience,

Russ
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Where does the qrydef come from? >>

  Take your existing  [Audio1 Query], open it in the query designer, and change the query type to append.  You will then have a row for indicating which field the data will be placed in.   You'll also be prompted for the table name that you are appending to (which would be the temp table).

  Now if you want to see the resulting SQL INSERT INTO, switch to SQL view.

 The query designer is a fast way to do things graphically, and then see the resulting SQL statement.   Likewise, you can take a SQL statement from code, paste it into SQL view, and with a few adjustments, switch to design view and then work with it graphically.

 This helps quite a bit when you want to work with a complex query.  

 And note that you could use a SQL statement directly in code (and maybe this is the point of your question), but since you already had the code in place for working with the querydef, I stuck with that.

 But a querydef is nothing more than a saved SQL statement, along with some properties that control the SQL statement (ie UniqueRecords) and some connection properties (i.e. Timeout) that are used when query is executed.   You can do all that in code if you need to.

 I can show you how that would look if you'll paste the resulting SQL here after you've made the changes in the query def.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.