将 Microsoft Access 作为计划任务运行 [英] Running Microsoft Access as a Scheduled Task

问题描述

我正在寻求关于如何安排数据库 (.accdb) 自动更新的评论，因为我对我设置的过程不太满意.

I am seeking comments on how to schedule auto updates of a database (.accdb) since I am not very comfortable with the process I have set up.

目前，它的工作原理如下:

Currently, it works as follow:

1. 任务计划程序调用 .bat
2. .bat 调用 .vbs
3. .vbs 打开数据库并调用宏
4. 宏调用函数(VBA 级别)
5. 函数调用更新子程序

我认为步骤太多，而且它需要 2 个与数据库相关并存储在系统上的外部文件(.Bat 和 .vbs)这一事实增加了程序中断的风险.

I consider there are too many steps and the fact that it requires 2 external files (.Bat and .vbs) related to the database and stored on the system increase the risk that the procedure would break.

显然(但请告诉我我错了以及如何更改它).vbs 不能调用子例程而只能调用宏.同样，如果用户希望进入数据库的 VB 环境，访问宏不能调用子程序而只能调用函数.这就是我调用一个函数(VBA 级别)然后调用子例程的原因.

Apparently (but please tell me that I am wrong and how I can change it) .vbs cannot call a subroutine but only a macro. Identically, an access macro cannot call a subroutine but only a function if the user is expecting to enter the VB environment of the database. This is the reason why I called a function (VBA Level) that then calls the subroutine.

希望你们中的一些人知道如何缩短步骤并最终掌握 .bat 和 .vbs

Hope some of you know how to shorten the steps and eventually get ride of the .bat and .vbs

推荐答案

据我所知，Windows 计划任务在 Access VBA 中做一些有用的事情"的最短路径是:

To the best of my knowledge the shortest path for a Windows Scheduled Task to "do something useful in Access VBA" is:

在数据库中创建一个公共函数(不是子函数).例如:

Create a Public Function (not Sub) in the database. For example:

Option Compare Database
Option Explicit

Public Function WriteToTable1()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    cdb.Execute "INSERT INTO Table1 (textCol) VALUES ('sched test')", dbFailOnError
    Set cdb = Nothing
    Application.Quit
End Function

在数据库中创建一个宏来调用函数:

Create a Macro in the database to invoke the function:

创建一个 Windows 计划任务以使用适当的参数调用 MSACCESS.EXE

Create a Windows Scheduled Task to invoke MSACCESS.EXE with the appropriate parameters

在上面的对话框中的值是:

In the above dialog box the values are:

程序/脚本:

"C:Program FilesMicrosoft OfficeOffice14MSACCESS.EXE"

添加参数(可选):

C:UsersPublicschedTest.accdb /x DoSomething

这篇关于将 Microsoft Access 作为计划任务运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！