Large .accdb gets corrupted (1 Viewer)

[The_Doc_Man]

Corruption in a native Access BE file is USUALLY caused by something that prevents MSAccess.EXE from completing a write-back of updated records. This can be caused by a power failure, a hardware failure, a network failure for split DBs, a software glitch in which a fault interrupts the middle of an update sequence, or a user brain-cramp of turning off the PC without shutting down Access in an orderly manner. Or using Task Manager to shut down a copy of Access that says "not responding." The power, hardware, and network failures are not user-caused, but the improper shut-downs are indeed a user action problem.

A little more investigation would be in order to see if anyone remembers any of the above causes, each of which has a different next action.

 

[tvanstiphout]

One step towards figuring out what is happening is to implement Crash Detection. It only takes a few lines of code here and there. The basic idea is:
1. AutoExec macro calls VBA function to start the app. As part of that the first hidden form is opened (frmHidden).
2. frmHidden ticks a timer which updates a registry location every minute with the current datetime (SaveSetting).
3. in Form_Unload for frmHidden, this registry entry is cleared. This indicates graceful shutdown.
4. In your startup code, check the registry entry. If there is a datetime there, that indicates that the app crashed the last time around, and the approximate datetime.

 

[Cotswold]

Many thanks for your comments. If you have something and is not a hassle please forward.

Meanwhile I just installed on the specific server (not actual server, merely data storage) a proper backup software and have disabled the Task Scheduler events. If interested in the specific backup software, it is widely available and I can refer to it. This way if there is a ransomware virus, I can always go back to earlier version. Before, the backup was done on OneDrive, which had the extra risk of it getting corrupted.

Have you looked for and found your corruption toy view the table on-screen?

 

[JohnPapa]

Have you looked for and found your corruption toy view the table on-screen?

Can you please explain what you mean? Thanks.

 

[Cotswold]

Thanks. Can you please elaborate on what you mean by utility to locate corrupted or slipped records?

REPLY#2
---------
A bit more to it than I remembered.
You will need to check that one or more fields don't have an ACSII value of more than 128 using Queries. You can often choose just one field if it is an indexed or relational field that must exist. Maybe check that also check for the content of any numeric fields to ensure that it contains valid data. An adaptation of the following will make sure it is alpha/numeric in a Query, which may be sufficient:

IIf(([ASCTkt]>47 And [AscTkt]<58) Or ([AscTkt]>64 And [AscTkt]<91) Or ([AscTkt]>96 And [AscTkt]<123) Or [AscTkt]=32,1,0)
If the result is zero then it's Ok, if not then you can drop them into a filtered query and handle them.

Also, corruption can result in lost indexes, so you may need to check that they exist and recreate them if they don't.
Once you have extracted/isolated bad records you will need forms to display so that you may can select to edit or delete records. (delete is more normal because of "record slip" but you may need to add new ones) You will probably also need to display lost records, i.e. related records where their key has been corrupted and will therefore be unrelated parent/child. Make no mistake it is not a trivial exercise but worth it as just viewing tables to locate and fix corruption is often not helpful in locating a solution. It can also be very time consuming. The more your data is normalised maybe the more complex but it is usually not the standing data tables that are corrupt. Only the table(s) that are updated daily. Often only one in a database that is the problem.

If you have many different sites using the program it becomes quicker and easier with a utility to locate, delete and fix the corruption. It could be a weeks work or more by the time you have tested it all. Not trivial and will need care to get it right. When complete it is then quick and easy to fix the corruption.

Some operations in the ticketing system I developed one utility for are:

Create Lost Indexes, Locate a Missing Ticket, View/Print Lost Tickets, View & Delete Bad Tickets, View Unrelated Tickets to Customers, View Orphaned Tickets. There maybe more required in your application.
Good luck.
-------------------------------------------------------
[To #44 : ] See the last para in #39 which explains

 

[The_Doc_Man]

If you really wanted to assure that the character in ASCTkt is in the ASCII range, you don't need QUITE so many tests. Any ASC(single-character) that is <128 is potentially an ASCII character. However, if you were picky about punctuation, then you really DO need a few more tests. One method I used to use was to write a function that contained this kind of test to return a code.

SELECT CASE ASC(the-character)
    CASE 9, 10, 11, 12, 13
        (return code for carriage control action for HT, LF, VT, FF, CR)
   CASE 32, 48-57, 65-90, 97-122
        (return code for space, digits, alphabetic U/L case)
   CASE ELSE
        (return a code for bad character)
END SELECT

There is a much more extensive and complex variation of this in the code repository. It was originally written for a specific purpose and isn't the fastest possible code, but it has been tested a lot and It is pretty reliable. The code is a text-parsing tool as a class module. The download includes documentation in Word.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

 

[Cotswold]

The code I included was from a working model written maybe 20+ years ago but it is the code to be inserted into a query. I've never used SELECT/END in a query. But just <128 isn't good enough as I exclude []{}~ // and everything <32. It worked and was quick to filter corrupted records from huge tables. I tend to think that a query will be quicker than tripping through a table one record at a time but maybe not? Also, could that type of routine fall over when it hits corruption. A query never has a problem showing corruption in my experience, so I used them to reduce testing.
I am always reluctant to post code because there is never one way of doing anything, just one that suits, which is a good thing.