VBA Program to run a macro

[ChuckW]

Hi,

I have an access database for sales people which is fed
from Quickbooks. I have designed it to build make tables
in order for them to run various reports. I created a
macro called MakeTables that I want to schedule to have
run daily early in the morning. Right now, I manually go
into Access and run this macro but I heard the best way
is to right a vb program which runs this macro and the
schedule this program to run in the Schedule tasks.

I Can't use an Autoexec macro since that is currently
being used to open a password protected form for the
users. Plus if the make tables were part of the autoexec
macro then everytime someone opened a form they would
have to wait about 10 mintues for all of the make tables
to be created. I don't know much about VB. Can someone
tell me what I would need to do in order to make this
happen?

Thanks,

Chuck
..

 

[tina]

since nobody else has offered a "good" workable solution, here's one option
that might work, though it's clunky:

create a new table in the db, i'll call it tblCheckDate. add one field,
called CurDate, data type Date/Time, and enter one record, with today's date
in the field.

create an Update query, i'll call it qrySetDate, as

UPDATE tblCheckDate SET CurDate = Date();

you say that you have an autoexec macro in the db that opens a login form.
put the following macro actions *before* the first action you currently have
running in the autoexec macro, as

Condition: DCount(1, "tblCheckDate", "[CurDate] = #" & Date() & "#") < 1
Action: SetWarnings
Warnings On: No
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
QueryName: qrySetDate
_next macro line_
Condition: ...(ellipsis)
Action: OpenQuery
(run the first make-table query)
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
(run the next make-table query)
(add as many open query actions as you need for the make-table queries,
*making sure you use the ellipsis in the Condition column on each line*)
_next macro line_
Condition: ... (ellipsis)
Action: StopMacro
_next macro line_
the current "first" action in your current autoexec macro (and roll on)

set the scheduler to open the database in the early a.m., as you planned.
either set the scheduler to close the database after a specified time (so
the queries have time to run), or add a Quit action to the macro *before the
StopMacro action*, and remembering to include the ellipsis in the Condition
column.

here's what should happen: when the scheduler opens the db tomorrow a.m.,
the first thing the autoexec macro does is run the DCount. the count of
records in tblCheckDate will be less than 1, so the make-table queries will
all run and then the StopMacro action will run, ensuring that the rest of
the autoexec macro does NOT run. the next time anybody opens the database
*that same day*, the DCount will run, and the count of records in the
"first" table will NOT be less than one. so the macro will skip all the
actions involving the make-table queries, and go on to the normal OpenForm
actions for the user login.

as i said, clunky, but it should work.

hth

 

[ChuckW]

Tina,

Thanks for your help. Your solution worked just fine.

Chuck

-----Original Message-----
since nobody else has offered a "good" workable solution, here's one option
that might work, though it's clunky:

create a new table in the db, i'll call it tblCheckDate. add one field,
called CurDate, data type Date/Time, and enter one record, with today's date
in the field.

create an Update query, i'll call it qrySetDate, as

UPDATE tblCheckDate SET CurDate = Date();

you say that you have an autoexec macro in the db that opens a login form.
put the following macro actions *before* the first action you currently have
running in the autoexec macro, as

Condition: DCount(1, "tblCheckDate", "[CurDate] = #" & Date() & "#") < 1
Action: SetWarnings
Warnings On: No
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
QueryName: qrySetDate
_next macro line_
Condition: ...(ellipsis)
Action: OpenQuery
(run the first make-table query)
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
(run the next make-table query)
(add as many open query actions as you need for the make- table queries,
*making sure you use the ellipsis in the Condition column on each line*)
_next macro line_
Condition: ... (ellipsis)
Action: StopMacro
_next macro line_
the current "first" action in your current autoexec macro (and roll on)

set the scheduler to open the database in the early a.m., as you planned.
either set the scheduler to close the database after a specified time (so
the queries have time to run), or add a Quit action to the macro *before the
StopMacro action*, and remembering to include the ellipsis in the Condition
column.

here's what should happen: when the scheduler opens the db tomorrow a.m.,
the first thing the autoexec macro does is run the DCount. the count of
records in tblCheckDate will be less than 1, so the make- table queries will
all run and then the StopMacro action will run, ensuring that the rest of
the autoexec macro does NOT run. the next time anybody opens the database
*that same day*, the DCount will run, and the count of records in the
"first" table will NOT be less than one. so the macro will skip all the
actions involving the make-table queries, and go on to the normal OpenForm
actions for the user login.

as i said, clunky, but it should work.

hth

Hi,

I have an access database for sales people which is fed
from Quickbooks. I have designed it to build make tables
in order for them to run various reports. I created a
macro called MakeTables that I want to schedule to have
run daily early in the morning. Right now, I manually go
into Access and run this macro but I heard the best way
is to right a vb program which runs this macro and the
schedule this program to run in the Schedule tasks.

I Can't use an Autoexec macro since that is currently
being used to open a password protected form for the
users. Plus if the make tables were part of the autoexec
macro then everytime someone opened a form they would
have to wait about 10 mintues for all of the make tables
to be created. I don't know much about VB. Can someone
tell me what I would need to do in order to make this
happen?

Thanks,

Chuck
.

.

 

[ChuckW]

One other question tina. I have another database for
running weekly reports. I run a bunch of make tables in
a query just like the one below but I have set it can
only be run on Mondays. If it is run any other day it
would screw things up. The application is seldom used (3-
4 times a week) but not always every day. Is there a way
to modify your macro so that every monday the scheduler
opens and runs some make tables first thing but any other
time either later on a monday or during the week the make
tables are not created? Again, I have created an
autoexec that opens a password protected form.

Thanks,

Chuck

-----Original Message-----
since nobody else has offered a "good" workable solution, here's one option
that might work, though it's clunky:

create a new table in the db, i'll call it tblCheckDate. add one field,
called CurDate, data type Date/Time, and enter one record, with today's date
in the field.

create an Update query, i'll call it qrySetDate, as

UPDATE tblCheckDate SET CurDate = Date();

you say that you have an autoexec macro in the db that opens a login form.
put the following macro actions *before* the first action you currently have
running in the autoexec macro, as

Condition: DCount(1, "tblCheckDate", "[CurDate] = #" & Date() & "#") < 1
Action: SetWarnings
Warnings On: No
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
QueryName: qrySetDate
_next macro line_
Condition: ...(ellipsis)
Action: OpenQuery
(run the first make-table query)
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
(run the next make-table query)
(add as many open query actions as you need for the make- table queries,
*making sure you use the ellipsis in the Condition column on each line*)
_next macro line_
Condition: ... (ellipsis)
Action: StopMacro
_next macro line_
the current "first" action in your current autoexec macro (and roll on)

set the scheduler to open the database in the early a.m., as you planned.
either set the scheduler to close the database after a specified time (so
the queries have time to run), or add a Quit action to the macro *before the
StopMacro action*, and remembering to include the ellipsis in the Condition
column.

here's what should happen: when the scheduler opens the db tomorrow a.m.,
the first thing the autoexec macro does is run the DCount. the count of
records in tblCheckDate will be less than 1, so the make- table queries will
all run and then the StopMacro action will run, ensuring that the rest of
the autoexec macro does NOT run. the next time anybody opens the database
*that same day*, the DCount will run, and the count of records in the
"first" table will NOT be less than one. so the macro will skip all the
actions involving the make-table queries, and go on to the normal OpenForm
actions for the user login.

as i said, clunky, but it should work.

hth

Hi,

I have an access database for sales people which is fed
from Quickbooks. I have designed it to build make tables
in order for them to run various reports. I created a
macro called MakeTables that I want to schedule to have
run daily early in the morning. Right now, I manually go
into Access and run this macro but I heard the best way
is to right a vb program which runs this macro and the
schedule this program to run in the Schedule tasks.

I Can't use an Autoexec macro since that is currently
being used to open a password protected form for the
users. Plus if the make tables were part of the autoexec
macro then everytime someone opened a form they would
have to wait about 10 mintues for all of the make tables
to be created. I don't know much about VB. Can someone
tell me what I would need to do in order to make this
happen?

Thanks,

Chuck
.

.

 

[tina]

when using the same setup (tblCheckDate, qrySetDate, etc), you can just
modify the condition column of the macro to also check the day of the week,
as

Condition: DCount(1, "tblCheckDate", "[CurDate] = #" & Date() & "#") < 1
And DatePart("w", Date()) = 2
Action: SetWarnings
Warnings On: No
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
QueryName: qrySetDate
(and the rest of the macro setup as noted in my previous post, including
whatever modifications you made for your situation, of course!)

the DatePart() function returns the day of the week as a number. per Access
Help, the first day of the week defaults to Sunday. that makes Monday the
second day of the week, so the condition statement DatePart() = 2 will only
be true on Monday.

hth

One other question tina. I have another database for
running weekly reports. I run a bunch of make tables in
a query just like the one below but I have set it can
only be run on Mondays. If it is run any other day it
would screw things up. The application is seldom used (3-
4 times a week) but not always every day. Is there a way
to modify your macro so that every monday the scheduler
opens and runs some make tables first thing but any other
time either later on a monday or during the week the make
tables are not created? Again, I have created an
autoexec that opens a password protected form.

Thanks,

Chuck

-----Original Message-----
since nobody else has offered a "good" workable solution, here's one option
that might work, though it's clunky:

create a new table in the db, i'll call it tblCheckDate. add one field,
called CurDate, data type Date/Time, and enter one record, with today's date
in the field.

create an Update query, i'll call it qrySetDate, as

UPDATE tblCheckDate SET CurDate = Date();

you say that you have an autoexec macro in the db that opens a login form.
put the following macro actions *before* the first action you currently have
running in the autoexec macro, as

Condition: DCount(1, "tblCheckDate", "[CurDate] = #" & Date() & "#") < 1
Action: SetWarnings
Warnings On: No
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
QueryName: qrySetDate
_next macro line_
Condition: ...(ellipsis)
Action: OpenQuery
(run the first make-table query)
_next macro line_
Condition: ... (ellipsis)
Action: OpenQuery
(run the next make-table query)
(add as many open query actions as you need for the make- table queries,
*making sure you use the ellipsis in the Condition column on each line*)
_next macro line_
Condition: ... (ellipsis)
Action: StopMacro
_next macro line_
the current "first" action in your current autoexec macro (and roll on)

set the scheduler to open the database in the early a.m., as you planned.
either set the scheduler to close the database after a specified time (so
the queries have time to run), or add a Quit action to the macro *before the
StopMacro action*, and remembering to include the ellipsis in the Condition
column.

here's what should happen: when the scheduler opens the db tomorrow a.m.,
the first thing the autoexec macro does is run the DCount. the count of
records in tblCheckDate will be less than 1, so the make- table queries will
all run and then the StopMacro action will run, ensuring that the rest of
the autoexec macro does NOT run. the next time anybody opens the database
*that same day*, the DCount will run, and the count of records in the
"first" table will NOT be less than one. so the macro will skip all the
actions involving the make-table queries, and go on to the normal OpenForm
actions for the user login.

as i said, clunky, but it should work.

hth

Hi,

I have an access database for sales people which is fed
from Quickbooks. I have designed it to build make tables
in order for them to run various reports. I created a
macro called MakeTables that I want to schedule to have
run daily early in the morning. Right now, I manually go
into Access and run this macro but I heard the best way
is to right a vb program which runs this macro and the
schedule this program to run in the Schedule tasks.

I Can't use an Autoexec macro since that is currently
being used to open a password protected form for the
users. Plus if the make tables were part of the autoexec
macro then everytime someone opened a form they would
have to wait about 10 mintues for all of the make tables
to be created. I don't know much about VB. Can someone
tell me what I would need to do in order to make this
happen?

Thanks,

Chuck
.

.