Obtaining record count from shared .mdb file using VBS + Task Scheduler

Question

I have searched through some topics where a VBS file is being called via a batch file on Task Scheduler, but it doesn't run.

In my case, my VBS file does run on Task Scheduler but my logic for grabbing a record count from a shared database (.mdb) does not work correctly. Ideally, my VBS file will check how many units have been made on the present day, and output the corresponding DATE, TIME, and TOTAL units, with DATE and TIME being fixed values and TOTAL being my record count.

*It should be noted that while my VBS file does not work correctly on Task Scheduler, it does work correctly when run manually. This is something I cannot wrap my head around.

Here's an example of what my output (.csv) file should look like, running every five minutes on Task Scheduler

DATE        TIME    TOTAL
01/13/2017  16:30   165
01/13/2017  16:35   169
01/13/2017  16:40   173
01/13/2017  16:45   176
01/13/2017  16:50   179

but as of right now, I am only getting the output below.

DATE        TIME    TOTAL
01/13/2017  16:30   recordset is empty
01/13/2017  16:35   recordset is empty
01/13/2017  16:40   recordset is empty
01/13/2017  16:45   recordset is empty
01/13/2017  16:50   recordset is empty

The Task Scheduler runs this VBS code from 7AM-10PM every day. If no units have been produced yet, then TOTAL should show 'recordset is empty' until a unit has been produced. Problem is, my code has been generating files of only 'recordset is empty' for an entire day.

Here is my VBS code, separated into:

-A copy pasted code that I used to be able to run DAO on 64bit Windows 7
-Setting environmental variables
-Date logic
-Record count logic
-Output to .csv file

    Option Explicit
    '////////////////////////////////IGNORE - COPY PASTED TO GET DAO.DBENGINE.36 RUNNING//////////////////////////
    CheckOSx86x64Args()
     dim JRO, SC, DAO
     Rem main code begins here
     Set JRO = CreateObject("JRO.JetEngine")
     Set SC = CreateObject("ScriptControl")
     Set DAO = CreateObject("DAO.DBEngine.36")

     Sub CheckOSx86x64Args()
         Dim CmdLn, Arg
         With CreateObject("WScript.Shell")
             If Replace(LCase(WScript.Path), LCase(.ExpandEnvironmentStrings("%windir%")), "") = "system32" Then
                 CmdLn = Replace(LCase(WScript.FullName), "system32", "syswow64")
                 If CreateObject("Scripting.FileSystemObject").FileExists(CmdLn) Then
                     CmdLn = CmdLn & " """ & WScript.ScriptFullName & """"
                     For Each Arg In WScript.Arguments
                         CmdLn = CmdLn & " """ & Arg & """"
                     Next
                     .Run CmdLn, 1, True 'added " ,1, True " in hopes of waiting until whatever process is running is done. read into bwaitonreturn
                     WScript.Quit
                     End If
             End If
         End With
     End Sub
'////////////////////////////////////////////ENVIRONMENT/////////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'On Error Resume Next
Const ForWriting = 2
Const ForAppending = 8 
Dim rst, oJet, oDB 'As DAO.DBEngine, DAO.Database, DAO.Recordset
Dim fso, GetTheBase 
Dim formatDate, SQLdate, csvCurrentTime 'date format
Dim counterFSO, activeWbTest, objworkbook, objLogFile
Dim origLP, destpath
Dim Total 
Const QRY_NAME = "Results" 'Table to export

csvCurrentTime = Hour(now) & ":" & Minute (now)

origLP = "\192.168.1.240\ProductionSrvr2\SIMULATION\LongPulse.mdb"
destpath = "\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\SM"
'///////////////////////////////////////////////DATE/////////////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'check if date provided as an argument;if provided, use that data, otherwise, determine date
If WScript.Arguments.Count > 0 Then
    SQLdate = WScript.Arguments.Item(0) 'Query date, MM/DD/YYYY with 0's
    formatDate = Replace(SQLdate,"/","")'File extension date
Else
    SQLdate = Right("0" & Month(Now()), 2)& "/" & Right("0" & Day(Now()), 2)& "/" & Year(Now()) 'Query date
    'SQLdate = "01/13/2017"
    formatDate = Replace(SQLdate,"/","")'File extension date
End If
'//////////////////////////////////////////////RECORDCOUNT LOGIC/////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
Set fso = CreateObject("Scripting.FileSystemObject")
GetTheBase = "SM_" & formatDate & ".csv"    'adding csv to the file

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(origLP)
'Query database for 'distinct ID' from table 'Results' where the column 'Test_Date' contains the system date
Set rst = oDB.OpenRecordset("SELECT DISTINCT ID FROM " & QRY_NAME & " WHERE Test_Date LIKE '*" & SQLdate & "*' ")
If rst.EOF = True Then 
    Total = "recordset is empty"
Else
    Total = rst.RecordCount
End If
oDB.Close
Set oDB = Nothing
Set oJet = Nothing
'//////////////////////////////////////OUTPUT TO .CSV FILE///////////////////////////////////////////////////
'////////////////////////////////////////////////////////////////////////////////////////////////////////////
'checks if .csv file already exists. If it doesn't, it creates file with headers. If it does, it skips the 'if' statement
Set counterFSO = CreateObject("Scripting.FileSystemObject")
If Not counterFSO.FileExists(GetTheBase) Then
    Set objLogFile = counterFSO.CreateTextFile(GetTheBase, ForWriting)
    objLogFile.Writeline chr(34) & _ 
    "DATE" & chr(34) & "," & chr(34) & _ 
    "TIME" & chr(34) & "," & chr(34) & _ 
    "TOTAL" & chr(34) & "," 
    Set objLogFile = nothing 'prevents "permission denied" error.set objLogFile to nothing for the next .opentextfile action
End If
Set objLogFile = counterFSO.OpenTextFile(GetTheBase, ForAppending)
objLogFile.Writeline chr(34) & _ 
SQLdate & chr(34) & "," & chr(34) & _ 
csvCurrentTime & chr(34) & "," & chr(34) & _ 
Total & chr(34) & "," 
Set objLogFile = nothing

I am not sure where I am going wrong in the record count logic. I start by using an SQL statement Set rst = oDB.OpenRecordset("SELECT DISTINCT ID FROM " & QRY_NAME & " WHERE Test_Date LIKE '*" & SQLdate & "*' ") to select unique records (using ID as criteria) where the Test_Date is similar to my SQLdate (which is always representative of the current day).

My guess is that the problem lies in this section

If rst.EOF = True Then 
    Total = "recordset is empty"
Else
    Total = rst.RecordCount
End If

If no units have been produced yet, my recordset should be completely empty. I use .EOF to detect this circumstance, but as of right now the code thinks rst.EOF = True all the time instead of recording an actual record count when units have been produced. Again, when the VBS file is run manually instead of on Task Scheduler, it is able to get a correct record count.

Here's an example of the cloud database (.mdb) file where I query ID and Test_Date for a record count

Title   Comment  ID         Test_Date   Test_Time   Pmax
AU29    PL1      H160019608 01/13/2017  1:03:16 PM  351.9642
AU29    PL1      H160019917 01/13/2017  1:03:35 PM  351.9942
AU29    PL1      H160019940 01/13/2017  1:03:56 PM  356.2267
AU29    PL1      H160019940 01/13/2017  1:04:17 PM  356.2153

The batch file simply calls my .vbs file (UniqueCounter.vbs) using the following code:

set SM_DEST="\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\SM"
set sim_count="\192.168.1.240\ProductionSrvr2\PROGRESS_LOGS\BIN\UniqueCounter.vbs"

REM collect simulation data
pushd %SM_DEST%
%sim_count%
popd

Anyone know how I could go about troubleshooting this? Any help would be greatly appreciated.

Obtaining record count from shared .mdb file using VBS + Task Scheduler

Answers (1)

Related Questions