VBA to Save Query Results to Temp Table

[ChuckRobert]

I am trying to save the results of a query called qry_RosterBase to a temporary table called tbl_TempForRoster. The query formats data from the Personnel Table for the Roster report. I am hoping that the numerous queries required to generate the Roster report will run faster by referencing tbl_TempForRoster when it opens, instead of qry_RosterBase. The code below is activated with a button click. If I comment out the final line “DoCmd.DeleteObjectacTable, strTable”, I can see that the code does make the temp table, and copy all data from the query to the temp table, but only after clicking the button a second time. If I do not comment out the last line, the temp table is never built. I also tried to get just some of the query by changing “Select * to “Select qry_RosterBase.Name, qry_RosterBase.Assignment….. In this case it would copy it over on the second click if only one field is entered in the select statement, but not with more than one. Suggestions?

[COLOR=black][FONT=Times New Roman]Dim strSQL As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Dim strTable As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]strTable = "tbl_TempForRoster"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]'Delete the table if it exists<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]DoCmd.DeleteObject acTable, strTable<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]strSQL = "Select * INTO " & strTable & "FROM qry_RosterBase "<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]CurrentDb.Execute strSQL<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]'Do required actions…, then delete temp table<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]<o:p> </o:p>[/FONT][/COLOR][COLOR=black][FONT=Times New Roman]DoCmd.DeleteObject acTable, strTable[/FONT][/COLOR]

<o></o>

 

[SydneyGeek]

Instead of deleting the object and recreating it, why not delete and append the data? Something like...

 Dim strSQL_Delete As String
 Dim strSQL_Append As String
 Dim strTable As String
 strTable = "tbl_TempForRoster"
 strSQL_Delete = "Delete * FROM " & strTable
 strSQL_Append = "Insert * INTO " & strTable & "FROM qry_RosterBase "
 DoCmd.SetWarnings False
 'Delete the data and refresh it from the query
 CurrentDB.Execute strSQL_Delete
 CurrentDb.Execute strSQL_Append
 DoCmd.SetWarnings True

There are several advantages to this approach, including that you can create indexes on fields that will be searched or filtered, for better performance.
And there is no need to delete the table in order to refresh the data.

Denis

 

[ChuckRobert]

Denis,<o></o>
Thanks for your feedback! I tried to implement this code, but for some reason it will not process the CurrentDb.Execute strSQL_Append command. Everything to that point works. I am certain the query name is correct, so I do not know why it stops there. <o></o>
I’m wondering if the database may be corrupt. This query is the foundation for building the Roster report, and the Roster report is taking longer than it should to build, and it does not print. On print, an error message displays "Cannot open any more tables", which I find odd as it is based on only 1 table, but the report does include many queries/sub-reports, DLookups, and about 12 crosstab queries are used to display required data in the report. <o></o>
Suggestions?

 

[SydneyGeek]

You may need a different syntax for the Append statement. I got lazy. Try ...
INSERT INTO tbl_tmpForRoster ( comma delimited list of fields in here )
SELECT * FROM qry_RosterBase

Denis

 

[ChuckRobert]

Denis, Thanks again! I'm off until Wednesday, but will try that then!

 

[ChuckRobert]

Denis,<o></o>
Thanks again for your guidance on this! <o></o>
Just to follow-up for those who are working a similar project... The base query [qry_RosterBase] pre-formats as much data as possible to support the Personnel Roster. Numerous underlying queries referenced the base query to generate sub-reports for each possible section and shift. The Personnel Roster contains many embedded sub-reports and was taking a long time to load. <o></o>
However, when this code transfers the minimum required fields from the base query to a temp table, and I re-directed all sub-queries to that, processing time dropped from 40-55seconds to about 6 seconds. I can live with that. <o></o>
Here is the code that transfers data from the base query to the temp table…

<o> </o>
Private Sub TransferQueryToTable_Click()<o></o>
Dim strSQL_Delete As String<o></o>
Dim strSQL_Insert As String<o></o>
Dim strTable As String<o></o>
strTable = "tbl_TempForRoster"<o></o>
strSQL_Delete = ""<o></o>
strSQL_Insert = ""<o></o>
strSQL_Delete = "Delete * FROM " & strTable<o></o>
strSQL_Insert = " INSERT INTO tbl_TempForRoster "_<o></o>
& "SELECT CName ," _<o></o>
& "RDOs ," _<o></o>
& "Rank ," _<o></o>
& "Rank_Order ," _<o></o>
& "Shift ," _<o></o>
& "Assignment ," _<o></o>
& "WpnsQual FROM [qry_RosterBase];"<o></o>
CurrentDb.Execute strSQL_Delete<o></o>
CurrentDb.Execute strSQL_Insert<o></o>
'MsgBox "Query data loaded to temp table."<o></o>

End Sub<o></o>

<o> </o>
Thanks again, Denis!<o></o>
<o></o>