By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

cron or schedule

P: n/a
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?
Mar 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
DFS
ami'n wrote:
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?
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.

Mar 10 '08 #2

P: n/a
DFS
DFS wrote:
ami'n wrote:
>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?

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.
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"
Mar 10 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.