Automatic updating of MS Access Databases using scheduled tasks
Jump to navigation
Jump to search
This page contains information on how to use a scheduled task to run code in MS Access. uses this.
Through specific VBA function called from script
PHI copy automation uses this.
Two parts to it, an .accdb with a function and a .vbs that calls the function.
- .vbs
sub main
phi_location = "<path of file>"
phi_loader = phi_location & "\<name of file>"
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists(phi) then
dim access
set access = CreateObject("Access.Application")
access.openCurrentDatabase(phi_loader)
access.run "<name of sub>"
access.closeCurrentDatabase
set access = nothing
else
'msgbox "could not find file"
end if
set fso = nothing
end sub
- .accdb must have sub <name of sub>
Through form open event
This is done as follows:
- set a form to be the form opened at start-up in an Access .mdb (menu -> tools -> startup)
- set the on form open event for the form to, if now() is between 1 and 5
- run an update or maketable query
- close the program
- set up a scheduled task to open the program within the above timeframe
Probably want to have the .mdb compact and repair on close.
Need to delete db#.mdbs occasionally if this is run on a server, for some reason MS Access doesn't clean them up. Suppose could add that to the updater code.
Related Articles
| Related articles: |