Code to Monitor for An Unanticipated Event (table deletion)

[Tim Marshall]

Hello folks,

Is there some way I can run some code in the background (possibly with a hidden form or otherwise) that can monitor and capture when an unanticipated drop table event might take place? In this case, what is happening is a local Jet table in an application is mysteriously being deleted.

I have an A2003 mde deployed on various PCs running A2003, A2007, and A2010. The application is only run on local PCs and manipulates data via pass-through queries on a server database (Oracle via ODBC). However, there are some local jet tables. One of these, tbl_Local_Prefs , is a single record with two fields for the user's name and is practically never updated. It has a single record with the name of the user. On some A2007 on 64 bit Win 7 pro machines, it is somehow being deleted from time to time.

The application is run multiple times every week day and this mysterious deletion occurs as much as once per week, sometimes more often than that, on the same two machines. Both machines run only A2007.

There is no code in the application to drop the table, and no table definition code whatsoever. I do have code run on start up that tries to get rid of those awful log message tables (in my case, my user name is Admin, so it's Admin - 001, 002, etc). It is as follows:

Public Sub sAdminOracleTablesRemove()
'Goes through tabledefs abd deletes Admin tables if they can be deleted.
'Log Messages are already all no, so that approach to suppress the creation 
'of these tables does not seem to work with Oracle PTQs
'
'DBLocal is a David Fenton proc that leaves a persistent database object throughout the session.

    Dim tb As DAO.TableDef

    On Error GoTo Err_Proc
    
    dbLocal.TableDefs.Refresh
    
    For Each tb In dbLocal.TableDefs
        If Left(tb.Name, 7) = "Admin -" Then
            Debug.Print tb.Name
            DoCmd.Close acTable, tb.Name, acSaveNo
            DoCmd.DeleteObject acTable, tb.Name
        End If
    Next tb
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 3211 'in use by another person - These tables are sometimes locked.
            Resume Next
        Case 2450 'frmv not available - when closing
            Resume Exit_Proc
        Case Else
            MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, "sAdminOracleTablesRemove", Err.HelpFile, Err.HelpContext
            Resume Exit_Proc
    End Select


End Sub

I don't think this affects the table, tbl_Local_Prefs , in question.

Any suggestions on how to capture when tbl_Local_Prefs might be dropped would be greatly appreciated.

Thank you very much in advance,
--
Tim

[NeoPa]

I see nothing in your code that could possibly have the effect of deleting any table named [tbl_Local_Prefs].

On the other hand, there is no way of capturing a table deletion from within Access that I know of (I'm pretty sure that means there is none). That is not to say that you could not determine pretty shortly afterwards that it had taken place, but if you're looking for a caturable event that you can interact with and even possibly cancel, then I'm afraid you're out of luck.

On the other hand, not the first one but a new other hand - think three handed here, Oracle, as a proper grown-up BE Server system, should certainly have Triggers or their equivalent, that can be set up on the server to capture and interact with these strange occurrences. My experience is with SQL Server I'm afraid, but if you're interested in delving into this further from that angle then may I suggest posting a facsimile of this question in our Oracle forum and seeing what comes up.

[Tim Marshall]

Neopa, thank you very much for your reply. I will indeed trawl through the Oracle

However, I'm not sure if Oracle could help. I'm not an Oracle expert (except in writing SQL), but I do have a fair bit of experience using Pl/SQL (t-SQL in SQL Server, right?) in various triggers, but I'm not sure how they would reach through the ODBC connection to affect local jet tables.

Is it possible some sort of network interaction could do something to a local Access table like that? I've been playing with Access since 1998 and used to hang out on the c.d.m.a usenet group up until the mid 00s, but I'm under the impression that could not happen.
--
Tim

[zmbd]

Please check the design of the BE to see if there is a table with that name. I ran into a situation with an instrument that I was using MSA to pull reports from the Ora... one table in my FE had the same name as a temp table in the BE and somehow when Ora would drop the table it was droping the same named table in the front end. No-one knows why or how but it was with a MSA2003 mde - just a stab in the dark and an easy check.

[NeoPa]

Apologies Tim. I did read the question, but there was a lot to keep in mind while trying to think it through without the project in front of me. I'm afraid I overlooked the bit where you said (quite clearly) that it was a local Jet table that was deleted. My suggestion was certainly related to the Oracle Server, so wouldn't help in your particular situation.

[ADezii]

I actually think that Tim's initial concept will work. Here is what I did with positive results:

1. Opened a Form in Hidden View with its Timer Interval Property set to 300000 (5 minutes).
2. Placed the following Code in the Hidden Form's Timer() Event:
   

   Private Sub Form_Timer()
   Dim tdf As DAO.TableDef
   Dim blnTableFound As Boolean
   Const conTABLE_NAME As String = "tbl_Local_Prefs"
   
   Open CurrentProject.Path & "\TableLog.txt" For Append As #1
   
   blnTableFound = False       'Initialize
   
   For Each tdf In CurrentDb.TableDefs
     If tdf.Name = conTABLE_NAME Then
       blnTableFound = True
         Exit For
     End If
   Next
   
   If blnTableFound Then
     Print #1, Now() & " " & conTABLE_NAME & " found"
   Else
     Print #1, Now() & " " & conTABLE_NAME & " [NOT FOUND]"
   End If
   
   Close #1
   End Sub

3. Every 5 minutes, the Code will Poll the TableDefs Collection and check for the existence of tbl_Local_Prefs . It will then write the Outcome to a Log File in the same Directory as the DB.
4. Within a couople of minutes, you will be able to see when or if this Table gets Deleted. The Time Interval can obviously vary by adjusting the Timer Interval.
5. I ran the Code for a few minutes, then Deleted tbl_Local_Prefs . I then posted a segment of the Log File for your review:
   

   11/17/2012 10:44:18 AM tbl_Local_Prefs found
   11/17/2012 10:49:18 AM tbl_Local_Prefs found
   11/17/2012 10:54:18 AM tbl_Local_Prefs found
   11/17/2012 10:59:18 AM tbl_Local_Prefs found
   11/17/2012 11:04:18 AM tbl_Local_Prefs [NOT FOUND]
   11/17/2012 11:09:18 AM tbl_Local_Prefs [NOT FOUND]
   11/17/2012 11:14:18 AM tbl_Local_Prefs [NOT FOUND]
   'will continue [NOT FOUND] until Table gets recreated

6. Good Luck - any questions feel free to ask.

P.S. - You must obviously leave the DB as well as the Form Open (Hidden) in order to achieve the desired results.

[NeoPa]

NeoPa:
On the other hand, there is no way of capturing a table deletion from within Access that I know of (I'm pretty sure that means there is none). That is not to say that you could not determine pretty shortly afterwards that it had taken place, but if you're looking for a caturable event that you can interact with and even possibly cancel, then I'm afraid you're out of luck.

ADezii:
I actually think that Tim's initial concept will work.

No argument from me on that score ADezii, but bear in mind this is not capturing the event when it takes place, but is rather detecting the deletion after the fact. There is no facility here to cancel or take any control over the deletion itself, such as would be the case with a Trigger or similar feature.

[ADezii]

I must have misinterpreted the request in that I thought the OP wanted to know the approximate time when the Table had been deleted, as previously stated:

Is there some way I can run some code in the background (possibly with a hidden form or otherwise) that can monitor and capture when an unanticipated drop table event might take place?

[NeoPa]

We seem to be reading the same text in two different ways. Your code can monitor the event, and even react to it. I wouldn't say it captures the event though. Not in terminology that makes sense to me. I read that to indicate a desire to stop the table being deleted in the first place. I don't see much use in knowing about it otherwise, other than to fix it as soon as possible after the fact.

Now that both sides have been explained though, and what the code can and can't do, it's all there for anyone reading the thread to benefit from, in the knowledge of what it can do for them. What it does is far from negligible, yet nevertheless is somewhat less than what I interpreted the request to be for.

[zmbd]

Create a second table... call tbl_somename doesn't matter.
In tbl_somename create
[somename_pk]
[somename_fk_tbl _Local_Prefs]
[somename_fk_tos omeactivetable]
enforce integrety updates only

Now tbl_Local_Prefs should not be able to be deleted so long as there is a related record in tbl_somename. If there is an attempt jet should thru an error. Wont catch the event but should prevent table from dropping. Would still like to know if the scheme has same name in the Ora-BE

[zmbd]

building on Adezii and my linked table...
Why not take Adezii idea and add afield to the form that is linked to the tbl_Local_Prefs say on the primary key... set the before delete event of the control to record the event. Do this in addition to linking to the secondary table. Now we have a way to check the existence of the table, if the record delete event fires, and with an enforced record integrity something should stop/catch the event.

[NeoPa]

So, if I understand what you're suggesting, you would :

1. Add a table to the FE DB.
2. Set up a one-to-many link between this new table and tbl_Local_Prefs with the new table on the Many side with Referential Integrity and Cascaded Deletes both enforced such that any delete attempt of the One side (tbl_Local_Pref s) would trigger an attempted delete of all related Many records.
3. Create a form bound to the new table (Many side) which stays open but hidden at all times.
4. Include in this form a Form_BeforeDelC onfirm(Cancel As Integer, Response As Integer) event procedure to capture any such deletion.
5. Within the procedure set Cancel = True.

I have an idea this may (only may mind you) not work when an attempt to delete the table is submitted. That said, it's a very canny idea and well worth investigating. Frankly, it's darn clever even if it doesn't. It may be that the best approach is the earlier one, embedded within this, which ensures the table cannot be deleted quietly as a relationship exists connecting tbl_Local_Prefs to another table.

Hats off to both of you. This was never going to be straightforward , but that didn't stop either of you coming up with ideas and suggestions.

[ADezii]

@NeoPa:
I agree with you in that it was a very clever approach by zmbd.

P.S. - To further simplify the Logic:

1. Create a Form Bound to tbl_Local_Prefs .
2. Open it in Hidden View.
3. Any attempt to DELETE tbl_Local_Prefs , should not be successful, and will generate 'cannot delete tbl_Local_Prefs since it is already in use by another process'.
4. With this approach no additional Related Table, Relationship, or Code in the BeforeDelConfir m() Event of the Form is needed.
5. Comments?

[NeoPa]

This is possible ADezii, however :

This keeps the table locked at all times. It will stop any other amendments to the design of the table too. Probably not an issue, I would guess, but that would be an assumption.

It doesn't capture the event in code as the alternative referred to is intended to do. Again, that may well not be important, but was in the question as far as I read it at least. The alternative may not even do that though. That is still to be confirmed.

In conclusion, I can see many scenarios where this would be a preferable solution, but some where the more complicated approach is required. On balance though, if it were me and the event code doesn't work, I'd be using this suggestion as a preference. At least until something in the table being permanently locked area caused me problems.

[ADezii]

As a final thought, I was basing the simpler Logic on the OP's statement:

One of these, tbl_Local_Prefs , is a single record with two fields for the user's name and is practically never updated. It has a single record with the name of the user.