Has table been opended?? 1

[gertNo1]

Hi Guys;

I am trying to validate a table after it has been updated in order to do that I want to know if it has been opened. The database is used in several production runs and I don’t want to impact production unnecessarily. I created a form that is suppose to hide and loop until the table has been opened or the form is closed via the database being closed.

Needless to say it is not working. The loop that execs on the form_load event is preventing the table from opening. The forms visible = false command is not working. The subsequent query that validates the table won’t run because the table it locked. When the database closes the form_Close event is not working, the database closes and the close event does not exec.

Bottom line this is not working! Any suggestions

Option Compare Database
Option Explicit
Dim glSysPrin_Opened As Boolean

Private Sub Form_Load()
    Forms!frmTrapDbaseClose.Visible = False
    'Validate_SysPrinNonRSF_tbl
End Sub

Private Sub Form_Close()
Dim db1 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim sRecCnt As Integer

DoCmd.SetWarnings False

Set db1 = DBEngine(0)(0)
Set rs1 = db1.OpenRecordset("PrinA", dbOpenDynaset)
Set rs2 = db1.OpenRecordset("SysPrinB", dbOpenDynaset)
Set rs3 = db1.OpenRecordset("tbl_UnMatched", dbOpenDynaset)

Do While Not rs3.EOF
    rs3.Delete
    If Not rs3.EOF Then
        rs3.MoveNext
    End If
Loop

On Error GoTo Err_FrmClose
Stop
If glSysPrin_Opened Then
    If Not rs1.EOF Then
        rs1.MoveLast
        If rs1.RecordCount <> rs2!RecCount Then
            sRecCnt = rs1.RecordCount
            rs2.Edit
                rs2!RecCount = sRecCnt
            rs2.Update
            DoCmd.OpenQuery "qryList_Offer_Outgoing"       
            DoCmd.Close acQuery, "qryList_Offer_Outgoing", acSaveNo
            DoCmd.OpenQuery "qryFind_Unmatched_Outgoing_Rates"       
            DoCmd.Close acQuery, "qryFind_Unmatched_Outgoing_Rates", acSaveNo
            If Not rs3.EOF Then
                rs3.MoveLast
                If rs3.RecordCount > 0 Then
                    DoCmd.RunMacro "mcrUnmatched_Outgoings"
                End If
            End If
        End If
    End If
End If

DoCmd.SetWarnings True
Application.CloseCurrentDatabase

Exit Sub
Err_FrmClose:
    MsgBox "Error in Form_Close SubRtn  " & Err.Description & "  " & Err.Number
    Stop
    Resume
End Sub

Function Validate_SysPrinNonRSF_tbl()
 ' Returns True if the specified table is open.
    Dim strCallingTblName As String
    
On Error GoTo Valid_Err

Do While glSysPrin_Opened = 0
    strCallingTblName = "Sys Prins non RSF"
    
    If SysCmd(acSysCmdGetObjectState, acTable, strCallingTblName) > 0 Then
        glSysPrin_Opened = True
    End If
Loop

Exit Function

Valid_Err:
    MsgBox "Error in Validate_SysPrinNonRSF_tbl funciton  " & Err.Description & "  " & Err.Number
    Stop
    Resume
End Function

Thank you

 

[JerryKlmns]

I haven't seen your code but my approach would be this.

Since you know what forms hit this table, add a new table [tblInspector] and store the table names that you need to investigate (currently only one but who knows in the future!). When such a form queries that table go and update a field of tblInspector with the current time [TimeWhen] and onother field indicating new update [bIsUpdated] with Boolean data type.

When you start your processing reset bIsUpdated field to false. Your scheduled process should first check the tblInspector for bIsUpdated=True. A DLookup() function would be sufficient for this purpose.

Now you do know if the table has new updates and when they happend. BUT there is the assumption that the tables are updated only through forms. To prevent users from updating tblInspector (and also update data on your tables) open a pessimistic locking recordset on tblInspector. Before letting the user update any data table check if he can update tblInspector. If he cannot then you are doing your process and he has to wait.

Hope it helps.
Just my 2 cents

 

[gertNo1]

Thanks Jerry for responding, but this table is not linked to any form and the Users don't want it to be linked. The Users want to allow updates directly to the table but would like the changes validated.

I agree if the form was behind a table it would be much easier.

Thanks again,
Trudye

 

[JerryKlmns]

gertNo1

Since it is MSAccess there is no way to do this. Triggers are not supported.

 

[gertNo1]

Ok, that makes sense, but I still should be able to exec code On-Close of database shouldn't I?

I thought the form_close would allow me to do that but it doesn't seem to be working (the database closes without exec the code). If not that then should I be using?

Any idea?

Thanx guys for the feedback you saved me a tremendous amount of time.

 

[JerryKlmns]

Would you try the Unload event of the form instead of the Close?