automating a query to run at a certain time each day 1

[balllian]

Is it all possible/feasible to run an append query which runs once every day say monday to thursday but then on a friday run three times. I not sure what would need to be coded to make this possible

Any help with this would be greatly appreciated.

thanks in advance

 

[Tranman]

balllian,
One way would be to put the query in a different database that containes links to the table(s) you want to use/append to.

After creating the query, create a macro named autoexec, that runs the append query, then closes the application.

Then set up your Windows scheduler to open the second database whenever you want to. When the database opens, the macro named autoexec will run automatically.

That's just one fairly simple way...there are other ways that would be just as good.

Tranman

 

[balllian]

I see how that can work on a day to day basis. How will i be able to get this to run three times on a friday?? By the way am i getting this to run three times as to cover running a calcualtion on a sat + sun. Any further assistance would be greatly appreciated.

 

[tledwards]

I use an '.bat' file to run my updates each night using a command line like this. It starts Access opens the database in a exclusive mode (/excl) and runs mac_MyMacro (/x) to update tables. No second database is needed.

START c:\"Program Files"\"Microsoft Office"\Office11\Msaccess.exe c:\AC\My.mdb /excl /x mac_MyMacro

Your could just use Windows scheduler to run '.bat' file three different times on Friday.

My database grows a lot during this process so a use a different '.bat' file to compact it after the update. The command line looks like this.

START c:\"Program Files"\"Microsoft Office"\Office11\Msaccess.exe c:\Ac\My.mdb /compact

 

[Tranman]

balllian,
Oh, I see. Totally ignored the "three times on Friday thing", didn't I?

Sorry.

Here's a little snippet that will do what you want:

Public Function RunAppendQuery()
'Turn off confirmation messages
CurrentProject.Application.SetOption "Confirm Record Changes", False
CurrentProject.Application.SetOption "Confirm Document Deletions", False
CurrentProject.Application.SetOption "Confirm Action Queries", False

Select Case Format(Date, "ddd")
'Run query once on Mon, Tue, Wed, Thu
Case "Mon", "Tue", "Wed", "Thu"
DoCmd.OpenQuery "Query1"
'Run query 3 times on Fri
Case "Fri"
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query1"
End Select

'Turn on confirmation messages
CurrentProject.Application.SetOption "Confirm Record Changes", True
CurrentProject.Application.SetOption "Confirm Document Deletions", True
CurrentProject.Application.SetOption "Confirm Action Queries", True

End Function

Just plug the snippet into a module, plug your query name into the code, save the module, set your scheduler to open the database daily, and in your autoexec macro, use the "RunCode" action to run the "RunAppendQuery()" Function.

On Mon, Tue, Wed, Thu, the query will run once.

On Fridays, it will run 3 times.

On Saturdays and Sundays, it will not run.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!! A WORD OF CAUTION !!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
[/color red]
This autoexec macro will run EVERY time you open the database. So, if you are going in to make some change to the query, and you double-click the mdb file to open it, the autoexec macro (and hence, the function), will run, and your table(s) will be appended, even though you probably weren't wanting to do that.

You can, however, bypass the autoexec macro by HOLDING DOWN THE SHIFT KEY [/color red] when you open the database.

Let me know if you have any more problems.

Good Luck,

Tranman

 

[Tranman]

A star to you, tledwards!

Probably what balllian needs is a combination of our two solutions...yours to run the selected macro in his current database, and mine to make the query run 3 times. (Because the rinky scheduler in Windows isn't REXX, and can't be told to run a task once on MTWT, and 3 times on Friday.) And this lets him schedule just one task daily vs. 7 weekly tasks.

Where did you find the command line documentation? I looked for it several years ago, and came up with nothing.

Tranman