Been working on this problem for a full day. Using Access 2016 with file formats 2003. Database file is on shared department network.
I have a form with vba behind that upon clicking Start button, about 30 queries run in sequence saving us about 4-6 hours every time we do a What If (previously performed this work manually).
1) I had been using DoCmd.OpenQuery "query name" for all the queries which include mostly MakeTable queries. I used DAO to Append fields and to Update new fields as needed. No vba errors ever but where I ran into a problem was in final results - one or two tables did not get updated properly if I ran through the queries too fast (even with SetWarnings on). So, researched internet and found suggestion to use DAO Execute in place of DoCmd.OpenQuery for action queries. My code changed to 2)
2) Set myDb = CurrentDb
myDb.Execute "query name", dbFailOnError
DoEvents
Set myDb = Nothing
This new code replaced all the DoCmd.OpenQuery code for action queries. I had a few Select queries and left the code as DoCmd (per web research).
Problem: The new code causes Error and exit from Sub not for syntax error but if for example a table already exists when I run Execute against a Make-Table query. The DoCmd.OpenQuery does not exist sub but allows the current table (same name as the MakeTable name) to just be deleted I guess; no error and vba sequence continues.
Since we run this routine over and over, I don't want to manually delete all tables that are named in my MakeTable queries.
Does anyone have a solution or suggestion for using DAO Execute query method that does not stall out if table is already in object list?
Thanks for any suggestions.
JJL
I have a form with vba behind that upon clicking Start button, about 30 queries run in sequence saving us about 4-6 hours every time we do a What If (previously performed this work manually).
1) I had been using DoCmd.OpenQuery "query name" for all the queries which include mostly MakeTable queries. I used DAO to Append fields and to Update new fields as needed. No vba errors ever but where I ran into a problem was in final results - one or two tables did not get updated properly if I ran through the queries too fast (even with SetWarnings on). So, researched internet and found suggestion to use DAO Execute in place of DoCmd.OpenQuery for action queries. My code changed to 2)
2) Set myDb = CurrentDb
myDb.Execute "query name", dbFailOnError
DoEvents
Set myDb = Nothing
This new code replaced all the DoCmd.OpenQuery code for action queries. I had a few Select queries and left the code as DoCmd (per web research).
Problem: The new code causes Error and exit from Sub not for syntax error but if for example a table already exists when I run Execute against a Make-Table query. The DoCmd.OpenQuery does not exist sub but allows the current table (same name as the MakeTable name) to just be deleted I guess; no error and vba sequence continues.
Since we run this routine over and over, I don't want to manually delete all tables that are named in my MakeTable queries.
Does anyone have a solution or suggestion for using DAO Execute query method that does not stall out if table is already in object list?
Thanks for any suggestions.
JJL