TheonW
asked on
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
capricorn1 has now got you covered...
;-)
;-)
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.
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.
ASKER
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.
ASKER
if someone could now just present the error handling procedure, then we can close out this question
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.
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.
a C grade ?
By using "currentdb.execute" instead of "docmd.openquery" you'll be able to add errorhandling when the query fails.
The format is:
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.
The format is:
currentdb.execute (" qry_drop",DbFailOnError)
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.
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...