Automating Access

[gbostock]

I'm new to Access but experienced with Oracle, SQL etc.

Here's what I want to do.

Create a macro to run 4 existing queries in sequence. Each query
depends on the results of the previous one. I want to send the results
of the final query to an excel file.

I've gotten as far as creating a macro with 4 openquery actions.

When I've got the macro working properly. I want to be able to use the
scheduled tasks program to run the macro at a regular time.

Any help will be appreciated.

 

[Guest]

Hi,

I think you will need to use VBA to do all of that. VBA will enable you to
"trap errors" and works well with Excel.

This sounds rather complex. If you are using these Access queries to do
calculations, perhaps it would be better to export the information to Excel
and let Excel do the calculations. You have not given us enough info to help
you very much with this.

Hunter57

 

[Pat Hartman \(MVP\)]

We need a little more information to guide you in the right direction.
Generally speaking, macros are the least desirable solution given their lack
of error trapping. If this is something for yourself, it doesn't matter but
if you are creating something for someone else, VBA is a better solution.
In versions of Access prior to A2007, there was an option on the macro menu
to convert a macro to code so if you don't know how to write the code, you
can create a macro and then convert it.

However, your solution really depends on what the queries do. If each query
makes a table or appends/updates an existing table, then running them in
series will accomplish your goal but you may not actually need to create
intermediate tables at all. Access allows you to use queries
interchangeably with tables so you can join queries to each other or to
tables. This allows you to simplify your query and build up to the final
recordset which may join or union several other queries.