VBA How do I add/append a recordset to another recordset or table

[elusiveluderick]

Hi,

> I am going to be looping a query a couple of hundred times and want the recordset from each loop to be kept/stored. I am comfortable with the looping but I do not know how or where to store the information.
> Should I store the query recordset in a second recordset or a table? Each loop I will add records to this recordset/table so it will become increasingly larger. The recordset/table structure will always be the same
> How do I get the information from my query recordset into a second recordset/table using vba & sql?



>I have removed the loop and the majority of the code to simplify it to a single instance to make it more digestible

>Any comments or ideas to help are appreciated.

Thanks in advance

Ron

Sub Loopin()  
Dim QueryA As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQLA As String
Dim tblResults As DAO.Recordset




Set dbs = CurrentDb


'Query SQL String for Day
strSQLA = "SELECT [Product Code] " & _
"FROM S2;"




'Set Recordset from Query which is data to Import
Set QueryA = dbs.OpenRecordset(strSQLA)


'??Set Recordset for the Table to Insert into?? Do I have to do this
Set tblResults = dbs.OpenRecordset("R1")


'Add Results from Recordset QueryA into my results recordset/table??


'If I am going to be doing many loops and adding many times should I add to a recordset continoulsy then a table or keep adding to a table how do i do this?






End Sub

[Welshgasman]

I would just use an Append query, if it is a case of just copying the same data.?

Not sure you actually need to, but that is your choice.

[ssanfu]

I am going to be looping a query a couple of hundred times and want the recordset from each loop to be kept/stored.

Tables store data, so you need a table.

I would change the variable name from "tblResults" to "rsResults" since you are naming a variable for a recordset.
The table to save all the looped data could/would be named "tblResults"

And you need a few clean up lines at the end

    'clean up
    QueryA.Close
    rsResults.Close    '<- renamed
    Set QueryA = Nothing
    Set rsResults = Nothing     '<- renamed
    Set dbs = Nothing
End Sub

[Ron]

I would just use an Append query, if it is a case of just copying the same data.?

Not sure you actually need to, but that is your choice.

Thanks for the input. Do you know if I can "Append" a Recordset instead of an SQL Append Query of a table or is that not a thing?

[Ron]

Tables store data, so you need a table.

I would change the variable name from "tblResults" to "rsResults" since you are naming a variable for a recordset.
The table to save all the looped data could/would be named "tblResults"

And you need a few clean up lines at the end

    'clean up
    QueryA.Close
    rsResults.Close    '<- renamed
    Set QueryA = Nothing
    Set rsResults = Nothing     '<- renamed
    Set dbs = Nothing
End Sub

Thanks. Will do.

[Welshgasman]

Thanks for the input. Do you know if I can "Append" a Recordset instead of an SQL Append Query of a table or is that not a thing?

You can add records with a recordset and from a recordset, but I was thinking of this way?

https://docs.microsoft.com/en-us/off...oft-access-sql

[elusiveluderick]

Thanks alot for the help. With your assistance I ended up working it out. I have posted a simplified version of the code for anybody that it may assist in the future.
Essentially the process I have used is:
1) Have a table whose purpose is to temporarily hold data in my database "tblTemp"
2) Clear the data from the table "tblTemp" using a delete query
3) Run append query "qryTemp" to move data to "tblTemp"
4) Run append query "qryResults" to my results table "tblResults"
5) Loop Steps 2 to 4 until all required data is in results table

Option Compare Database

Sub Help()
Dim strSQLA As String
Dim strSQLDelete As String, strSQLAppend As String
Dim RCount As Integer
Dim dbs As DAO.Database


Set dbs = CurrentDb


For RCount = 1 To 10


'Thanks Welshgasman & ssanfu for the help


'Query SQL String for Deleting Data in Temporary Table
strSQLDelete = "DELETE tblTemp.* FROM tblTemp;"


'Run Delete Query
dbs.Execute strSQLDelete, dbFailOnError


'Query SQL String for Appending Data into Temporary Table NOTE that source table chnages on every loop P1>P2>P3...>P10 until end of loop
strSQLAppend = "INSERT INTO tblTemp ( [Field1], [Field2] ) " & _
"SELECT P" & RCount & ".FieldA, P" & RCount & ".FieldB " & _
"FROM P" & RCount & ";"


'Run Append Query
dbs.Execute strSQLAppend, dbFailOnError


'Query SQL String for Appending from Temporary Table to Results Table
strSQLAppend = "INSERT INTO tblResults ( [Field1], [Field2] ) " & _
"SELECT tblTemp.Field1, tblTemp.Field2 " & _
"FROM tblTemp;"


'Run Append Query
dbs.Execute strSQLAppend, dbFailOnError




Next RCount




End Sub

[Welshgasman]

Well I'm glad you got a solution, but normally a temporary table has to be used to clean the data.? You are merely putting the data from source to temp to results?

Cut out the middleman and just take from source direct to results?

[elusiveluderick]

The solution is greatly simplified.
The results require a couple of levels of calculations that require queries before going into the results table. The queries are also a bit long and to keep my sanity I broke a couple of the queries up that probably could have been combined. My computer complains less than my brain so it is working out so far.
There is actually initially 12 tables and 3 calculation queries for each loop I run. The source tables change for each loop. I hold the results of the queries in temp tables before running a final append query. This gets looped 252 times and the tables are immense so it takes about 4 hours to run. So I just let it go overnight.

[elusiveluderick]

I am sure you would find ways to make my code 1000% more efficient but as long as it works I am stoked.