Automate Query

[Diane Walker]

We have Access 2003. Is there a way to automate the query? This query will
output records to a file. I need to run this query daily and don't like to
run it manually? Thanks.

 

[Guest]

The short answer is Yes. The complete answer depends on exactly what you are
doing.

What kind of file are you exporting to? Text? Spreadsheet?

Can the file have the same name each day and overwrite the previous file?

On what drive and folder does it go?

Do you want it to automatically run as a certain time of the day or do you
just want to push a button on a form?

 

[Diane Walker]

Thank you very much, Jerry for your prompt response.

1. I would like to export the file to Excel spreadsheet or to an Access
database

2. I would like to use the same name each day and overwrite the previous
file

3. It will go to drive "H" and the folder name is "IS"

4. I would like the Query to run automatically everyday at 4:00 a.m.

Please let me know if you have any questions. Thanks.

 

[Guest]

Easy enough, but there are some 'gotchas'.

Create the following function in a module. Change the names of the query and
spreadsheet as appropriate. Also watch out for word wrapping as acExport
should be on the same line as TransferSpreadsheet:

Function fSendFileAt4am()
If Time() >= #4:00:00 AM# And Time() < #5:00:00 AM# Then
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, , "TheQueryName",
"H:\IS\4amExport.xls"
DoCmd.SetWarnings True
End If
End Function

Then in a form that must be open (but not necessarily visible) put the
following in the On Timer Event using the Code Builder: fSendFileAt4am

It should look like:
Private Sub Form_Timer()
fSendFileAt4am
End Sub

In the Timer Interval put the following which equals one hour: 3600000

Now IF the database is open and IF the form is open, the query should be
exported to a spreadsheet somewhere between 4am and 5am every morning. You
could cut down on the Timer Interval and the times that are checked; however,
that will cause the Timer to fire more often.

 

[Diane Walker]

Thanks very much for your prompt response, Jerry. I have not used the
Function in Access. But, I will see how to put these statements in the
Function. Again, thank you very much for your assistance.

Diane