Running multiple queries from code module

[rasage]

Hello,

Please help. I have about 17 queries that I am using to manipulate data. I am openeing them from a code module. Though it seems that every three times I run it (to test), it returns invalid data. I beleive it is running out of memory, once every couple times I run it...
Is the following appropriate or should I be using a querydef object. Is there a line of code that I am missing that may help with the memory leakage?

'run query to update EXEMPT
DoCmd.SetWarnings False
DoCmd.OpenQuery "delete_exempt", acNormal, acEdit
DoCmd.Close acQuery, "delete_exempt", acSaveNo
DoCmd.OpenQuery "append_human_to_exempt_all_exempt", acNormal, acEdit
DoCmd.Close acQuery, "append_human_to_exempt_all_exempt", acSaveNo
DoCmd.OpenQuery "update_exempt", acNormal, acEdit
DoCmd.Close acQuery, "update_exempt", acSaveNo
DoCmd.OpenQuery "SUM_EXEMPT", acNormal, acEdit
DoCmd.Close acQuery, "SUM_EXEMPT", acSaveNo
DoCmd.OpenQuery "delete_totexemp", acNormal, acEdit
DoCmd.Close acQuery, "delete_totexemp", acSaveNo
DoCmd.OpenQuery "SUMEXEMP_TO_TOTEXEMP", acNormal, acEdit
DoCmd.Close acQuery, "SUMEXEMP_TO_TOTEXEMP", acSaveNo
DoCmd.SetWarnings True

'run query to update NON-EXEMPT
DoCmd.SetWarnings False
DoCmd.OpenQuery "delete_nonexemp", acNormal, acEdit
DoCmd.Close acQuery, "delete_exempt", acSaveNo
DoCmd.OpenQuery "append_human_to_noexempt_all_nonexempt", acNormal, acEdit
DoCmd.Close acQuery, "append_human_to_noexempt_all_nonexempt", acSaveNo
DoCmd.OpenQuery "update_nonexempt", acNormal, acEdit
DoCmd.Close acQuery, "update_nonexempt", acSaveNo
DoCmd.OpenQuery "SUM_NONEXEMP", acNormal, acEdit
DoCmd.Close acQuery, "SUM_NONEXEMP", acSaveNo
DoCmd.OpenQuery "delete_totnone", acNormal, acEdit
DoCmd.Close acQuery, "delete_totnone", acSaveNo
DoCmd.OpenQuery "SUMNONE_TO_TOTNONE", acNormal, acEdit
DoCmd.Close acQuery, "SUMNONE_TO_TOTNONE", acSaveNo
DoCmd.SetWarnings True

'run query to delete HUMAN and append from TOTEXEMP & TOTNONE
DoCmd.SetWarnings False
DoCmd.OpenQuery "delete_human", acNormal, acEdit
DoCmd.Close acQuery, "delete_human", acSaveNo
DoCmd.OpenQuery "append_TOTEXEMP_TO_human", acNormal, acEdit
DoCmd.Close acQuery, "append_TOTEXEMP_TO_human", acSaveYes
DoCmd.OpenQuery "append_TOTNONE_TO_human", acNormal, acEdit
DoCmd.Close acQuery, "append_TOTNONE_TO_human", acSaveYes
DoCmd.SetWarnings True

...please help

 

[lewds]

Hi..

I not sure about all the opening and closeing of queries, but as long as I am running Action Queries, I convert the queries to SQL..as such

docmd.runsql "DELETE TableName.* FROM TableName;"

as a means to remove all data from a table or

docmd.runsql "SELECT INTO.......ect

as a means to append data to a table

 

[kramerica]

Rasage,

Lewds is correct.. it would be better to run them all as SQL statments. Then you wouldn't need to write all those queries.

What happens when you just run the queries.. (I've never used the docmd.close on a query before.. only on forms)

DoCmd.SetWarnings False
DoCmd.OpenQuery "delete_exempt", acNormal, acEdit
DoCmd.OpenQuery "append_human_to_exempt_all_exempt", acNormal, acEdit
DoCmd.OpenQuery "update_exempt", acNormal, acEdit


Try getting rid of the docmd.close statements.

Kramerica