Run Macro Every XXX Minutes (1 Viewer)

jo15765

Registered User.
Local time
Yesterday, 16:41
Joined
Jun 24, 2011
Messages
116
I have a user uploading excel workbooks to a website, where data is then inserted into MS SQL Server tables. These SQL Server Tables are linked tables into MS Access Database.

I need a way that once records are inserted into those MS SQL Server Tables to run a MS Access VBA Module. What would be my best approach for achieving this? Basically, somehow programmatically with vba check a table for rows every XXXX minutes.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:41
Joined
May 21, 2018
Messages
5,680
There is a timer event

This is a form event. So often people have a default hidden form that opens at startup and runs in the background.
 

jo15765

Registered User.
Local time
Yesterday, 16:41
Joined
Jun 24, 2011
Messages
116
Hmmm...so this means that I must leave the access database open constantly in order for the code to execute?

I was hoping for a solution that wouldn't require the DB to be open.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:41
Joined
May 21, 2018
Messages
5,680
You cannot run vba without an application open. You are checking rows in order to do what? Without an open application you would have to do this via a batch file or build an executable. Can you provide more details what you mean by check the rows and do what with ti?
 

jo15765

Registered User.
Local time
Yesterday, 16:41
Joined
Jun 24, 2011
Messages
116
If the table contains rows, then I want to run a few queries, and run a report and save the report as pdf all through VBA.

If the table does not contain rows, then there is no action needed.
 

plog

Banishment Pending
Local time
Yesterday, 18:41
Joined
May 11, 2011
Messages
10,651
The database with the VBA code must be opened to be able to run it. What you can do though is use Task Scheduler to open it, run your code then close it:


In short, you create a database with the linked tables, queries, reports, VBA, etc, that you need. Then you build a macro to control everything--test for the data, run whatever queries, output the report. Then in task scheduler you point to that macro and run it however often you need it run.
 

jo15765

Registered User.
Local time
Yesterday, 16:41
Joined
Jun 24, 2011
Messages
116
@plog - i like the idea of using Task Scheduler to open the Database. That is a good thought so then the database is not opened constantly!
 

plog

Banishment Pending
Local time
Yesterday, 18:41
Joined
May 11, 2011
Messages
10,651
I worked at a duct tape, bubble gum and pipe cleaners place that had a lot of these processes. Here's my tips:

1. Make a seperate database for this. If this database has other applications where users are in it, build a new one for this process.

2. Put it on an old computer. Since we had a lot of these processes we dedicated an old machine to all these task scheduler things. That way we never got screwed if Jim went on vacation or powered down his computer.

3. Make a log table. Everytime this thing runs make an entry in a table--put the date/time, name of the process, status and hopefully some metric--like number of items on the report.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:41
Joined
Feb 28, 2001
Messages
21,149
Here is another couple of bits of advice.

1. Don't run this too often. The computer has to be on, but if the machine doing this is normally idle, you won't have a problem. If other people can be using the machine, though, they can provide competition for system resources. This includes the "virtual memory" file (also called the Page file or Swap file in some circles). If too much is going on, the VM file can be allocated to the point that you can't allocate space for this separate instance of the stand-alone DB that will do this task. In summary, if it is not a dedicated machine, be aware that resource contention will be at least potentially an issue. And since it will be "blindly automated" (or what we used to call "lights out" automated), there is no human in the loop to make a judgment call that it might be a good time to delay the process.

2. In essence, you are using the Access command-line option /X:macro-name to trigger a sequence of events. Just remember that the macro MUST end with an Application:Quit step. Otherwise you leave the DB dangling and the next run will trip over the remnants of the previous run. And every step of the process needs to be tested to assure that the whole sequence is robust. TEST the process manually several times. If you can make it run at least 10 times without it getting hung up in some way, then you have made some progress. In fact, if you are going to use the Windows Task Scheduler for this, it is a rule that you MUST make the thing you are going to run totally self-sufficient.

3. (2nd instance of saying it) Don't run this too often. You have a lot of networking "handshakes" to accomplish (OK, Access and Windows do the hand shakes; you don't) and they take time. I suggest manually triggering the DB from a command prompt a few times so that you can establish some sense of timing on the process. The general rule for such processes is that if you must run it more often than twice the approximate time for a single run, that you might trip on the remnants anyway, just based on timing alone, not on failed programming.

EDIT (for clarification): If it takes x seconds to run, don't run it more often than about ever 2x seconds.

4. Delegate yourself or some other person to every so often check on the computer doing this if it is one you have set aside for the purpose of this workbook upload. If the process is "between cycles" and appears to be quiet, a quick Compact & Repair might be a good idea during the "every so often" part of this. You should understand that even a fully automated process, when Access is involved, will eventually develop database bloat to the point of needing help.

5. Of course, don't forget that if you have VBA involved, the macro's RunCode action ONLY activates FUNCTIONS, not subroutines.
 
Last edited:

Isaac

Lifelong Learner
Local time
Yesterday, 16:41
Joined
Mar 14, 2017
Messages
5,886
I've posted alot about combination 1) windows task scheduler, 2) vbscript, 3) run access sub. I use it TONS .. it works a charm.

1) windows task schedule with c:\windows\system32\cscript.exe in the program start, and "path to vbs" in the argument
2) vbscript with createobject("access.application"), access application opencurrentdatabase, , and then accessapp.run "named sub"
 

Users who are viewing this thread

Top Bottom