2

I have a macro in MSACESS that I want to run as a scheduled task, without a gui and any confirmation windows that pop up. The macro inserts records into a table. This is what it looks like...

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

This macro works fine when run interactively in an Access session. It also runs fine when I run it from a .bat like this...

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" \\Somewhere\Somewhere_else\tdb.accdb /cmd Update_Burndown_Metrics

The one caveat in this mode is that a window pops up asking me if I really want to insert the records. The answer to that will always be "yes".

Now, I want to run the .bat as a scheduled task. I do not want the gui and I don't want the pop-up. Can this be done ?

I tried sticking "Application.Visible = False" in the macro. Didn't work. I think this may be for ExCel only?

daveg
  • 1,051
  • 11
  • 24
  • Use `CurrentDb.Execute` instead of `RunSQL`. Won't even need the SetWarnings lines. I do wonder why the confirmation popup is not suppressed. Also, are you saving CurrDate into a text field? If it is actually a date/time field, should use `#` delimiter instead of apostrophe. Point of detail, this is VBA not a macro. Macros in Access are very different. – June7 Mar 11 '19 at 22:47
  • I must confess up front that I'm a linux guy, not windows. A bit (lot) like a fish out of water. So are you saying that if I replace "DoCmd.RunSQL" with "DoCmd.CurrentDb.Execute" the popup will be surpressed ? Yes, it is a date field. The "CurrDate" was something I found googling around. I was so pleased with myself when this actually worked, however it worked. I tried/failed to use "now()" in the insert command, but since that cmd used aggregates, it didn't work, so I resorted to this. Not sure what you mean (specifically... remember I'm a linux guy) when you say # delimited. – daveg Mar 11 '19 at 23:43
  • If you make `Date()` the *Default Value* for `BurnDownMetrics.when_captured`, you won't need to supply that value with your `INSERT`. The db engine will do it for you automagically. – HansUp Mar 12 '19 at 01:01
  • You can also try removing the brackets from your DoCmd.SetWarnings (False) command. In VBA, arguments for subs do not need brackets. So, it would be DoCmd.SetWarnings False – S. MacKenzie Mar 12 '19 at 01:44
  • The parentheses don't make a difference in this case. – June7 Mar 12 '19 at 02:39

2 Answers2

2

I don't know what CurrDate is nor why it would work for you. It is not an intrinsic function in VBA nor Access SQL. If you want today's date, use Date(); if you want date and time, use Now(). There is a CurDate() function in MySQL but Access SQL engine will not recognize that one either.

CurrentDb does not need the DoCmd prefix.

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"

Example VBScript that opens Access db object and runs VBA or macro procedure. The Access app window will not display but it will show in Windows taskbar.

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

Thanks to @HansUp suggestion, found another approach https://rtmccormick.com/2014/06/05/how-to-connect-to-access-database-with-vbscript/. Example VBScript:

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

As advised in the link, had to run the script from a shortcut with Target property modified:
%windir%\SysWoW64\wscript.exe C:\Users\LL\RunProc.vbs

June7
  • 19,874
  • 8
  • 24
  • 34
  • I'll give this a try tomorrow. Question first.... are you sure abou tthe unmatched single quotes that appear before 2 of the "accessApp." statements ? I really doon't know this language, but the fact that they appear to be unmatched is suspicious (to me anyway). – daveg Mar 12 '19 at 03:19
  • Those apostrophes cause the lines to comment and will not execute. This is the same in VBA. I left the lines in just for information. – June7 Mar 12 '19 at 03:37
  • How does one run VBA code outside of an application, like Access ? I tried just creating another vba sub in the same module that I had "Update_Burndown_Metrics", but then just invoked it the same way I did before in the .bat, which just invoked Access first. From looking at your code, it looks like it's invoking Access, no need/desire for me to. So the question now is, how does one run vba from a .bat ? Thanks for the help BTW :-) – daveg Mar 12 '19 at 17:10
  • OK, I think I'm getting closer. I managed to create a vbs script on the Desktop. When I run it, I get an error "Microsoft Access cannot find the procedure" which sounds like a contexting issue. The vba sub inside Access is called "Update_Burndown_Metrics" which is inside a Module. To be honest, I don't know what the module is named but when I bring it up in design view, the banner at the top has "Update_Burndown_Metrics (Code)". If this is contexting, how to I properly identify the sub ? – daveg Mar 12 '19 at 17:47
  • Module and procedure cannot have same name. Rename the module. – June7 Mar 12 '19 at 19:31
  • VBScript can use ADO or DAO to execute an `INSERT`. No need to start a full MSACCESS.EXE application session. – HansUp Mar 12 '19 at 19:40
  • I renamed the module to UBM_Code and tried again. Same error. Can/should the VBA subroutine move out of Access altogether ? I tried sticking it in the vbs script but that didn't work – daveg Mar 12 '19 at 20:30
  • That should work but might be able to accomplish completely within VBScript. See revised answer. Edit question with VBScript code if still need help. – June7 Mar 12 '19 at 20:43
  • Does make me wonder how the .bat file could have worked if module and sub had same name. – June7 Mar 12 '19 at 20:49
  • That one gave... "Provider cannot be found. It may not be properly installed"... "Source: ADODB.Connection". I don't have OLE. I don't think I can do ADO. I just a DB on a file server. I tried creating a macro in the DB that does the same thing. works fine when I call it. Renamed the vba module and sub to something else to avoid confusion. That failed too, same error, can't find the procedure. – daveg Mar 12 '19 at 21:27
  • FINALLY I got it to work. My bad. There was another module with a sub by the same name. So embarrassed. Thanks Everyone for all the help ! – daveg Mar 13 '19 at 00:49
0

Where the SQL is concerned, you can evaluate the Date() function directly from the query, i.e.:

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

Furthermore, if you are populating all of the fields in the BurnDownMetrics in the order in which they appear in the table, then the query can be reduced further to:

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

Your Sub can then become:

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

This should then no longer warn you about inserting records.

As for suppressing the GUI, I'm not sure that this is possible: the method that I typically follow is as described by this answer.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • I tried putting the date() function (or something similar) directly in the SQL as you suggested. For some reason it didn't work. I think it has something to do with the aggregate functions, so I googled around, found the CUrrDate somewhere and glued it into a string. Seems to have worked, hacky as it is. I don't think it's reliable to assume column order when doing something like an insert, or even a query. Simply changing a property of a column can rearrange things. I know this to be true for other, non-Access DBs anyway. But maybe Access works differently. – daveg Mar 12 '19 at 03:17
  • Could you elaborate on: *"For some reason it didn't work"* Did you receive an error message? If so, what was it? Are you inserting the data into a field of `Date/Time` type? From your example it looks like you are populating a text field with date data, which is very bad practice. I agree that the order of fields should not be relied upon, hence why I offered both options. – Lee Mac Mar 12 '19 at 13:22
  • I actually posted something on this... https://stackoverflow.com/questions/54488047/how-can-i-insert-a-set-of-records-in-an-access-table-which-come-from-an-aggregat – daveg Mar 12 '19 at 16:57