如何在没有GUI的情况下在后台运行MSACCESS

Question

我有一个我想作为计划任务运行的宏，而没有GUI和任何弹出的确认窗口。宏将记录插入表中。这就是它的样子...

Public Sub Update_Burndown_Metrics()
    Dim SQL_Text As String
    Dim CurrDate As String
    CurrDate = DateValue(CStr(Now()))
    SQL_Text = "insert into BurnDownMetrics (project, domain, tot_effort_spent, tot_effort_left, tot_est_effort, when_captured) select project, domain, sum(effort_spent), sum(effort_left), sum(tot_effort), '" & CurrDate & "' from tasks group by project,domain"
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL SQL_Text
    DoCmd.SetWarnings (True)
End Sub

在访问会话中交互式运行时，此宏正常运行。当我从一个.bat这样运行时，它也可以运行良好...

"C:Program Files (x86)Microsoft OfficerootOffice16MSACCESS.EXE" \SomewhereSomewhere_elsetdb.accdb /cmd Update_Burndown_Metrics

在此模式下的一个警告是，一个窗口弹出，问我是否真的想插入记录。答案将永远是"是"。

现在，我想作为计划任务运行.bat。我不想要GUI，也不想要弹出窗口。可以做到吗？

我尝试在宏中粘贴" application.visible = false"。不起作用。我认为这可能仅适用于Excel？

Answer 1

我不知道什么是咖喱，也不知道为什么它对您有用。它不是VBA或访问SQL中的固有函数。如果您想要今天的日期，请使用date（）;如果您想要日期和时间，请使用now（）。MySQL中有一个curdate（）函数，但Access SQL引擎也不会识别该功能。

CurrentDB不需要DOCMD前缀。

CurrentDb.Execute "INSERT INTO BurnDownMetrics (project, domain, tot_effort_spent, " & _
                  "tot_effort_left, tot_est_effort, when_captured) " & _
                  "SELECT project, domain, Sum(effort_spent), " & _
                  "Sum(effort_left), Sum(tot_effort), Date() " & _
                  "FROM tasks GROUP BY project, domain"

示例VBScript打开访问DB对象并运行VBA或宏过程。访问应用程序窗口将不会显示，但它将显示在Windows任务栏中。

Set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase "C:UsersLLUmpires.accdb"
'accessApp.UserControl = true
accessApp.Visible = False
accessApp.Run "TestVBA"
'accessApp.DoCmd.RunMacro "TestMacro"
accessApp.Quit

感谢@hansup的建议，找到了另一种方法https://rtmccormick.com/2014/06/05/how-to-connect-to-connect-to-access-database-with-with-vbscript/。示例vbscript：

Set cn = CreateObject("ADODB.Connection")
cn.open = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:UsersLLUmpires.accdb"
cn.execute "INSERT INTO Rates(RateID,RateLevel) VALUES('zz','zz')"

链接中的建议，必须从捷径上运行脚本，并修改目标属性：
%windir%SysWoW64wscript.exe C:UsersLLRunProc.vbs

Answer 2

在涉及SQL的情况下，您可以直接从查询中评估Date()函数，即：

insert into BurnDownMetrics (project, domain, tot_effort_spent, tot_effort_left, tot_est_effort, when_captured) 
select project, domain, sum(effort_spent), sum(effort_left), sum(tot_effort), Date()
from tasks
group by project, domain

此外，如果您在 BurnDownMetrics中以它们出现在表中的顺序填充，则可以将查询进一步缩小到：

：

insert into BurnDownMetrics
select project, domain, sum(effort_spent) as tot_effort_spent, sum(effort_left) as tot_effort_left, sum(tot_effort) as tot_est_effort, Date() as when_captured
from tasks
group by project, domain

您的子可以变成：

Public Sub Update_Burndown_Metrics()
    With CurrentDb
        .Execute _
        "insert into BurnDownMetrics (project, domain, tot_effort_spent, tot_effort_left, tot_est_effort, when_captured) " & _
        "select project, domain, sum(effort_spent), sum(effort_left), sum(tot_effort), Date() " & _
        "from tasks " & _
        "group by project, domain"
    End With
End Sub

这不应再警告您插入记录。

---

至于抑制GUI，我不确定这是可能的：我通常遵循的方法如此答案所述。