Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

So the BE is okay to be on a web-enabled network drive if the drive supports SMB protocol, correct? How would I verify whether it does or does not support that?

 

[The_Doc_Man]

You would have to have two things: A knowledge of what configurations the drive will support (owner's manual, probably available from the drive vendor online) and a knowledge of WHICH configuration was selected to configure the drive.

I don't know offhand of a simple experimental test because the definitive test involves something called a Sniffer, which is a network analysis appliance you hook on a network to see what traffic is on the wire. It ain't cheap but it can solve that question in seconds.

Indirect tests have the issue that as long as two users aren't sharing the BE file simultaneously, an internal lock collision won't occur anyway, so a "wrong protocol" wouldn't notice the difference right away. You need a fatal interaction to prove the point.

 

[Marshall Brooks]

Okay - I don't know even where the drive is - and I'm not sure who does, much less how it is configured. I can check.

May be able to get the sniffer set up.

You need a fatal interaction to prove the point.

How would I set that up? I'm thinking if I created a "test/dummy" BE on the network drive and had new copies of the FE to access that file instead of the real one ...

 

[Marshall Brooks]

We are mixing terms again. The network that the FE is located on MIGHT be NAS, but is not web-enabled. The network that the BE is located on is web-enabled. I don't know if it is NAS, but nobody accesses it from the internet.

I asked someone in IT if the network the backend is on is configured for SMB protocol.

 

[Minty]

You are obviously not sharing the file with another user. SHARING is what this thread is about.

Yes I am, it's on the home wired network, and is accessed by other users(mainly the wife!) on other machines when I am testing. It certainly doesn't download a copy to the local machine. It acts just like a file server.

 

[Minty]

@Minty.
Are you saying that you managed to install the same drive using two different methods?

No I think it was a standard install. it works as both a File server, and has a Web based management interface, that I believe if I opened the ports on my router would allow external (User/Password protected) internet access to the same files.

Locally (on my home network) I can access the files via both methods. I could turn off the web based access as I don't actually need it, but I'm pretty sure it was enabled by default. You wouldn't be able to connect to the web instance of the file, as I am pretty certain Access wouldn't see it as a valid connection. When I'm not busy (I'm preparing to go on holiday tomorrow) I'll have a play and see what it says if you try.

 

[Marshall Brooks]

Okay - To clarify some things:

• IT replied back and confirmed the server that the BE is on is SMB. It appears to be web-enabled, but none of our users access it from a web interface.
• I spoke with someone else in our department and I think I misunderstood and the FE for Citrix users is not on an NAS. I think it is on a typical shared network drive, although it is configured so that users only have access to one subdirectory.

I suspect the issues that I'm seeing (duplicated records, deleted records, improperly copied records) are primarily network connectivity issues. I have three issues to address/ask about:

• Citrix for us is configured to shut down after a set time. It's a rather long set time (3-5 or maybe 8 hours), but it is not an idle timeout. IOW, you can be in the middle of working in Citrix and a purple bar goes across the screen with "Shutting down in 2 minutes unless you click OK." I'm not sure how elegantly it shuts down - i.e. does it close all running apps and shut down, or does it just remove the virtual server? If it does the latter and one of our users has a bound form open, would that potentially cause issues? If so, I'm not sure what I can do about it. We've asked the Citrix support group before, but haven't been able to discern much.
• There are 4 ways that our non-Citrix users connect to the database:
  • In the office via wired Ethernet, which is fast but can be spotty - i.e. The network connection was lost, please close and re-open the database.
  • In the office via wireless Intranet - same as above. For some users the wireless in-plant is more stable than the wired. Typically not much of a discernable different.
  • From home via VPN (Pulse Secure/Ivanti) - Very slow. Connectivity varies, but personally, I'm not sure it loses the network connection more often than in the office.
  • Technically, all of our non-Citrix users do HAVE the ability to open the database in Citrix, either from home or in the office. I used to do this from home when I was working intensive tasks in the database. I've started doing it from home whenever I need to use the database. But I don't think just suggesting that users do this will work. Questions:

Using VBA, how can I determine whether a user is using VPN or Intranet? I thought this or similar might help: https://stackoverflow.com/questions...ffice-intranet-or-office-wifi-using-excel-vba but that only verifies that they are connected to EITHER Intranet or VPN, not which one?​

​

If I determine that they are using VPN, I'm not sure how to force them to use Citrix. Currently, the database only opens locally from the desktop or from the shared drive (Citrix). If I wanted to ONLY allow Citrix usage, I could disable the desktop. Technically, that doesn't FORCE them to use Citrix, but I tried once and it took about 8 minutes for the database to open from the network path outside of Citrix.​

• I'm not crazy about it, but I'm considering adding an idle kickout to the database that would occur after say 20 minutes of inactivity. If I did this, would it be okay (from a corruption standpoint) to just close all bound forms and return them to the switchboard rather than closing the database? This might help with the Citrix timeout issue, although users could still open the database 15 minutes before Citrix timed out and still have problems.

Thanks for sticking with me!!!

 

[The_Doc_Man]

I'll answer what parts of that question that I can.

If your CITRIX session dies simply by breaking the connection then you have a "dangling" process wherever Access was physically running. When that dangling process gets resolved (if it ever does), then is when corruption can occur. However, if in the interim you have a lock collision with another user working the specific area where the dangling process was working, that can hasten the demise of the situation.

The network status of VPN vs. ordinary Internet/intranet connection is going to be tricky because that is in the data link layer (below the end-to-end layer, which is layer 3). Only the network driver itself will know. I found one reference that involves writing a function to return T/F based on VPN or not using the Win Management Interface to query the connection properties.

Determine if connected to VPN or Office Intranet or Office Wifi using excel vba

I have the following code with which i am successfully checking if i am connected to VPN from home to access company network folders. Sub doit() If ConnectedToVPN Then ' run other code to ...

stackoverflow.com

Do not use an Idle Kickout without taking into account the possibility that there might be something active. In a properly secured DB, this something else can only be a form, which can have its own timer. What I did was I had a Public flag in a general module and it was, essentially, a "permission to run" flag. If I wanted a shutdown, I set the flag ahead of time and waited. Every form had a timer event that tested the permission flag every 10 minutes. If it ever saw "no" then it forced the form to start shutting down by determining whether anything was running. If the form was currently dirty, it forced a timed popup that exited after 10 seconds. It ran a Me.Undo and then told the form to close. The timer was on a form that knew to check for other forms and not release itself until the Forms collection showed no more active forms (than itself). At which point it did an Application.Quit for Access itself.

 

[Marshall Brooks]

@The_Doc_Man - Thank you - I'll take some time to digest this and see where it takes me!

 

[Marshall Brooks]

Okay, I'm having a weird issue. I have the idle logout code working. I didn't take into account the form possibly being dirty and I probably should, but I don't think that is causing the issue.

I did the logout code differently than most examples. Our database has a switchboard and 7 data forms typically in use. I wanted code that would close the data forms after 15 minutes of inactivity, leaving the switchboard and the database still open.

With the idle logout code running, we are getting a "Type Mismatch" error whenever the MSO FileDialogFilePicker prompts to select a file or folder. Usually it gives an error the first time, and then it runs correctly.

Skeleton code looks like this:

Sub Error()
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
With objDialog
If .show = -1 Then 'Ok Pressed
    ' Do Something
Else ' Cancel Pressed
    Exit Sub
End if
End Sub

I commented out lines and figured out the Type Mismatch is from the line If .show = -1, although that seems to be correct.

I tried rewriting it to:

If .show = 0 ' Cancel pressed
Exit Sub
Else ' OK Pressed
Do Something
End if

That seemed to work so I changed most of the database (that code is used many places), only to find out that it also fails initially and works the second time.

I could avoid the issue by removing the IF-Then block, i.e.
.Show
' Do Something
and just hoping that users don't click Cancel, but that is a rather crude workaround.

The idle logout code looks like this on each data form:

Option Compare Database
Option Explicit
Dim lngActivityCounter As Long

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
        lngActivityCounter = 0
End Sub

Private Sub Form_Timer()
    ' https://www.accessmvp.com/JConrad/accessjunkie/kickoff.html - Item 23 Kicks off if no mouse movement or Keyboard movement. and T2 Database - MB 8-Jul-2024.
    On Error GoTo form_timer_err
    Static last_ctl As String
    Dim curr_ctl As String
    curr_ctl = Nz(Me.ActiveControl.Name, "No Active Control")
    Me.TimerInterval = 30000 ' 30 seconds
    strResult = ""
    If last_ctl = "" Or last_ctl <> curr_ctl Then
        last_ctl = curr_ctl
        lngActivityCounter = 0
    Else
        lngActivityCounter = lngActivityCounter + 1
    End If
     '15 minutes = 900 sec (15 min x 60 sec/min)
     'After 15 minutes (900 sec/min) of no activity on this tab,
     'close this tab
     'TimerInterval=30 seconds, so 30 counts
     If lngActivityCounter >= 30 Then
        strResult = Dialog.Box(Prompt:="The <form name of inactive form> will close due to inactivity.\n\nClick OK to close immediately.\n\nClick Cancel to cancel closure." & "", Buttons:=(1 + 48), TITLE:="Inactivity Timeout", AutoCloseSec:="30")
            If strResult = vbOK Then
                DoCmd.Close acForm, Me.Name, acSaveNo
            Else
                lngActivityCounter = 0
            End If
     End If

form_timer_err:
  If Err = 2474 Then
    Resume Next
  End If
End Sub

I'm not seeing why I am getting the Type Mismatch with the timer code in place and not without, and I'm not seeing why the code works the second time I run it, but not the first.

More disclosure. The entire with block looks like this:

    With objDialog
        .AllowMultiSelect = False
        .TITLE = "Please browse for Draft PDF Document to send to review."
        .ButtonName = "Select"
        .Filters.Clear
        .Filters.ADD "PDF Files", "*.pdf"
        ' Typically Access will open the Documents folder and then the last selected folder.  The code below makes it open the writers folder initially and then the last used folder. Otherwise, initialFileName would ALWAYS open the writer folder.
        If FileDialogDisplayed = False Then
            .InitialFileName = Nz(ELookup("[Network_Path]", "[tblTeam]", "[Assignee] = '" & Assignee & "'"), "")
        End If
        If .show = 0 Then ' Cancel pressed
            Box ("No Files Selected. Exiting")
            Set objDialog = Nothing
            Exit Sub
        Else
            FileDialogDisplayed = True
            AttachFile = .SelectedItems(1)
'             https://stackoverflow.com/questions/12687536/how-to-get-selected-path-and-name-of-the-file-opened-with-file-dialog
             Dim FileNameOnly As String
             FileNameOnly = Dir(.SelectedItems(1))
'            If UCase$(Mid$(objDialog.SelectedItems(1), InStrRev(objDialog.SelectedItems(1), ".") + 1, Len(objDialog.SelectedItems(1)))) <> "PDF" Then
            If UCase$(Mid$(FileNameOnly, InStrRev(FileNameOnly, ".") + 1, Len(FileNameOnly))) <> "PDF" Then
                Box ("Selected file MUST be a .PDF File. Exiting.")
                Exit Sub
            End If
        End If
    End With

I thought the FileDialogDisplayed = False block could be doing it, but I still get errors without that.

Initially, I had the idle timer checking once a second instead of every 30 seconds and I thought I was getting errors more frequently with that, but it might have just appeared that way.

 

[Marshall Brooks]

Ruling some things out:

Application.FileDialog.Show = -1?

This is less of a problem question, and more of an understanding question. I stole the code below: Dim fd as Office.FileDialog FileDialog. Set fd = Application.FileDialog(msoFileDialogFilePi...

stackoverflow.com

Replacing "If .show = -1 Then" with "If .show Then" still gives the Type Mismatch error also.

 

[Marshall Brooks]

Solved (the current issue). (Almost)

I can avoid the error by turning off the timers before the file picker dialog opens and then turning them back on after it closes.

I'm not sure why that is required, but if someone if running this procedure, then they aren't idle.

I did find out I will need to turn off the timers for ALL the open forms ... i.e. this code is associated with Form A, but if Form A and Form B are both open, I need to turn off both timers or I will potentially get the error.

 

[Marshall Brooks]

@The_Doc_Man - New issue with the Idle Timers. The timers load with the form. I have a button that sets "Dev Mode" and shuts down all idle timers for all open forms and sets a global variable "TimersAreOff" to true. But if I close and re-open a form, the timer is reset and I get errors if the VBA is being modified. I added code to the Form Load events to set the timerInterval to 0 if TimersAreOff is true, but if I have an error in the VBA, it resets the global variables, which re-enables the timers, which then generates error messages. How can I KEEP the timers off during development?