Logging break in connection to Back-End

[Tango1]

An Access system of 10 years vintage is starting to give 'corrupt database' errors. I've written a mini-app to allow the User to run Compact and Repair operations on both the FE and BE databases which allows him re-start working.

I've attributed the problem to the IT department 'doing some work on the network' but the problem (intermittent, say 1-2 time a week) seems to be ongoing.

I'm thinking of writing a mini-app to be run outside the main application that will do something like looping a DLookUp on a small table in the main app just to check it is still there and accurately record when (and if) it disappears. And then potentially when it re-appears.

Is there a better way? I feel I might be re-inventing the wheel!

[SwissProgrammer]

"starting to give 'corrupt database' errors." Attention !

"Is there a better way?" Maybe yes.

(1) I would seriously consider a full backup of the data drive(s) immediately first!

Now! Call in the team. Do an all-nighter or round-the-clock if you can easily afford it, and back up (to clean new drives) at least all of your data drives. Immediately!

(2) After that, defragment the original drives.

Defragmentation has had negative effects if the drive is damaged or if it is so full that the defragmentation process itself is fragmented beyond the useful scope of your drive. So, back up the drives first as they are now. Look at "EASEUS ToDo Backup" and other programs like that which have sector-by-sector backup capability. Do two types of backup: Simple backup of entire drive; and separately do a sector by sector backup.

It sounds like your drives have reached their read/write limits.

That could mean that they have less than enough room for new data.

That could mean that they have written to and read from sectors so many times that the sectors are wearing out and not responding correctly.

(2a) If defragmentation will not work due to damage to the drives, then do a disk check and disk repair. Then do the defragmentation.

(3) Try the original drives. If they work well after defragmentation then use them and apply the 2050 rule. If they do not, then replace them (and load onto the replacement drives the back up) and apply the 2050 rule.

(4) The 2050 Rule:

When you replace your drives, either use larger drives or split the data bases across multiple more drives. Your goal should be to split your current data onto enough drives that the data takes up less than 20% of each drive. Less than means less than, not more than or close to; but less than.

Then, when you see that any of your drives are using more than 50% of the space, split that data across two new drives.

If you get arguments against this then go study how drives hold information and how the sector spaces are allocated, then you will probably see the logic of 20/50

Thank you for asking.

.

[twinnyfo]

Tango1,

Welcome to Bytes!

This "may" be as serious a problem as SwissProgrammer describes. But let's start smaller first. I've been recently experiencing some similar problems with our DBs, and much of it has to do with networks not playing nice with Access (or the other way around, actually). This is particularly exacerbated by VPN access, which seems to corrupt data in Access more frequently, and with telework on the rise in our office, we are seeing more corrupt data files.

I would begin by backing up your FEs and BEs. Kick everyone out of your DBs and keep them out for a while. With your BEs, create brand new, empty DBs and import all your data tables from scratch. Make sure you then also compact and repair those new data files.

Do the same with your FEs. You will have to make sure you also include any additional references in your VB editor. Make sure your FEs fully compile (which if you are making an executable FE--and you should, it won't be created unless it fully compiles).

In 99 44/100% of cases, this will solve your problem. A corrupt disk or network will probably cause more significant problems than corrupt data files. A disk unable to write to a particular sector, for example, will not be able to compact and repair a DB at all, since the data is completely gone.

Hope this hepps!

[Tango1]

twinnyfo and SwissProgrammer thank you both for your detailed responses. You were both err-ing on the side of bad hardware or corrupted code and I would pursue these if I could. However, the client is an 'international' and difficult enough to get through the front door in normal circumstances.....

The BE is on a server in their datacentre. Two things I could possibly do is have them convert it from an MDB to a ACCDDB hoping that is tantamount to exporting to a clean database. Secondly, if they would expose their server/share name I could link via a UNC rather than have them create mapped drives for each user. These are on my 'To Do' list but don't think they are essential tasks.

The BE is comprised of two MDB's. The Main Data and an Audit Trail (recording who changed what, when and by how much). 15 years ago my reasoning that the Audit Trail would grow over time and could be truncated without compromising the live data. However, main data is only 90MB and Audit Trail 50MB, so size has not been an issue!

The FE has not been modified for some time so I don't expect any corruption there but still re-installed from the distribution version. Compact and Repair of the FE does not restore the system, hence I had to write something so the User could fix the BE's.

There are only 4 users on the system, two who just read and print.

All previous experiences of failure in Access have been the results of network issues; BNC connectors being trodden on under the desk; electrical interference from an overhead crane when a motor flashed over; problems storing the BE on a NAS rather than a proper server and more recently people trying to connect via WiFi. Hence my recent interest in Network monitoring.

I can imaging doing a 'DIR' call to see if a file is still available to check the network is 'up', or a DLookup to see if the database is in an inconsistent state. I imagine I am not alone with this problem and thought there may be a clever system call or piece of software that would do this simple task other than professional suites that sit on the server.

Thanks for taking time to respond. I know how much it eats into your day answering these sorts of questions.

[isladogs]

In my experience, when errors occur that indicate corruption, running a compact & repair rarely helps.

I would suggest the following sequence to deal with any corrupted code:
1. Make a backup (just in case)
2. Decompile the FE - there is a very clear explanation of this process on the FMS website. Decompiling only takes a couple of seconds
3. Then recompile by running Debug...Compile from the VBE
4. Compact

[Tango1]

I agree with your first statement isladogs, a decompile is usually required to fix most errors. However, in this instance a C&R on the BE fixes the problem (there is no 'code' in the BE so nothing to decompile) but does nothing for the FE.

I did request the User to re-install the FE from the distribution copy, so should be clean and not require a decompile. I'll check today that he did this and also the exact wording of the error.
I fear he is using a Runtime version of Access.
Thank you

[jimatqsi]

Tango1, could you tell more about the problem, please. Under what circumstances does this problem occur? Is it at startup, during some particular transaction type or process, launching a form, etc .... Are all users seeing it randomly, simultaneously? Are you sure that all users are running the front end locally and not on a network drive?

The most common expression of this problem that I have seen is that users don't understand the difference between running a copy of the front end on their own computer as opposed to using a shortcut to the front end on the network somewhere - and multiple people end up running in the same copy of the front-end. In that case, this kind of result is inevitable.

Jim

[Tango1]

Hi Jim. Thanks for your reply.
The main user is in a laboratory and has the system open all day. He finds the system has broken after entering a item-number and gets the message "database is not available" when trying to retrieve details of that item. (I'm trying to illicit the exact wording.)

Failure is not isolated to just one form but seems can occur at any time an enquiry is made i.e. listings by equipment type or department etc. Hence my belief it is a network issue (and the fact that the software has not changed but it sounds like the network has)
I thought perhaps there was uncommitted data pending in an open form which may have caused a problem if there was a 'hiccup' on the network but am assured that the problem can occur between searches for multiple items. i.e. one works but the next fails.

Although it is the 'main' user who reports a problem I am also enquiring what other user might be doing. Everyone seems to be locked out once an error occurs. It's not inconceivable that someone in 'Store' is goofing a transaction that manages to break the system. I will have this investigated.

[jimatqsi]

What does this mean, what are they seeing? "Everyone seems to be locked out once an error occurs"

Since it affects everyone when it happens, it sounds like the problem is not on the client but on the server end, assuming everyone is running their own, private copy of the front end.

[Tango1]

Yes, the BE seems to go into an unresponsive state which can be fixed by a Comp & Repair.

I'm going to pursue the suggestion to export the BE to a clean database in case there is something like an index that is not being properly rebuilt by a C&R.

I'm also trying to get a better description of the error rather than "it's something like the database is not available".

I'm also going to try and emulate the problem by deliberately crashing a mapped drive to see what errors arise. Partly because the consensus of opinion here is it will be an Access problem and not a network problem. Partly to test my network logging mini-app.

I'll post again when I have some results.

[SwissProgrammer]

Tango1,

If you can mirror the entire user's system and test it at your place with your techs error checking one step at a time, you might find (some of) the problem(s). Then when you have found at least some of exactly what causes exactly which problem, post it here.

Your biggest problem at this time might be asking non-tech to tell you what technically is happening. Try to not put any further technical the burden on them as that might strain your contract relation and future reputation.

If you have to, get permission to go to the customer, with their (totally the customer) paying the travel and the Per Diem for the entire process. Get their written permission to set up a mirror site at their location (for security) and have your techs error test it and analyze it there. Include in the price the customer paying for a fully usable mirror (servers, hardware, cabling, etc.) at their site. Contract to leave it there when you leave so that if something else happens later it is there for you to use. The customer should pay for all of this. You should not pay for any of this. Then when you have found at least some of exactly what causes exactly which problem, post it here. Tell your customer to expect the length of stay to be 4 times what you expect it to be; because estimates by management are commonly less than 1/2 of what the techs advise management might be needed, and because it is also common for it to take at least twice as long as the techs think it will to fix a problem.

That is how I used to do this. Our competitors sent their problem customers to us. Our customers commonly were satisfied. I suggest that you get full control of this fast and decisively before you chance losing your contract and your reputation.

Take as many of your techs as you think that you "might" need.

Thank you for asking.

[isladogs]

Hi TANGO1
I suggested decompiling as it seemed that corruption was in the FE.
Obviously there is no point decompiling a BE as it will have no code

[Tango1]

Hi Guys
Thanks for your help so far but I fear we have an unsolvable issue here, probably caused by Microsoft Updates. Will probably need to rename this thread!

My user finally sent a screen shot of the error message and it was "Unrecognised Database format" . An internet search shows many other instances of this problem and Microsoft have suggested a registry hack on the server, to DisableLeasing.
https://support.microsoft.com/en-us/...rs=en-us&ad=us

I've obviously contacted the IT department to see if they can investigate this, because some other feedback suggests the network is slowed down as a result .....

Looking back, it was in November last year that MS put out a security update that effectively disabled the 'Update' syntax in an SQL command (kb 4484119) That took them 2 months to fix. So it's not unknown that changes outside of Access can impact on Access.

However, I fear that the current error can have multiple causes as some issues (on the internet) go back to 2018 (not just recent changes).

In the above link MS seem to think the issue is related to using DAO to open a database using VB code. I do this a lot because I started building systems in Access 97. I'm going to review my code and see what is involved in translating to ADO, as Plan B if all else fails.

Thanks again for your help everyone.

[twinnyfo]

Tango,

The "Unrecognized Database format" error occurs when the back end data has been corrupted. This can typically be cured by grabbing the back end data files, opening them natively, and then compacting and repairing them. This is a (somewhat) common occurrence with Access DB Files. Sometimes there is more to the story, which would require a rebuild of the data files and importing all the data. That is a more serious step, but hopefully this can solve your immediate problem.

Hope this hepps!

[SwissProgrammer]

twinnyfo,

That sounds labor intensive.

Can your suggestion of "This can typically be cured by grabbing the back end data files, opening them natively, and then compacting and repairing them." be done with a Command Line Interface batch file? Or maybe be done with some type of a script file? Or maybe by subclassing Access and run a custom executable on it?

If so, then maybe periodically the process could be run on a schedule.

[twinnyfo]

SP,

Programatically, yes:

Option Compare Database
Option Explicit
 
Public Function _
    CompactDB( _
        strSrc As String, _
        strDest As String) _
    As Boolean
On Error GoTo EH
 
    CompactDB = Application.CompactRepair(SourceFile:=strSrc, _
                                          DestinationFile:=strDest, _
                                          LogFile:=False)
 
    Exit Function
EH:
    CompactDB = False
    Exit Function
End Function

Depending upon your network security protocols, this may require monitoring during execution. The theory here (to make sure you don't lose any data) is to follow these steps:

1. Make a copy of your BE
2. Compact your BE to a new file name
3. Rename that file to overwrite the original BE

When I have corrupt data, I prefer to grab the BE and save it locally and perform manual compact and repair--simply because my local machine can do this much faster than trying to do it over a network (or even over PVN which is horrendously slow for us).

"In theory" this should help alleviate some of the problems. But, I can't guarantee anything. :-)

Hope this hepps!