i'm also not newbie to Access but i'm not programmer. I have a need to
automate some task in Access. Every monday which is the first day of
the week, I have to send a MS Excel file containing data which
generated by MS Access. In my MS Access, I queries and filter a lot of
data from Oracle database which I connect using ODBC.

In this query, there is one perimeter need to fill up. The parameter
is date. It's a range of date. Every Monday I have to fill this
parameter from date of last Monday to last sunday. This is weekly data
need to be emailed out to my user.

Is there any mechanism to automate this task?

Well if you simply place some standard code in a module that runs what you
need, an does so without user intervention, then you can place that code in
the windows scheduler to run.

First, and foremost, DO NOT use parameters in the query, but simply provide
a "where" clause for the report (I am assuming this is a repot).


So, that means today is Monday, but you want the previous Monday to Sunday?
(basically 1 week previous)

So, you code might look like:

Sub MyReportRun()

Dim dtStart As Date
Dim dtEnd As Date

Dim strwhere As String


If Weekday(Date) <> 2 Then
' it is not Monday (2nd day of week)
' don't run this code
Exit Sub
End If

dtEnd = Date - 1 ' set date = 1 less then
' monday (sunday)

dtStart = DateAdd("ww", -1, Date) ' previous Monday

strwhere = "invoice date between " & _
qd(dtStart) & " and " & qd(dtEnd)

DoCmd.OpenReport "myReprot", , , strwhere


End Sub

Public Function qd(myDate As Variant) As String

' returns a formatted string of date, surrounded with # signs
' format is mm/dd/yyyy as required for sql, regardless of date setting
If IsNull(myDate) = True Then
qd = ""
Else
qd = "#" & Format(myDate, "mm/dd/yyyy") & "#"
End If

End Function


So, use the "where" clause in your code. If it actually sql and a reocrdset
that you looping through, then again just go:

strwhere = "invoice date between " & _
qd(dtStart) & " and " & qd(dtEnd)

strSql = "select * from tblCustomers where " & strWhere

set rstData = currentdb.OpenRecordSet(strSql)

' you code to do whatever goes here....

So, AFTER YOU GET YOUR code working that runs the above, YOU CAN THEN use
the following technique to schedule the above standard sub code in a
standard ms-access module in the windows scheduler:

http://www.members.shaw.ca/AlbertKal...obs/Index.html


so, I can't stress enough that you MUST create a sub in a standard module
that runs WITHOUT any prompts. Get that working, and test it. Then, and ONLY
then will you try the next step of scheduling the code to run as explained
above. In other words, you build that script that runs your sub code (that
explained in the above link). You then place that windows script in the
windows scheduler.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

ami'n wrote:
Start | Settings | Control Panel | Scheduled Tasks

Create a standalone Access file (.mdb or .mde)

Add a macro named 'autoexec' - it will execute when the Scheduled Task opens
the file
The autoexec macro will contain at least one TransferSpreadsheet statement
to output your data to an Excel sheet, and a Quit command to close the
system altogether.

The TransferSpreadsheet statement will take as an input your query with the
date parameters:

WHERE DatePeriod Between (Date() - 1) and (Date() - 7)

Note: the macro builder for TransferSpreadsheet says 'Table Name' but you
can enter a SELECT query.

Schedule this task to run anytime Monday, after 12:00:00am.

DFS wrote:
btw, the Run line in your Scheduled Task will need to be something like this

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"D:\MyAccessFile.mdb"