Re: Automate Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Jerry Whittle" <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4BA4C020-7D8B-49D2-AC3B-9C789487367D@xxxxxxxxxxxxxxxx
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Diane Walker" wrote:

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.

"Jerry Whittle" <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC23556C-CE41-4E78-9815-11AF64E01409@xxxxxxxxxxxxxxxx
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?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Diane Walker" wrote:

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.


.

Relevant Pages

  • Re: Automate Query
    ... 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. ... Jerry Whittle, Microsoft Access MVP ... Keith Bontrager - Bicycle Builder. ...
    (microsoft.public.access.queries)
  • Re: Looking for Easiest Way to Create Report
    ... If the spreadsheet is blank (apart from the four header rows) before you run your query then that makes life very much easier than it would have been if you'd had to match values line by line. ... Then you can export the whole thing to a new Excel worksheet in one operation, ...
    (microsoft.public.access.gettingstarted)
  • Re: Looking for Easiest Way to Create Report
    ... By recording Excel macros and editing them you may be able to get all this down to one button push in each application. ... run your query then that makes life very much easier than it would have been if you'd had to match values line by line. ... So, if you do go for the query and TransferSpreadsheet route, that leaves you with the problem of levering in the four header rows. ... My guess is that the simplest solution will be in Excel, if you're prepared to do the job in two stages: one which generates the spreadsheet from Access, and another which requires you to move to Excel to add the headers. ...
    (microsoft.public.access.gettingstarted)
  • Re: Export to an already setup excel spreadsheet where columns<>
    ... Add an additional sheet to the workbook that you will use only for receiving ... Then, I just select the data in my query, by column, copy it, and then ... go into the excel sheet and select the first couple or three cells under the ... the spreadsheet seems to be 'protected' and I have NO idea what ...
    (microsoft.public.access.externaldata)
  • Re: Export to an already setup excel spreadsheet where columns<>
    ... Add an additional sheet to the workbook that you will use only for receiving ... Then, I just select the data in my query, by column, copy it, and then ... go into the excel sheet and select the first couple or three cells under the ... the spreadsheet seems to be 'protected' and I have NO idea what ...
    (microsoft.public.access.externaldata)