Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Auto Trigger MS ACCESS Code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Location
    Denver Colorado, USA
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto Trigger MS ACCESS Code

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,757
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    Denver Colorado, USA
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,757
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Location
    Denver Colorado, USA
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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....
    Code:
    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

    Code:
    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

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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
    Code:
    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
    Last edited by xenou; Mar 25th, 2014 at 07:36 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Location
    Denver Colorado, USA
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    Board Regular
    Join Date
    Nov 2006
    Location
    Denver Colorado, USA
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    are you on windows7?

    do you open MSACCESS.EXE first?

    what does your actions section look like?

  10. #10
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,474
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Trigger MS ACCESS Code

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •