Detect record adds, run .BAT if .MDB stops updating 1

[GHTROUT]

I am an IT person outside of NetSvcs, ApDev and the DBA areas. This is probably a job for an NMS system, to detect a file changing and report if it stops. I know Access very well up to the point that stops at the macro level all the way to the runcommand. I know zero code - knowledge stops at macros. It is expected that I just figure out and implement the solution within the boundries of the PC with the MDB and my machines, all on the same subnet.

So.......
I am looking for a way to report if an MDB stops responding to an input stream on its PC's serial port(s). One input is every 30 seconds (1 kb of data), and another is every 30 minutes (about 10 kb of data).

All I need to know is if the MDB file has gone for a number of hours without "changing" - time or size. I could live with "24 since change" and I only need to run this on business day hours...no data is sent off hours. The data is telephone call stats for our Help Desk and a few other phone queues.

I've found a few share/freeware aps that can react to change or lack of change of a file. That might work. I can also base this on a file updating on the network that many PCs have rights to read. The PC copies the entire MBB to the network every 4 hours.

Suggest what you would do...

~

http://www.DeadPhone.com/

http://www.GHTROUT.com/

 

[JerryKlmns]

Why don't you open a connection and check the last record added to the basic data keeping records? Or if there is no time there, just the record count?

If you open an mdb and then close it, there is a time change on file!
If you add data that doesn't consume "enough" data storage you might not get a file size change!

You should check what the mdb is supposed to do. Hold records!

 

[GHTROUT]

How would I set that process it to run a command if there was no change in record count since the previous check?



~

http://www.DeadPhone.com/

http://www.GHTROUT.com/

 

[JerryKlmns]

GHTROUT,

I don't understand your last question.

My thought was to use a form with a timer control set to an appropiate time interval for checking.

Build your connection (Cnn), declare it at the top of the module.
When timer fires, open the connection, execute this SQL statement

SELECT Count(*) FROM YourTableName

against the connection and assign the returned resultset to a recorset [YourTableName holds the main inuput data table].

Keep the value in a module variable.Close the recordset and connection
Next time it fires check the new result with the variable. If there was no addition do what you need.

Option Explicit
Dim Cnn As ADODB.Connection
Dim LastRecordCount As Double
-----------------------------------
Sub Form_Load
Set Cnn = New ADODB.Connection
With Cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source") = "C:\myDB.mdb"
End With
LastRecordCount = -1
End Sub
-----------------------------------
Sub Checking()
Dim rst As ADODB.Recordset
Dim NewRecordCount As Double

Cnn.Open
Set rst = Cnn.Execute ("SELECT Count(*) FROM YourTableName")
NewRecordCount = rst.Fields(0)
rst.Close
Set rst = Nothing
Cnn.Close

If LastRecordCount <> NewRecordCount Then
[green]'   extra code of yours[/green]
    LastRecordCount = NewRecordCount
Else
    MsgBox "No change in record count in table YourTableName"
End If
End Sub

 

[GHTROUT]

OK, I think I see how that could work. Since the MDB is unnatended for as long as a week at a time, I could have one or even more PCs open another MDB (use scheduled tasks and have tables linked to MDB I need to monitor) then query the highest record number and append it locally - then close msaccess. When the scheduled task runs, your code example could run on the startup form and at that point I can run some command after:

If LastRecordCount <> NewRecordCount Then
"enter code of yours" (like a batch that starts "blat" (the email from command line freeware) and send an email to my department distro list.

I need to break down and get over the limits of macros and use some code. Thanks for your help...I like the direction of that method.



~

http://www.DeadPhone.com/

http://www.GHTROUT.com/

 

[GHTROUT]

I was able to set a solution in place today. Some variables of course, but the core method is based on your assistance. Thanks again.


~

http://www.DeadPhone.com/

http://www.GHTROUT.com/

 

[JerryKlmns]

GHTROUT

You 're very welcomed. Thnx for the *