How do I get a Macro to run automatically?

S

Stockwell43

Hello,

I am using a simple Macro to send a report out of Access with a click of a
button. What I am wondering is:

How can I make it where the macro runs itself daily when the database is
opened? Also, how often can I have it run during the day and how would I do
that, say three times a day?

Thanks!!
 
S

Stefan Hoffmann

hi,
How can I make it where the macro runs itself daily when the database is
opened? Also, how often can I have it run during the day and how would I do
that, say three times a day?
Click to expand...
A macro named "AutoExec" is automatically executed when the database is
opened.
You only need to track in a separate table (a kind of a log) how often
it was called before this day.


mfG
--> stefan <--
 
S

Stockwell43

Hi Stefan, Thank you for responding.

Could you please elaborate a bit on the table and how I would tie the macro
to it?

Thanks!!
 
A

a a r o n . k e m p f

Jet doesn't support launching scripts automatically
for something like this; you need 'SQL Server Agent'

It comes with the FREE MSDE 2.0; and it can launch scripts on a
scheduled basis.. and send emails when it fails.

-Aaron
 
J

Jeff C

In addition to an auto exec macro there is a command line that can be used in
conjunction with Windows scheduler:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "Full Path to the
access databse" /x "name of macro"

create a scheduled task running msaccess.exe and modify the line in the run
window to reflect the syntax above.

You can search for additional info on access command lines.
 
S

Stefan Hoffmann

a said:
Jet doesn't support launching scripts automatically
for something like this; you need 'SQL Server Agent'
Click to expand...
Were talking about Access not Jet, were talking about macros not scripts...


mfG
--> stefan <--
 
K

Klatuu

Ignore aaron kempf. He is a known troll.
His answer, as usual, has nothing to do with your question.

As to making your macro run several times a day, it will depend on what else
the applcation does. If it only need to run to perform some maintenance task
and will not have a user interface, I recommend using the Windows Task
scheduler to kick the application off at specific times.

We do that here. In addition, we have Access installed on a server and run
it from the server so that if a particular user in not in the office, it
doesn't get skipped.
 
S

Stockwell43

Thank

Hi Klatuu,

It is a production report that needs to be sent to the managers during
updates. So instead of manually clicking a button to send it, I was hoping I
could get the macro to do it for me without clicking the button. In
otherwords behind the scene sort of thing. Is there a way to use the timer on
this?? Twice a day will work if need be.

Thanks!!
 
K

Klatuu

Sure, you could use a form timer to do that. Is this a single user
application? I doubt you want multiple reports going out.
 
S

Stockwell43

Currently, Me and my manager use it. She sends (I send) the report to one
manager right now but it to go to three managers soon.

Thanks!!
 
K

Klatuu

I think that at some point in the past, I pointed you to the api that will
allow you to determine who is the currently logged in user is (Windows User,
that is).
I would use that to determine whether to send the reports, then use the
timer event of a form that stays open as long as the application is running.
 
S

Stockwell43

I'm not following. Yes, I remember the link for see who is logged in but the
report is sent through email and the managers would receive it in their
inbox. Would I need to use the Who's Logged In code to do this? I can send
the report now through a click of a button, I was hoping to have access do it
on it's own at certain times of the day instead of me manually clicking the
button. For example: 9am and 2pm send the Production Report without me seeing
it.
 
K

Klatuu

What I am saying is that if you and your boss are both using the application
then both of you would send the report if you have it set up to send
automatically.

So, there are two ways you could approach this.
1. Add a time event to a form that is always open. Periodically check the
current time and at a predertmine time, check to see who the user is. If it
is the chosen user, email the reports. If it is not the current user, ignore
it.

2. You can use a startup command line to cause an access mdb to open with a
specific macro. So, you could write a macro that will kick off the email
process, and use the Windows Task Scheduler to start your application with
the macro.


As an example:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"C:\MSApps\MyApp.mdb /X EmailRpt"

would start your application and the macro named EmailRpt would run.
 
S

Stockwell43

Got It!!

I guess I didn't think about the report being sent from each open database.
I thought because it's one database it would only shoot out once no matter
how many users were in it. See, that's why I have to use this forum get help
from people like you. :eek:)

Thanks Klatuu, I will try one of these suggestions. I want to put this
project to rest so I can.
 
K

Klatuu

Please don't tell me you are sharing an application that resides on a shared
location.
Your database should be split and each of you should have your own copy of
the front end of your computer. If you are sharing an unsplit database, you
are asking for corruption.

But regardless, the application is running on your computer and on your
boss' computer. Neither of them know about the other so, yes, you need to be
aware of who is running the current instance so you know whether to send the
report or not.
 
S

Stockwell43

I can split the database, that's not a problem. I'll do that in the morning.
You're good man Klatuu and I always appreciate your help and taking the time
explain things to me.

Thank again and enjoy what's left of the day!!
 
A

a a r o n . k e m p f

if your application is not stable-- don't try to blame it on the
network.

Jet corrupts data. no matter what you do.
 
A

a a r o n . k e m p f

but SQL Server has this built in.

Select SUSER_SNAME() as ThisUser.

Jet is obsolete because it doesn't integrate natively with Windows
Authentication.
 
Top