Share via

Task Scheduling from Within MS Access??

Anonymous
2022-02-13T17:15:12+00:00

Hi.

I'm trying to do some scheduled jobs with Access. Some jobs import data to tables that other users may using at the same time (e.g. some are hourly data pulls from outside sources). From my research, everyone seems to do this using the Windows task scheduler. However, it seems like they are starting Access, running the macro/function, and then quitting Access.

My question is: does this model create issues if the server, which is to be set up as split system (front end and back end) with multiple users and to be running all the time? It's the starting up Access that has me wondering - sometimes when I exit Access unexpectedly the lock file persists and then the database opens in read-only mode. I was wondering if the scheduled job will run into the same challenge where the running access process will have locked the database.

I appreciate the collective experience of the community here.

Thanks,

Mike

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2022-02-14T23:24:44+00:00

    I think you're overthinking it. To create a new FE, you copy/paste in Windows Explorer.

    Then typically I would invoke the special process with a /cmd command line option:
    <path_to>msaccess.exe <path_to>the.accdb /cmd DoThis

    Then in your startup code check for a Command$ of "DoThis" and call that procedure.

    1 person found this answer helpful.
    0 comments
  2. DBG 11,711 Reputation points Volunteer Moderator
    2022-02-13T18:26:22+00:00

    Hi Mike. One way to avoid potential problems like you mentioned is to create a separate Access FE just for the purpose of running the scheduled task.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2022-02-14T16:41:35+00:00

    It's not. It just made me wonder how to do this. I haven't seen how to open the connection to the database programmatically without relying on the environment. It's been built-in when one creates the database object and the corresponding recordset object. I'm assuming it's some variant on Set db = currentDB. From my long forgotten past with other tech, there was some kind of open connection to the server, attach to a particular db instance via credentials, and then you were set to go. I've appreciated how much access hides, but now it seems I need to peek under the covers some.

    I'm sure I'm not using the right search terms.

    0 comments
  4. Anonymous
    2022-02-14T16:24:25+00:00

    Thanks, DBGuy.

    OK, I think I got it. Just to confirm, I'll create a module in a separate accdb file that looks something like

    Dim objAccess As Access.Application
Dim db As DAO.Database
Set objAccess = New Access.Application
Set db = objAccess.DBEngine.OpenDatabase(strDBPath, False, False) 
' where strDBPath points to my accdb file and I'll figure out the other param's

    then I'll go grab the data, do the rs.addnew /rs.update work and then

    db.close

Set db = nothing  

DoCmd.quit

    then I do the scheduler like tom said.

    This helped me with my googling. And to give credit, this is from automateExcel.com. Much appreciated.

    0 comments
  5. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2022-02-14T13:55:05+00:00

    > sometimes when I exit Access unexpectedly the lock file persists and then the database opens in read-only mode

    That is a well-discussed bug in this forum, should be dealt with separately, and should not be a consideration for your overall project.

    I think a separate (virtual) workstation is the best idea, setup to login to a Domain account automatically upon reboot, and using the Windows task manager. No need to reinvent the wheel.

    Access is inherently multi-user, so that should not pose an issue either.

    0 comments