Auto Trigger MS ACCESS Code

[tuktuk]

hey there,

I've recently switched to window 7.

previous on XP i used task scheduler to open a MS Access database at a particular time daily where there was code attached to a form (which was opened when database opens) that runs code if opened at the correct time (when the scheduler opened the database/form) and then provided a msg box otherwise.

i'm running into issues w the windows 7 task scheduler. doesnt seem to work as well.

are there other suggestion on how to trigger ms access code on a repetative sched?

thanks
tuk

 

[Joe4]

Should work just the same. Make sure that have set the location where the database resides as a "Trusted Location" on the computer that Task Scheduler is running from. Then the VBA code will be enabled and it should run, just like before.

 

[tuktuk]

I'm actually able to get the database to open if i run the task scheduler job via rt click RUN.......so everything is trusted and paths are set properly.

i'm running into issues when i try to schedule it using the trigger (at a set time). it says running two jobs at once.....must be other tasks scheduled to run from my IT dept.

wasn't sure if there is another method to simplly schedule ms access to open.

 

[Joe4]

Running two jobs at once shouldn't be a problem if they are totally unrelated tasks.
Make sure that it doesn't currently think the job is already running, or the Access database is already open.
You may want to delete the job, and build it again.
Note that there may be settings on how to handle different situations. Be sure to check out all the options in Task Scheduler.

 

[tuktuk]

still not able to trigger this correctly. i'm thinking it may have to do with my code rather than the task scheduler.

here goes:

after further reseach i found the best method is to use the cmd prompt to trigger.....with that said in the Program/Script section of hte task sched i have "cmd"
and the /c start "" "C:\Program Files\Microsoft Office 2007\Office12\MSACCESS.EXE" "C:\Users\TASK_SCHED\TaskSchedAssistant_NightlyBatchRun.accdb"
task sched says it completes opening this database. there is a locked version at the location.

here is my code behind a form that opens when the database opens....

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
If TimeValue(Now()) < #5:55:00 AM# And TimeValue(Now()) > #5:39:00 AM# Then
    Call CallAnotherDatabase
Else
End If
DoCmd.SetWarnings True
End Sub

which calls a sub in a module within the same database whose reference are as follows:
1. Visual Basic for App
2. Miscrosoft Access 12 Object Library
3. OLE Automation
4. Microsoft Office 12 Access database engine object lib

Public Sub CallAnotherDatabase()

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "S:\Databases\EveningAutoTrigger\EveningAutoTrigger.accdb"

Set appAccess = Nothing

End Sub

does any of this look incorrect?

thanks
tuk

 

[xenou]

Seems convoluted to open a database that opens a form that opens another database that runs more code. I suppose it should work but I've never tried anything quite like it.

My preference is to write a vbscript. My scheduled task runs the vbscript file (i.e., just point to it just like you would a .bat file or an .exe).

What follows would be the contents of a sample vbs file (you test it first by just double-clicking it/opening it. Once it works you point your scheduled task to it and all should be well. For instance, my "db1.mdb" would probably be your "EveningAutoTrigger.accdb" and my "Scheduled_001" would be the subroutine or function in the database to run. You could also run macros in a similar way.


File.VBS

On Error Resume Next
Call Run_Job()

'__________
Sub Run_Job()

	Set objAccess = CreateObject("Access.Application")
	With objAccess
	    .OpenCurrentDatabase "C:\Folder\db1.mdb", False
	    .Run "Scheduled_001"  '//if the routine is a function not a sub: .Run ("Scheduled_001")   
	    .CloseCurrentDatabase
	    .Quit
	End With

End Sub

 

[tuktuk]

i agree it is rather sloppy but after researching on using window 7 task scheduler i found it was best to open ms access database that are on your C drive rather than on a network drive.

with that said, the EveningAutoTrigger.accdb is a databae that others link to (since it updates with daily data) i thought the easiest method would be have taks sched open a database on my c drvie then use .opencurrentdatabase to load the nightly batch. the problem is i do not get a debug screen upon error using the task sched....

tuk

 

[xenou]

There's nothing really wrong with what you are doing except the fact that apparently it doesn't work. The only way to debug it is to test it without the task scheduler (i.e., opening the database and seeing if everything fires). I use windows task scheduler with databases on a LAN and it doesn't have any issues.

 

[tuktuk]

are you on windows7?

do you open MSACCESS.EXE first?

what does your actions section look like?

 

[xenou]

Hi, Yes, I'm on Win7. My action section is the path to my vbscript file (i.e., C:\myFolder\myFile.vbs). The vbscript file takes care of opening the database and running the sub I want it to run (as described in my post # 6 above). It also takes care of closing the database when it's finished running, too.