Automate a "Sendobject" maco

[macky]

In one of my databases (Access 97) I have been asked to e-mail some selected data to a specific person, I have done this using the Sendobject macro, (which works well).

This requirement is usually sent vis a control button on the 1st of every month, is there some way of automating this procedure so that it does not require someone to click the button.

I thank you in advance for your advice.

 

[aaahhhrrggghhhh]

Hi

One way of doing this is to :

1. make sure that the code that does the hard work is runnable from a macro.
2. set up a macro to do that, make sure that you give it a well recognisable name.
3. If your using W2K or WXP, go to control panel, select "Scheduled tasks". Set up a scheduled task to open your database on last day of each month, using the following dos run command :

"C:\Program Files\...\MSACCESS.EXE" "C:\mydb.mdb" /X Mymacroname

Here, the /X executes the macro, so if its in a scheduled task then, so long as your machine is switched on on the last day of the month at the time you specify, then the macro will run and do the emailing for you.

Hope this helps.
Mr Big. Dont be small. be BIG

 

[macky]

Thanks for your prompt reply,

We are on a LAN system here and are therefore using Windows NT, does this make any difference.

 

[aaahhhrrggghhhh]

Not that I know, the macro is dependant only on the access application itself, and the command is interpreted through the dos interpreter, so it would not be a problem. Try a test - write a macro that inserts a value and a timestamp into a temporary table, and set up a scheduled task to execute that, then see if it does it.

Let me know if there are any further probs.

Mr Big. Dont be small. be BIG

 

[macky]

As I am still very much on a learning curve regarding VB code, can you show the required code that I would need to use and where would I place it.

Thanks

 

[aaahhhrrggghhhh]

Hi Macky.

OK, here is what you do.

For the example, I will assume that your database is in :

\\myserver\mackydb$\mydb\mydb.mdb

If you have a home directory (that is backed up nightly), this is the best place for the batch file. Lets say this directory (using for the example) :

\\myserver\Data\Users\macky\myschedtasks

Find the path for msaccess.exe. It should be something like :

C:\Program Files\Microsoft Office\Office\msaccess.exe

OK, so open the database. Go to the Tables tab on the database window. Select "New". Create a table with the structure :

myname text
timestamp date (format:dd mm yyyy hh:nn:ss)

in the "timestamp" field, select the default value of '=Now()' (without the quotes)
call this table "tmptesttbl". You may accept that access creates a primary key, if you so wish. This is not so important.

Go to Macro's tab on the database window. Select "New". add the command : RunSQL and give it the following sql statement :

insert into tmptesttbl (myname) values ("macky&quot

now save the macro as tmptestmacro.

OK, so exit access.

now open either a text editor (that saves to dos format), or open a dos session, cd to the "\\myserver\Data\Users\macky\myschedtasks" folder, and type "edit tmpmacky.bat"

now for the task you should enter the line of text as :

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "\\myserver\mackydb$\mydb\mydb.mdb" /X tmptestmacro

please include the quotes as they tell windows that the statement does not use the old 8.3 format.

Save your batch file (if your using a text editor make sure that the file will be saved in DOS mode).

Now set up a scheduled task (I'm not sure how to do this in NT as I use Win XP), its probably in control panel, somewhere.

Make sure that the task runs in a few minutes time.

wait for it to happen.

did it work? If you open the database and look at the table we created earlier, there should be an entry with (approximately) the same time that you scheduled the task to run.

Now all you have to do is change the dos file so that it executes the macro that you wanted to run, and then change the scheduled task to do it when you want it to occur.

Please let me know if this helps.

Regards,
Mr Big. Dont be small. be BIG