Run code at 12 AM everyday in MS Access

Set timer to run at 12 AM everyday in access how to do that. You can see the code and details in the below thread.
i want to run the macro everyday 12AM. I search for it and could not find how to run on specific time.

https://www.experts-exchange.com/questions/29115749/Loop-through-query-and-send-email-everyday-at-12AM.html
LVL 31
MAS (MVE)EE Solution Guide - Technical Dept HeadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Built a separate front-end, which sends the mails started by using the AutoExec macro. Create a scheduled task, which starts that front-end at 12am.
1
MlandaTCommented:
You can write a short VBScript which invokes the Access VBA code. You can then use the Windows Task Scheduler to schedule the VBScript to run everyday at 12.
'VB Script
Dim appAccess, blComplete

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "\\path\to\the\ms\access\database.mdb"
appAccess.Visible = True

blComplete = appAccess.Run("NameOfVBAFunctionHere", "Pameter1IfAny")
 
Set appAccess =Nothing

Open in new window

You can see this article for more information: https://analystcave.com/excel-running-excel-macro-vbscript-cmd/

Remember, your VBScript file is just a plain text file with extension *.vbs
1
MAS (MVE)EE Solution Guide - Technical Dept HeadAuthor Commented:
Thanks to both for your comments.
This a code in Form Timer
test.JPG
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
You missed Mlanda's and my point:

Get rid of the timer. The scheduling is done by invoking the send method via the scheduled task and the AutoExec macro or the VBScript.
0
Mark EdwardsChief Technology OfficerCommented:
i.e:
The Access db is closed.
The Windows Task Scheduler runs the VBScript .vbs file.
The Access db opens...
The autoexec runs....
The VBA process runs....
The VBA code can close the Access file, or do whatever you want it to when it's finished.
Wait for the scheduler to run again at 12:00 AM

Simple....
0
MAS (MVE)EE Solution Guide - Technical Dept HeadAuthor Commented:
I created a script and I get this error. It is not opened.
 test.JPG
0
ste5anSenior DeveloperCommented:
When using the AutoExec approch, we don't need the VBScript.
0
David BernsteinChief DeveloperCommented:
I prefer using the timer approach, sometimes the Task Scheduler doesn't work. I have a client that is a summer camp and I set up the timer to play the bugle (Taps) every night at 9PM. The output jack on the computer hooked into the PA system.
0
MlandaTCommented:
The Timer sitting on a form will ONLY fire when the Access Database and that Form are open. The Task Scheduler approach will work so long as the Windows machine is powered up and running and will give you a history of previous invocations and whether they succeeded or failed - which makes for a more robust solution in my view.

Maybe use a hybdrid approach:
- You VBScript can check if the Access lock file exists - if it exists - the DB is open and it must not execute.
- The (Access form based) Timer will then fire instead.

Both approaches would work and combine to give a good solution.
0
MAS (MVE)EE Solution Guide - Technical Dept HeadAuthor Commented:
I ended up with this batch file and scheduled this batch filie to run everyday 12AM.
START "" /MAX "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "D:\HRM\HRMS.accdb" /x passportnotify30days

Open in new window

0
mbizupCommented:
For a database dedicated to a scheduled task like this, you should add the following line to the code posted in your earlier question, to close out the database after running the query/emails:

DoCmd.Quit

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MAS (MVE)EE Solution Guide - Technical Dept HeadAuthor Commented:
START "" /MAX "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "D:\HRM\HRMS.accdb" /x passportnotify30days

I used this and added docmd.quite as suggested by mbizup.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.