MHUK,
I have several scheduled tasks that perform automated file copies, so I know about this. However, you might not want users playing with the task scheduler. For example, if your users want the report to run every Monday morning at 7:30 AM, task scheduler would be great. If they want it on a varying date or time every week, that would require visiting the task scheduler - something you might NOT want your users doing. So, besides giving an overview on the scheduler, solution number two would start you off on a method to trigger the report inside Access. To use that, the database would have to be open during working hours. Hope your users have enough memory installed!
Please note that quite a bit of legwork is required on your part for either solution. If you have the time and patience, it will increase your knowledge. (At least it certainly did mine.) If you are short on either, you might want to go outside this forum and work with someone one-on-one. Afraid that can't be me as I have Labrador Retriever puppies due and will be busy!
Two solutions to choose from:
1. Using the task scheduler.
First, you must be able to set up the database so the report runs automatically. I.E., double-clicking the .MDB file opens the database and an autoexec macro (auto starts - think autoexec.bat) - runs the report.
Create a .BAT file that does nothing more than open the database. Test it - make sure that double clicking the .BAT file from a command (DOS) prompt results in the report.
Then, you need to verify you have task scheduler installed or install it. Double click "My Computer". If you see an icon that says "Scheduled Tasks", you can continue. Otherwise you'll need to install it from your Windows 98 CD.
Once installed, create a scheduled task to execute the .BAT
To get instructions on this, double click the task icon and select help. Note there is a "Add Scheduled Task" button in the file list that triggers a wizard. Still, I recommend reading the help first.
Hopefully this is enough for you to follow through on.
The second solution comes from MS Knowledgebase solution number 158937. It's purpose is to show how to automatically perform an automatic compaction at a scheduled time. So, some of the code isn't suited to your purpose. And the following is a cut-and-paste. You have to figure out where changes are needed.
Hint: To allow user defined basis, add a date/time field to the form and allow users to set it. The logic to determine if it's time to run the report will be a little tricky. You should think through the possibilities you want to provide. Then you need to set a flag to indicate the report has been run. And the complexity increases if they want to schedule multiple runs in advance.
KNOWLEDGEBASE 158937 EXERPT FOLLOWS=====================
Create a new blank form and set the following properties:
Caption: Compact Databases
Default View: Single Form
Scrollbars: Neither
RecordSelectors: No
NavigationButtons: No
OnTimer: [Event Procedure]
TimerInterval: 60000 '60000 milliseconds=1 minute
Click the Build button next to the OnTimer property of the form and type the following procedure:
In Microsoft Access 7.0 and 97:
Private Sub Form_Timer()
'=====================================================
'The Timer event runs this code every minute. It compares your system time with the StartTime variable. When they match, it begins compacting all databases in the DBNames table.
'===========================================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 AM"
' If StartTime is now, open the DBNames table and start compacting
If Format(Now(), "medium time"
= Format(StartTime, _
"medium time"
Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames"
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder"
& "\" & RS("DBName"
' Create a new name for the compacted database.
' This example uses the old name plus the current date.
NewDbName = Left(DbName, Len(DbName) - 4)
NewDbName = NewDbName & " " & Format(Date, "MMDDYY"
& ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes
End If
End Sub
======================================================
END OF KNOWLEDGEBASE 158937 EXERPT
Like I said, a LOT of legwork either way.
There are several of us using Access where I work. BTW, I'm an antique mainframer. We collectively marvel at what IS possible using Access. I, however, also can't conceive how some seemingly simple tasks are so difficult...
Hope this helped even a little!
Bob (M.E.A.N.)
Moderately Exasperated Access Novice ;-)
