Need MS ACCESS QUERY

I need a query in access that run the three actions below in one action, like a sub-query style.
so I created a query that will drop the tables, and i created a saved import that imports the tables.
What I need is to be able to run a single action that will run all three of these task,
1. qry_drop ( Query that drops the tables)
2. qry_import ( a Saved import that imports the tables)
3. facilityCharges ( the query that needs to run)
LVL 3
TheonWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
create a macro with Action (save macro as myMacro)

Action
RunCode


Function Name  RunMyQueries()



Create a function in a Regular module

Function RunMyQueries()

docmd.openquery "qry_drop"
docmd.openquery "qry_Import"
docmd.openquery "facilityCharges"

end function


now all you need to do is call the macro  MyMacro to run the queries

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Note that in cases like this, you will need iron-clad error handling and perhaps "Rollbacks" in case one of the queries fails...
...and/or the results of one query are dependent on the results of another...
TheonWAuthor Commented:
boag you are correct, I will need error handling,  since all queries depend on the success of the first ones, for example, their needs to be a check to see if the tables exsist, if not then run the second query, if they do then run the drop query. We have to make sure that the tables are dropped and  imported everytime to get the current data.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Rey Obrero (Capricorn1)Commented:
just revised the function to include the checking


Function RunMyQueries()

docmd.openquery "qry_drop"

'place code here to check if query runs properly

docmd.openquery "qry_Import"

'place code here to check if query runs properly
docmd.openquery "facilityCharges"

end function
Jeffrey CoachmanMIS LiasonCommented:
capricorn1 has now got you covered...
;-)
ggzfabCommented:
Instead of the drop & import you could use a linked table and define a query holding the facilityCharges logic to create the finally needed table.

Personally I often use an intermediate table holding the sourcedata and an additional runnumber, thus creating a history of imports and the possibility to "go back in time" by filling the final table from a previous runnumber.
TheonWAuthor Commented:
ggzfab, the reason we do not use linked tables is because the query will not run in a timely manner, when the tables are linked we never get any results and it appears that the query is not even running, if we import the tables the same query runs in about 30 seconds.
TheonWAuthor Commented:
if someone could now just present the error handling procedure, then we can close out this question
Rey Obrero (Capricorn1)Commented:
TheonW,

i think your  original problem have already been given a solution..

post another Q about your other problem.. and in your other question give a thorough explanation of what needs to be checked for your error handling procedures.
Rey Obrero (Capricorn1)Commented:
a C grade ?
ggzfabCommented:
By using "currentdb.execute" instead of "docmd.openquery" you'll be able to add errorhandling when the query fails.
The format is:
currentdb.execute (" qry_drop",DbFailOnError)

Open in new window

The DbFailOnError will perform a roll-back when things go wrong.

Finally I wouldn't use a macro to start the function as it's easier to start the function from the code behind a button.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.