Auto Disconnect Code Help

[WillM]

I have searched several times for a solution to this problem, but can't seem to figure out what the issue is. I have code (copied from here and the MS-Access how to) that has a hidden form that loads on the db startup. Attached to that form is code that is supposed to check for a file, if the file is found, nothing happens. If the file is not found, then it starts a timer and shuts down the front end of every user that has it opened.

The problem I have is that the code doesn't seem to "see" the file regardless of whether or not it is named the correct/incorrect name. The file that it is supposed to look for is in the same folder, on the network, that the database front end is in.

I have tried "\\blah\blah\txtTest.ozx" and "s:\...\txtTest.ozx" but neither way seems to work.

Option Explicit
Dim boolCountDown As Boolean
Dim intCountDownMinutes As Integer

Private Sub Form_Open(Cancel As Integer)
    ' Set Count Down variable to false
    ' on the initial opening of the form.
    boolCountDown = False
    Dim strFileName As String
    strFileName = Dir("S:\folder\folder\folder\txtTest.ozx")
     If strFileName <> "txtTest.ozx" Then
        MsgBox "Database being updated, please try again later."
        Application.Quit acQuitSaveAll
    End If
End Sub

Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("S:\folder\folder\folder\txtTest.ozx")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "txtTest.ozx" Then
            ' The check file is not found so
            ' set the count down variable to true and
            ' number of minutes until this session
            ' of Access will be shut down.
            boolCountDown = True
            intCountDownMinutes = 2
        End If
    Else
        ' Count down variable is true so warn
        ' the user that the application will be shut down
        ' in X number of minutes.  The number of minutes
        ' will be 1 less than the initial value of the
        ' intCountDownMinutes variable because the form timer
        ' event is set to fire every 60 seconds
        intCountDownMinutes = intCountDownMinutes - 1
        DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "This application will be shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work."
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
            Application.Quit acQuitSaveAll
        End If
    End If

Exit_Form_Timer:
    Exit Sub

Err_Form_Timer:
    Resume Next
End Sub

We are using Access 2010.
Any insights/help are greatly appreciated.

 

[Uncle Gizmo]

Add a message box after this line like so:-

strFileName = Dir("S:\folder\folder\folder\txtTest.ozx")
MsgBox " >>> " & strFileName

What happens?

 

[CJ_London]

Just a suggestion, but perhaps .ozx is not being recognised? Have you tried using .txt or .csv? I'm presuming the file is deleted when a backup is in progress and reinstated when backup is completed?

Have you 'Dir'd' the directory to see what filenames it is returning?

e.g. *.ozx or txtTest*

 

[Uncle Gizmo]

What have you got your timer interval set to?

Me.TimerInterval = 5000 '(5 Seconds)

 

[Uncle Gizmo]

It looks to me like "If boolCountDown = False Then" is always False?

 

[Uncle Gizmo]

It looks to me like "If boolCountDown = False Then" is always False?

Ah! I see it gets changed to true by:-

If strFileName <> "txttTest.ozx" Then
' The check file is not found so
' set the count down variable to true and
' number of minutes until this session
' of Access will be shut down.
boolCountDown = True
intCountDownMinutes = 2

It seems to be working ok for me...

 

[Uncle Gizmo]

So you possibly need to set the timer up ...

You could do this in the Open event:-

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 5000 '(5 Seconds)

 

[WillM]

Thanks for your responses...

The Timer Interval is set for 30000 on the Property Sheet, nothing in the VBA code as far as the timer.

I added the Me.TimerInterval = 5000 to the top of the Form_Open section and it still went to the default of being "closed".

 

[WillM]

I also put in the msgbox code and it returned the >>> so it is a least opening and going through that part.

The problem isn't that it doesn't work, the problem is that it always works no matter what the file name is: .ozx or .old.

I will try and change it to a .csv or .doc and see if that makes a difference.

 

[WillM]

It doesn't matter what the file name is, which it shouldn't really care as it is just checking to see if it is there or not. At least in theory, that is what it is supposed to do.

I tried .doc, .csv, and .txt and it immediately goes to the shutdown mode which makes me think it isn't able to see the file on the network, even though it is there.

Are there any issues with Access checking a network server to see if a file is there or not? In my research I didn't see any issues with that, but I am by no means an expert. The file sits in the same folder as the database, so you would think it could see out and back in...it is a split database, so the front ends can all see back into that same folder.

 

[Uncle Gizmo]

I think your forms open event is throwing you out to early...

 

[Uncle Gizmo]

I did a YouTube about it:-

http://youtu.be/qFQlCq86AJg

 

[WillM]

Thank you again for the help. I think we are going to need to go in a different direction. I appreciate all the input and help!

 

[WillM]

As it turns out, user error is almost always the issue. Embarrassingly enough, we had the file misspelled. Seriously, it pays to double and triple check everything. Lesson learned.

We decided to go with a table in the backend for Maintenance, along with a form that has a checkbox (the value in the table is also a checkbox). We then put the hidden form in the front end , created an autoexec macro that calls the hidden form and the main form of the program when it opens. In the "on open" event of the hidden form, we have code setup similar to what I described above, but it just checks to see if the box has a check or not. If it does, then it runs code to not let anyone connect to the database, and within 7 minutes, shuts down all the open forms the users have open. This allows us to then open the database in exclusive mode to make our changes, and then we go back in and remove the check mark and it works great. We even made two macros that will allow us to disconnect everyone at 11PM and let them reconnect at 4AM so when our files are pushed, they can be updated correctly since no one will be in the database with it open.

I hope this helps anyone else who was looking for a solution. We took ideas from several places here and duct-taped them together to make it work for us. Thank you to everyone for your help, especially Uncle Gizmo for the video!