Run VBA in background
I have an access file with VBA.
I have schedules tasks to run a macro/VBA to send emails based on the query.
Now the problem is it is not working when the server is logged out or when the server is restarted.
How we can run the VBA codes even if the user is not logged in?
I have schedules tasks to run a macro/VBA to send emails based on the query.
Now the problem is it is not working when the server is logged out or when the server is restarted.
How we can run the VBA codes even if the user is not logged in?
ASKER
DO you think this is a workaround?
https://expert-advice.org/windows-server/how-to-set-up-auto-login-windows-server-2012-and-2016/
https://expert-advice.org/windows-server/how-to-set-up-auto-login-windows-server-2012-and-2016/
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I once used a Server 2016 Essentials which opened Outlook 2013 at system startup. This eliminated the need for MS Exchange.
It ran bunches of background tasks, some of which ran every two minutes and some once a day.
Some of the background tasks were completely unrelated to email.
For instance,at midnight vbaproject.otm would use CreateObject("Access.Application")
to start Access,, run a background program that manipulated Access then closed Access.
Super powerful so worth the complexity
One of the biggest problems was error handling. For instance, msgbox had to be avoided because it suspends all threads and waits for a response. Since nobody is watching the server console, nobody ever responds to the msgbox.
Hope this helps.
It ran bunches of background tasks, some of which ran every two minutes and some once a day.
Some of the background tasks were completely unrelated to email.
For instance,at midnight vbaproject.otm would use CreateObject("Access.Application")
to start Access,, run a background program that manipulated Access then closed Access.
Super powerful so worth the complexity
One of the biggest problems was error handling. For instance, msgbox had to be avoided because it suspends all threads and waits for a response. Since nobody is watching the server console, nobody ever responds to the msgbox.
Hope this helps.
Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
.... bunch of related stuff ...
Public Sub ActivateYmailTimer(ByVal nSeconds As Long) '
nSeconds = nSeconds * 1000 'The api SetTimer call accepts milliseconds, so convert to Seconds
If pubYmailTimerId <> 0 Then Call DeactivateYmailTimer ' deactivate previous timer
pubYmailTimerId = SetTimer(0, 0, nSeconds, AddressOf TriggerYmailTimer) ' activate this timer
Private Sub TriggerYmailTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
Call ImportThenCloseDatabases
Call ActivateYmailTimer(ImportClientThrottle20SecondsAtATime)
I have another idea that might be simpler than the Outlook 2013 settimer approach I previously posted.
If you already have an access vba program and you want to run it once a day on your windows server you can use taskmgr.
I have to leave in 5 minutes, so I don't have time to explain many details.
At a minimum you would need MS Access installed on your server. (Several links tell me this will work fine.)
At the highest level taskmgr could kick off a vbscript program that uses CreateObject("Access.Application")
to open you .accdb file and manipulate it.
A third idea:
If you just want to email some simple query results once a day, the ENTIRE program might be done in vbscript without ever installing Access. It depends on how "simple" the query is.
Let me know if you want help with either of my three ideas.
If you already have an access vba program and you want to run it once a day on your windows server you can use taskmgr.
I have to leave in 5 minutes, so I don't have time to explain many details.
At a minimum you would need MS Access installed on your server. (Several links tell me this will work fine.)
At the highest level taskmgr could kick off a vbscript program that uses CreateObject("Access.Application")
to open you .accdb file and manipulate it.
A third idea:
If you just want to email some simple query results once a day, the ENTIRE program might be done in vbscript without ever installing Access. It depends on how "simple" the query is.
Let me know if you want help with either of my three ideas.
You can run Access from task scheduler adding startup macro parameter
"C:\Program Files\Microsoft Office\Office16\MSAccess.e
MacroName is name of sub in your datatbase. Make actions and quit from Access at the end of sub (Docmd.Quit)
"C:\Program Files\Microsoft Office\Office16\MSAccess.e
MacroName is name of sub in your datatbase. Make actions and quit from Access at the end of sub (Docmd.Quit)
Access (and Office) are foreground applications, and they expect a desktop and other things to be in place, which you only get with a login.
Jim.