Export data on scheduled basis when no user in database?

[Guest]

Is there any way I can run a procedure within my database unattended in the
middle of the night? I need to do a data export when no user is in the
application. Currently, I have this all set up to run when a command button
is pressed, several Transfertext commands need to be run and then some
special code to export the files to an FTP server.

 

[Guest]

Klatuu suggests the following:
Create a Macro that runs the export/query/code.
Use Windows Task Scheduler to schedule the event.
Use the /x macroname command line option to cause the macro to execute when
your mdb is opened.

The command line would look something like:
"D:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "C:\Documents and
Settings\whittlej\Playground.mdb" /X macRunQuery

This will open up Access and run the query. If you already have the database
open, it will open up another instance of it. Of course your computer needs
to be on. Also if you are getting the stupid security warning, that will
block it.
------------------
Here's another way that requires the database to be already open:

Create a form named frmTimer. In the On Open Event put the following code:

Private Sub Form_Open(Cancel As Integer)
Me.Visible = False
End Sub

In the Timer Interval event put 3600000 which is one hour.

Put the following code in the On Timer Event with the proper hour and
commands. It includes examples of exporting a spreadsheet and running a
query. Watch out for wrapping on the longer lines:

Private Sub Form_Timer()

'Runs at 10 pm. Change the 22 to the 24 hour value when you want it to run.
If Hour(Now) = 22 Then
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table
Name", "E:\SheetName.XLS"
' DoCmd.OpenQuery "YourQueryName"

DoCmd.SetWarnings True
End If

End Sub

Make or modify a macro named AUTOEXEC that opens frmTimer. Both Access and
this form must be open (but not necessarily visible) for the above to work.

 

[Guest]

Thanks for the response.
Do you do this via Control Panel--Settings because I could not see how to
specify the command line option. Also, I got an Access Denied Message - that
may mean I cannot use Task Scheduler at all.
Using this method, does the user have to be logged on? When we leave at
night we have to restart our PCs, so they will be turned on but we will be
logged out of Windows XP.

 

[Guest]

I forgot to ask, how do I pass the database password to the command line?
Thanks.

 

[Guest]

Hi,

I've never used Klatuu's idea of Task Scheduler for Access. I use the second
option of the form with a Timer setting. That does require that the database
is open which would mean that the user is logged on to the computer and
network.

 

[Guest]

I found the Task Scheduler works really well but my problem is that it only
runs when I am logged on to Windows. I need someway to open a database
whether I am logged on or not.

 

[Guest]

I am also trying to export data on a scheduled basis. Early in the thread
you mentioned not being able to enter a command line for Scheduled Tasks. I
cannot do this as well. Later on -- it seemed like you had figured a way
around this. Can you post how you were able to get a command line into the
Scheduled Task?

Thanks

 

[John Nurick]

Put the command line into a Windows batch file or script file, and set
up the scheduled task to run that file.