daily automated export

[janetb]

Sorry if this isn't the correct group - suggestions welcome. Let me preface
by saying I'm not an access guru. Did try doing some searches but came up
with nothing. Will be using AccessXP on Novell server. Is this possible
with with host script file or should I use DTS to read into sql2000 and do it
that way?

Have a simple query populating report with subreports. Currently the
co-worker saves the report as rtf (which loses all line/box formatting), and
then emails to a set group. Wondered if it's possible to:
1. On a daily, set time basis (say 9am), run a query and save as xls file
to a fileshare on the server with the date being the filename?
2. On a daily, set time basis (say 9am), run the report and save as rtf (or
ideally something besides rtf that will save box/line formatting) and email
to group as an attachment.

Thanks for all suggestions.
Janet

 

[Joe Brown]

Funny thing...I received a notice from MS that I had a reply to my message
titled daily automated export. I did not post it. Janetb did.

 

[John Nurick]

Hi Janet,

I'm not a Novell person and don't know what scripting facilities are
available, or even whether a Novell server can run Access at all.

On a Windows system, there are at least three ways of running a query in
an Access/Jet database (i.e. an MDB file) and exporting to Excel at a
set time:

1) Write a script in your favourite OLE-aware language, that uses the
DAO library to instantiate a DBEngine object, open the database and run
the query. This doesn't involve Access per se, and works fine for simple
queries (but not ones that need parameters or that use VBA functions).
Then set up a scheduled event to run the script.

2) Write a VBA procedure in the database that exports the data. Then add
a macro that uses the RunCode action to call the procedure. Finally, set
up a script to run at the scheduled time that launches the database,
using the /x startup switch to fire the macro.

3) A script as per (1) that automates Access itself. This isn't as
simple as automating Word or Office; see
http://support.microsoft.com/?id=210111 for more.

(2) and (3) can be used for reports, which (1) can't.

As for the RTF issue: there's no really satisfactory answer. Printing
the report to a PDF file is one possibility; or export it to snapshot
format; or if it has to be RTF, try the Report Utilities at
http://www.lebans.com.

 

[yong362]

Sorry if this isn't the correct group - suggestions welcome. Let me preface
by saying I'm not an access guru. Did try doing some searches but came up
with nothing. Will be using AccessXP on Novell server. Is this possible
with with host script file or should I use DTS to read into sql2000 and do it
that way?

Have a simple query populating report with subreports. Currently the
co-worker saves the report as rtf (which loses all line/box formatting), and
then emails to a set group. Wondered if it's possible to:
1. On a daily, set time basis (say 9am), run a query and save as xls file
to a fileshare on the server with the date being the filename?
2. On a daily, set time basis (say 9am), run the report and save as rtf (or
ideally something besides rtf that will save box/line formatting) and email
to group as an attachment.

Thanks for all suggestions.
Janet