VBA Code to delete table and then run 4 append queries to refresh data

[Graham63]

Hi, I'm looking for one of you nice experts to put me on the road to solving this. I've got the separate action queries to run but as you might imagine thats all too complicated. I'm not sure how to start I'm a little familiar with code but writing it isn't always straight forward. Thanks for any help in advance

I have a table called "EquipmentRequired" which is populated by 4 append queries, 5000BaseReq, 6000BaseReq,6000IFBBReq,EquipmentReq which get some of their information by counting fields in another table but all have the same field names. The queries contain all data that is initially used to append new records to the table and this works fine.
Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.

 

[TJPoorman]

CurrentDB.Execute("Your SQL String Here")

 

[MarkK]

Why do you have to run Append queries? Typically you permanently store your raw data in tables. Then you write Select queries that summarize or otherwise process that data, but for all intents and purposes a Select query is indistinguishable from a table, so there is no reason to have to move or Append the data in order to summarize it. Just query it. And query your queries. And query your queried queries. No appending required.

 

[Graham63]

Thanks!

I set out with select queries but couldn't query it out before, you have forced me to have a little re-think! Can I come back to you?

 

[mh123]

to carry on as you are you could just

DoCmd.SetWarning False
DoCmd.DeleteObject tblhere
DoCmd.OpenQuery 1st query
^^ 2nd query
^^ 3rd query
^^ 4th query
DoCmd.SetWarnings True

But as stated above it may be worth looking into making your structure first!

 

[BlueIshDan]

Funny how we all run queries differently.
I use DoCmd.RunSQL
mh123 uses DoCmd.OpenQuery
and TJPoorman uses CurrentDB.Execute haha

 

[namliam]

Only 'proper' way to execute action queries for which you want no popups is currentdb.execute

Openquery should only be used for Select statements that need to sit on the screen for the user

 

[TJPoorman]

Funny how we all run queries differently.
I use DoCmd.RunSQL
mh123 uses DoCmd.OpenQuery
and TJPoorman uses CurrentDB.Execute haha

Depends on my use. Typically I use DoCmd.RunSQL with turning warnings off.
Unless I am running an INSERT statement with parent and child records, then I use CurrentDB.Execute to be able to query the parent ID that was just inserted.

 

[MarkK]

I always figured that the shortest distance is the CurrentDb.Execute method, since DAO is--as I understand it--native to the Jet Database Engine. DoCmd, by contrast, is provided by Access, which invokes the warnings infrastructure--even if the setting is false--and must, in the end, leverage DAO at some point anyway.

 

[BlueIshDan]

What we have here, ladies and gents, is an educated man who knows his $4!#.
Thank you for that