Database corruption – how to cope with users shutting down

[Cronk]

Second time I’ve posed a question on this site.


Firstly, by way of background, I was called to a site yesterday, a crisis that the “database had crashed”. The application is used by a local government authority to issue permits for events being held on public land. After first making a copy of the BE, it turned out that one of the key tables (tblPermits) could not be opened (“format could not be read…”).


Compact and Repair led to the table being removed by Access. After repeating this and getting the same result, I got straight on to the IT support people and had a backup copy from the previous night within 10 minutes. All up and running in half an hour.


Now the issue. Users run the application from FEs on C drives of laptops in networked docking stations and can take the laptops home for working remotely. I believe this corruption was caused by the manager of the unit who on the previous day, had not shut down the laptop but merely closed the screen to put it in sleep mode, before taking it home and then waking up to continue with a Word document which had no problem; not so the database.


When I pointed out the bleeding obvious to the manager, he fully accepted the situation but wants me to set up something to cope with this same event in the future, because he says in the rush at the end of the day, he’s likely to do the same again.


I can’t see any way from within the Access application that could trigger a close of the database. Maybe there is a Windows API that could be used. I’ve done some cursory searching but before going further, I’ll ask if anyone has any suggestions/advice.

 

[jdraw]

Cronk,

This may be of interest (just found via search after reading your post)

 

[Insane_ai]

The answer is here (Check the connection then maybe combine with Cronk's response):
https://stackoverflow.com/questions/35222503/check-internet-connection-from-excel-vba

Option Explicit

#If Win64 Then
    Public Flg As LongPtr
    Public Declare PtrSafe Function InternetGetConnectedState _
            Lib "wininet.dll" (lpdwFlags As LongPtr, _
            ByVal dwReserved As Long) As Boolean
#Else
    Public Flg As Long
    Public Declare Function InternetGetConnectedState _
            Lib "wininet.dll" (lpdwFlags As Long, _
            ByVal dwReserved As Long) As Boolean
#End If

Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20

Function IsInternetConnected() As Boolean
    Dim R As Long

    R = InternetGetConnectedState(Flg, 0&)

    If Flg >= INTERNET_CONNECTION_OFFLINE Then
        Debug.Print "INTERNET_CONNECTION_OFFLINE"
    End If

    If CBool(R) Then
        IsInternetConnected = True
    Else
        IsInternetConnected = False
    End If
End Function

Sub main()
    Dim mssg As String
    If IsInternetConnected Then
        mssg = "Connected"
    Else
        mssg = "Not connected"
    End If
    MsgBox mssg
End Sub

 

[The_Doc_Man]

because he says in the rush at the end of the day, he’s likely to do the same again.

And you will have to do the same thing you did again, too. Once that disconnect (pick up laptop from docking station, take it home, try to reconnect later) occurs, you (and he) are screwed. Here is why.

Your connection to the database rides a TCP/IP "channel" using Server Message Block protocol, which is a member of the IP sub-set of TCP/IP. This IP connection has a negotiated port number on both ends of the virtual connection. The complete connection includes the IP address and port number.

Inherent in this connection address is that the source of the connection has an IP address. The docking station is very likely to be hard-wired through the company routers. Thus the docking station address will have a sub-net related to the router through which it runs.

The connection he uses at home uses a different hard-wired (or worse, wireless) router with a different IP address. At the minimum, it will be a different sub-net. This is NOT the same connection that was still open. And he won't be able to close the connection on the laptop without shutting it down and rebooting. BUT the OTHER end of that connection is still open and is listening to "dead air."

The problem is that if the physical link layer is disconnected, the server (which was the receiving side of the connection) cannot tell its partner "I'm shutting down now" because his partner, not being there, cannot supply the ACK of the message that contains the shutdown flag. The handshake cannot occur so that connection stays open. Anything that was dependent on that connection stays locked.

The fact that the laptop with the same MAC address shows up later on a different network doesn't matter. TCP/IP doesn't use MAC address verification when the two IP addresses differ in the sub-net number. Therefore, when the laptop fires up another shot at the DB, either it won't be able to (due to its own file locking) or it will create a second instance of the connection. That is because the laptop cannot supply the right IP address for the connection. THAT socket is hopelessly locked by that time, waiting for but doomed to never see the ACK of the DISCONNECT message.

Tell that supervisor person that he CAN take his laptop home and work from home but he CANNOT just close it and walk out. He MUST MUST MUST close the app so that all resources are properly released before he undocks. Then charge him extra for recovery every time he does it.

Now, as to a WORD document, there is something about word that checkpoints the document every so often. Further, WORD has a less complex component object model to be recovered. So WORD recovery is very likely with minimum data loss. Access does NOT checkpoint every so often.

Seriously, you need to impress on this unwise child that to close, undock, and move the laptop virtually guarantees data disruption if ANYTHING is running at the time from the laptop in question.

 

[Mark_]

Or you can charge them for several months worth of work to rework your application to not use normal communications modes.

If you have every user export requests as files that a process on your back end sees and does an import, you don't care if they close in the middle. If you then write your response to a file that THEY import, if they never do, you don't care.

This is a months long process to rework everything to deal with this type of situation. If you can spec it out briefly and give them a proposal, hopefully their manager will see it make far more sense for him to do the right thing than for them to pay you three/four digits to come fix when he's "In a hurry" or five/six digits to redevelop things so "This won't happen again".

Telling him he can't do something will not carry the same weight as showing what it will cost if he does. Course I AM being in an evil mood at the moment...

 

[MarkK]

I believe this corruption was caused by the manager of the unit who on the previous day, had not shut down the laptop but merely closed the screen to put it in sleep mode

Any evidence for this belief? I have severed connections like this to the BE many times and never destroyed a table. At one warehouse I run an FE on a laptop over wifi, and it routinely loses the connection to the BE, which is exactly what you are describing. The FE keeps running but disconnects, and raises an ugly "Your network connection was interrupted" error, so we routinely just shut down the FE (clicking thru a bunch of errors) and restart it. It has never caused corruption in the BE.

I just think that before you jump thru a bunch of hoops assuming you know the cause, I would take a step back. I would let this run as is, and see if the same problem arises again. Sounds like the fix you implemented, revert to backup, is the cheapest one too.

Anyway, those are my thoughts,
Mark

 

[Pat Hartman]

Rather than rewriting the entire app (or using a different platform), you might consider converting the BE to SQL Server. This is likely to be not trivial if the app is old and was developed by a non expert but far less of a problem than a complete rewrite or port to a new platform. Although Access cannot recover a connection that was severed, you are unlikely to loose any data since SQL Server is more robust in how it deals with dropped connections. This may be the path of least resistance if the boss has more money than brains.

 

[Cronk]

Thanks to everyone who has replied.



Firstly, to those that suggested having some sort of time out running on a timer event in the database, this won't work because once the laptop is in sleep mode, the database is suspended.


To keep my original post short, I did not add the fact that this database is planned to be replaced with some browser based system in 18 months and the budget of the section is limited.


Consequently, conversion to a SQL server backend is not practical, nor is the re-writing of code to have non-bound forms.


@MarkK, I think you have been lucky so far. Depending of what is happening when the network connection is broken, you will not have any problem. Reminds me of a preface to a computing book I read a long time ago - "There are 2 types of hard disk users, those that have had a hard disk crash, and those that will have one"


@Doc, thanks for the detailed technical explanation of the issue. I did give the supervisor a simple explanation of the problem. Fortunately he is savvy enough to restore a backup, and the nuisance of that will be his penalty for not shutting down properly.



I was fishing for anyone who might have used tapping in to the Windows API that is executed to put the OS into sleep mode, to see if that could provide a trigger. However, no bite and time/money do not enable further investigation.



I've mark this thread Solved. (I'd use Finalised if that was an option.)

 

[Pat Hartman]

18 months is a long time. Isn't protecting the data important? Converting to SQL Server is the path of least resistance and may not even be difficult depending on what's under the covers. You could test it in about two hours by not changing anything except relinking the tables. SSMA (SQL Server Migration Assistant) is the current method of upsizing. If you are lucky enough to be running A2010, you still have the upsizing wizard built in which is infinitely less problematic to use.

 

[Cronk]

Pat, what you propose would be fine if I was dealing with a client who was not part of a government public service, which has a corporate IT section with very bureaucratic rules about who can connect to their SQL Server and which charges high costs (lots of overheads included) for the privilege. I have no ability (permissions) to install software on the any PC on the organization network.



Nevertheless, it is an option that I will include when I front the client tomorrow with alternatives. The outcome I expect is that he will accept that the best alternative is for him to close down Access properly.