Automatic updating of MS Access Databases using scheduled tasks

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:

PHI copy automation ‎ (← links)