How to automatically check data every 10 seconds and print if any data is there (1 Viewer)

[dzirkelb]

I would like to create a script that runs every 10 seconds. The script would run a query looking for data in a table, if there is any data, then it prints a report based upon that data, then deletes the data.

How do I go about doing this? I can write the code to look for the data, print the report, and delete the data, but I need help having it automatically check every 10 seconds. And, where do I put this code for it to run all the time?

 

[Ranman256]

Make a form, set the form property, TIMER INTERVAL = 10000. (10 secs)
In the events, set the ON TIMER event,
In the code, check for files, then fire if needed:

Private Sub Form_Timer()
Dim FSO, oFolder, oFile, oRX
Dim sTxt As String, sFile As String

If IsMissing(pvPattern) Then pvPattern = "*.*"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)

Set colFiles = oFolder.Files

For Each oFile In colFiles
     sFile = oFile.Name
            'do something to the file here if exists   
Next

Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
End Function


Public Function FileExists(ByVal pvFile) As Boolean
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FileExists = FSO.FileExists(pvFile)
Set FSO = Nothing
End Function

the form must be open for the timer to work.

 

[dzirkelb]

Wonderful, thanks!

Is there a way to have the access application run as a service instead of a program? I'd like this to load up at logon, but not be seen / have the ability to close it.

 

[Ranman256]

You could call it and make it invisible.
program1 would start up (Access or Excel) then it would call Access timer app (program2)
And set it to invisible.
Program1 would then close

(I can't access my code from here) but Google calling Access from another app.
Something like:

Dim acc as Access.application
Set acc = new Access.application
Acc.visible = false
Acc.openDatabase "c:\folder\timerApp.mdb"

 

[sneuberg]

What if the printer runs out of ink? Wouldn't it be better to flag the records as printed rather than delete them?

Also I suggest looking at Data Macros. Maybe you can print the data upon entry rather have this loop running.

 

[sneuberg]

You could call it and make it invisible.
program1 would start up (Access or Excel) then it would call Access timer app (program2)
And set it to invisible.
Program1 would then close

(I can't access my code from here) but Google calling Access from another app.
Something like:

Dim acc as Access.application
Set acc = new Access.application
Acc.visible = false
Acc.openDatabase "c:\folder\timerApp.mdb"

This is interesting. The follow code which I adapted from here does have the effect of running the code in the background.

Option Compare Database
Option Explicit
Dim appAccess As Access.Application
 
Sub DisplayForm()
 
 Dim strDB As String
 
 
 strDB = "C:\Users\sneuberg\Documents\Access Projects\TimerAddsRecords.accdb"
 ' Create new instance of Microsoft Access.
 Set appAccess = CreateObject("Access.Application")
 ' Open database in Microsoft Access window.
 appAccess.OpenCurrentDatabase strDB
 appAccess.Visible = False

  
End Sub

The thing to note is that "When the variable pointing to the Application object goes out of scope, the instance of Microsoft Access that it represents closes as well. Therefore, you should declare this variable at the module level.", i.e., the line

Dim appAccess As Access.Application

is in a module and outside the subroutine.

I ran this code twice and now I see that even though no Access applications are showing in the Applications Tab of the Window Task Manager I see two msaccess.exe *32 in the Processes tab.

So this is way cool. Now I just need to figure out to stop these tasks baring just ending them in the Windows Task Manager.

 

[Ranman256]

you cant. Once they become a service, you cant access it EXCEPT thru task manager.

 

[sneuberg]

you cant. Once they become a service, you cant access it EXCEPT thru task manager.

Then this isn't a solution I'd offer to a typical user.

 

[Ranman256]

I think you can start the app and leave an icon in the system tray, but not as a window.
Right click the system icon ,then close.
Eliminating the task manager.

 

[sneuberg]

I think you can start the app and leave an icon in the system tray, but not as a window.
Right click the system icon ,then close.
Eliminating the task manager.

Yeah but the OP didn't want to see it. I think I found a way to do this. I've included the databases I've been using to test this in the attached zip file.

The database RunAcccessAppInvisible.acdb has following code in the Open App module to start the other database application.

Sub StartApplication()
 
Dim strDB As String
strDB = "C:\Users\sneuberg\Documents\Access Projects\TimerAddsRecords.accdb"
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
'get and store the process id of the started application
CurrentDb.Execute "INSERT INTO [tblPID] (PID) VALUES ( " & appAccess.Run("GetCurrentProcessId") & ")"
appAccess.Visible = False

  
End Sub

The database that this starts (TimerAddsRecords.accdb) has the follow declaration in a module.

Declare Function GetCurrentProcessId Lib "kernel32" () As Long

I use this to get the process id (PID) of TimerAddsRecords.accdb and store that in a local table. The TimerAddsRecords.accdb just has a form with a timer that adds records to a table every two seconds just so I could confirm it was really running.

Since the PID is stored is a table you can exit the RunAcccessAppInvisible.accdb application and the only Access database open (TimerAddsRecords.accdb) is in the background. When you want to stop the background database you restart RunAcccessAppInvisibleaccdb and execute the following code.

Sub StopApplication()

Shell "TaskKill /pid " & Nz(DLookup("[PID]", "[tblPID]"))
CurrentDb.Execute "DELETE * FROM [tblPID]"

End Sub

This needs to be made more robust. I'm not sure what happens if you run the StopApplication and the other process is no longer there because of a reboot for example. Worse yet would be the process id assigned to something else important.

 

[dzirkelb]

What if the printer runs out of ink? Wouldn't it be better to flag the records as printed rather than delete them?

Also I suggest looking at Data Macros. Maybe you can print the data upon entry rather have this loop running.

I am printing labels, so no ink. The program will reside on a server, not the user's computer. Also, this table is just a temp table. I dump records in when the product is received in, then delete them when it is printed. If it doesn't print for some printer error, then they will just reprint manually. In my experience here in 10 years, about a million labels printed, only a handful have failed.

The Data Macro is exactly what I want to do; however, I didn't know Access could do this. We work primarily with SQL server, and launching an external application from an sql trigger wasn't want I wanted to do, and it is difficult, and running on the SQL server itself. Although I can't use an sql linked table to accomplish the Data Macro (seems to have issues), I will be able to work around that. Thanks for this info, I'll report back with what I find.

 

[sneuberg]

The Data Macro is exactly what I want to do; however, I didn't know Access could do this. We work primarily with SQL server, and launching an external application from an sql trigger wasn't want I wanted to do, and it is difficult, and running on the SQL server itself. Although I can't use an sql linked table to accomplish the Data Macro (seems to have issues), I will be able to work around that. Thanks for this info, I'll report back with what I find.

I didn't know your backend was a SQL Server. Data Macros are kind of Access's answer to triggers. I don't know if they work with linked tables.

 

[dzirkelb]

I didn't know your backend was a SQL Server. Data Macros are kind of Access's answer to triggers. I don't know if they work with linked tables.

They don't, but this is just a temp table, easy for me to convert the code to look at an Access table instead.

However, I'm having trouble finding how to print something from a Data After insert event. The drop down options are limited compared to a regular macro, and there is nothing to print. I can run a Data Macro; however, when I create a macro to just print the report, that macro is not in the list to choose from.