Large .accdb gets corrupted (2 Viewers)

[JohnPapa]

Cloud? That's a significant issue. Is there a chance that any part of the affected DB is trying to run from a cloud server?

Cloud? That's a significant issue. Is there a chance that any part of the affected DB is trying to run from a cloud server?

The db is local, the copy (backup) is copied to the cloud, basically OneDrive

 

[The_Doc_Man]

OK, as long as no active part of the DB (FE or BE) is in the cloud, it's OK. I.e. backup copies going to a folder that gets copied to the cloud is OK.

 

[JohnPapa]

OK, as long as no active part of the DB (FE or BE) is in the cloud, it's OK. I.e. backup copies going to a folder that gets copied to the cloud is OK.

Only a copy of the .accdb is copied

 

[amorosik]

A client's .accdb gets corrupted every couple of months. The size of the db is around 300MB and the network is around 15 pcs. It is a dental practice management db. On the same server there two other dental .accdb with the same structure but much smaller, maybe 30MB and 70DB. Never had a problem with the latter.
When the corruption occurs, I can compact and repair the db, I can enter the FE (.accde), but some functionality is lost.
I have copies of the non-corrupted db and the corrupted db. Are you aware of a way to find out where are the differences, so that I maybe find out the reason and how to fix it?
It does not help that maybe 8 of the pcs are 4th generation (2013) i3.
Is there a tool, apart from writing vba, to compare the two dbs.

The question isn't entirely clear, because if you want to perform a comparison between db1 and db2, it's not possible if db1 is corrupt
Conversely, if we assume db1 and db2 are accessible, then simply loop through all the tables, scrolling through all the rows for each table, and compare the two databases

In my experience, the truly surprising thing isn't that a 300 MB Access database gets corrupted periodically, but that it can be worked on from 15 workstations without causing problems

Data archive corruption in an Access file is a known occurrence, even though many use this system. In my opinion, it's essential to have the data stored by software that does the job properly

You have to keep in mind that if it happens once, it's also justifiable to the user
If it happens twice, then suspicions begin to arise
If it happens three times, the loss of trust on the part of the customer/user could prove problematic.

 

[amorosik]

I ended up doing a backup of the .accdb every 2 hours, which minimizes the data loss

Only a copy of the .accdb is copied

How do you copy the backend file if some user is using the program?

 

[JohnPapa]

The question isn't entirely clear, because if you want to perform a comparison between db1 and db2, it's not possible if db1 is corrupt
Conversely, if we assume db1 and db2 are accessible, then simply loop through all the tables, scrolling through all the rows for each table, and compare the two databases

In my experience, the truly surprising thing isn't that a 300 MB Access database gets corrupted periodically, but that it can be worked on from 15 workstations without causing problems

Data archive corruption in an Access file is a known occurrence, even though many use this system. In my opinion, it's essential to have the data stored by software that does the job properly

You have to keep in mind that if it happens once, it's also justifiable to the user
If it happens twice, then suspicions begin to arise
If it happens three times, the loss of trust on the part of the customer/user could prove problematic.

I agree with most of your analysis. We also have a vb.net cloud version of the software on Azure.

The specific 300MB db is the result of a dentist who has been a client for about 20 years and grew from 1 to 15 dentists. We have specific db on about 150 other locations with no problem. Some the dbs are over 100MB, but do not have more than 6-7 users.

 

[JohnPapa]

How do you copy the backend file if some user is using the program?

I use a .bat file to first create a copy of the .accdb and then I copy the copied file to OneDrive with a timestamp. I include the .bat file below, which runs with Task Scheduler

@echo off

for /f "tokens=1-4 delims=,: " %%i in ("%time%") do (
set hour=%%i
set minute=%%j
set second=%%k
set hundredth=%%l
)


for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set month=%%i
set day=%%j
set year=%%k
set week=%%l
)

copy "V:\vd5_be.accdb"   "V:\vd5_be-copy.accdb"

"C:\Program Files (x86)\7-Zip\7z.exe" a -tzip "c:\junkvd\%Year%%day%%month%-%hour%%minute%%second%-vd5_be.zip"   "V:\vd5_be-copy.accdb"

 

[amorosik]

I use a .bat file to first create a copy of the .accdb...

....copy "V:\vd5_be.accdb" "V:\vd5_be-copy.accdb"

Have you ever thought that copying WHILE using the data database could also create problems?
I don't see any way to do this on locked files
Try modifying the copy procedure using Windows VSS
And in any case, using an ACCDB file shared on the network is always a major source of hassle, in my experience.

 

[DaveMD]

You have to keep in mind that if it happens once, it's also justifiable to the user
If it happens twice, then suspicions begin to arise
If it happens three times, the loss of trust on the part of the customer/user could prove problematic.

This is very accurate. There are only so many times that you can point to some network blip (or other intermittent event) causing corruption to your users, even if it is the reason. Sometimes the users' environment (slow network, use of WiFi) is what it is and beyond your control. It will result in users losing confidence in the integrity of the application if corruption continues to occur. I'm not suggesting you must go down this path, but it could be time to consider moving your BE to SQL Server. In over 10 years of using SQL Server with MS Access FE's, I have had zero instances of data corruption in the BE. There will be some learning curve if you don't have experience with SQL Server, but can be surprising how quickly it can be to pick up the basics to get your application converted.

 

[JohnPapa]

This is very accurate. There are only so many times that you can point to some network blip (or other intermittent event) causing corruption to your users, even if it is the reason. Sometimes the users' environment (slow network, use of WiFi) is what it is and beyond your control. It will result in users losing confidence in the integrity of the application if corruption continues to occur. I'm not suggesting you must go down this path, but it could be time to consider moving your BE to SQL Server. In over 10 years of using SQL Server with MS Access FE's, I have had zero instances of data corruption in the BE. There will be some learning curve if you don't have experience with SQL Server, but can be surprising how quickly it can be to pick up the basics to get your application converted.

As I mentioned, we also have a VB.net version of the software, which uses SQL Server on Azure. We have moved the .accdb data to SQL server for many of our customers and were able to move most of the data.

Changing the Access FE which works with .accdb, so that it works with SQL Server, would require a rewrite of the FE and would take many, many, many months. Not worth it for one customer.

 

[JohnPapa]

Have you ever thought that copying WHILE using the data database could also create problems?
I don't see any way to do this on locked files
Try modifying the copy procedure using Windows VSS
And in any case, using an ACCDB file shared on the network is always a major source of hassle, in my experience.

I never used VSS. In a .bat file, it would translate to something like the following. Will try it out

set source="C:\SourceFolder\MyDatabase.accdb"
set destination="C:\DestinationFolder\MyDatabase_Copy.accdb"

REM Copy the file (overwrite if exists)
copy %source% %destination% /Y

Edit: Just tried it and will replace the copy with VSS. Many thanks.

 

[JohnPapa]

Also. I read that Access Runtimes may cause problems. Never had a problem with A2013 Runtime, but It appears that there may be a problem with later versions. Would replacing the runtime with Access help?

 

[Cotswold]

Flaky networks, or WiFi, or users switching off with numerous copies open are never helpful. Even switching off the PC with one copy open I always advised against. Beware your client's hardware support company with some smart Alec who decides that any additional users can all run the same FE and ignores your instructions.
Write a utility to locate corrupted, or "slipped" records (where parts of one record appear in the next, or previous record). Include a feature to correct the corruption correctly. Bear in mind that removed corrupted records will be lost data that can prevent other records from opening due to relationships. After running the utility compact and repair. Make 100% sure that when running backups and C&Rs no other copies are opened.

(@JohnPape : I have never suspected Runtime of causing problems. All of my systems were in Runtime because clients' could have varying versions of Access on their PCs. I only issued software in one version with matching Runtime. Corruption was never universal but only experienced on odd sites from time to time. With hundreds of others never experiencing corruption.)

 

[Martin_Matten]

As I mentioned, we also have a VB.net version of the software, which uses SQL Server on Azure. We have moved the .accdb data to SQL server for many of our customers and were able to move most of the data.

Changing the Access FE which works with .accdb, so that it works with SQL Server, would require a rewrite of the FE and would take many, many, many months. Not worth it for one customer.

It looks as an important customer anyhow, and dealing with data problems all the time will take more time than converting the system in a decent way. It is possible without months of work, if you have some experience with SQL Server. I started with ADP projects in 2000, and it is a bit of a shame that ADP has been abandoned by Microsoft afterwards. Now I've converted my ERP app into ACCDB, and it has more overhead to work with SQL Server. But it works.
As a large client, I would lose confidence in a failing system and contact colleagues with a working system.

 

[JohnPapa]

Flaky networks, or WiFi, or users switching off with numerous copies open are never helpful. Even switching off the PC with one copy open I always advised against. Beware your client's hardware support company with some smart Alec who decides that any additional users can all run the same FE and ignores your instructions.
Write a utility to locate corrupted, or "slipped" records (where parts of one record appear in the next, or previous record). Include a feature to correct the corruption correctly. Bear in mind that removed corrupted records will be lost data that can prevent other records from opening due to relationships. After running the utility compact and repair. Make 100% sure that when running backups and C&Rs no other copies are opened.

(@JohnPape : I have never suspected Runtime of causing problems. All of my systems were in Runtime because clients' could have varying versions of Access on their PCs. I only issued software in one version with matching Runtime. Corruption was never universal but only experienced on odd sites from time to time. With hundreds of others never experiencing corruption.)

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

 

[JohnPapa]

It looks as an important customer anyhow, and dealing with data problems all the time will take more time than converting the system in a decent way. It is possible without months of work, if you have some experience with SQL Server. I started with ADP projects in 2000, and it is a bit of a shame that ADP has been abandoned by Microsoft afterwards. Now I've converted my ERP app into ACCDB, and it has more overhead to work with SQL Server. But it works.
As a large client, I would lose confidence in a failing system and contact colleagues with a working system.

We do have lots of experience with SQL Server. That is why I am saying that it will take a long time. I am just thinking of one of the forms that has 500 controls. Unfortunately, there isn't a one to one correspondence between .accdb and SQL Server.

 

[Martin_Matten]

... there isn't a one to one correspondence between .accdb and SQL Server???
You link the table as a server table and all the fields will have the same names and properties. The issue is mostly to make the tables updateable if you're using bound forms.

 

[JohnPapa]

Yes, most of the forms are bound

 

[Cotswold]

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

From my experience corrupted records are often referred to as containing 'hieroglyphics' by users. But the content is usually higher ASCII characters. As opposed to any possible keyboard entered characters ASCII values. Usually more than one field is affected, sometimes whole records with the original data being replated with rubbish. Access records aren't stored as they appear, or as you expect them to be. They are a bit more like a simple sequential file. Quite the reverse of an OFX file, which is often displayed as individual records but is actually one huge string.

A single Access record is a block of records of a given size. (without looking it up I can't be specific) So if you have a record with ten fields of 8 chars long there will be many grouped within one Access record. If you have large fields in a record then far less will be contained and fields may even span over one Access record to another. As an Access record is filled with your table fields, if there isn't room it will store the rest of the record's fields in another Access record. So when you have corruption it will invariably involve several of your table records. You will only see your data as individual records. But as I say the data appears to shift from on to another when corrupted and its content replaced.

Corruption will therefore often appear to shift your records so that (say) some of the content of #5678 slips into #5679 and makes it difficult to restore the original data. If you create a query for a table you will extract all fields where within the contents contain a character higher than say 200. Once you have that query you can view it in a form and decide what you want to do. I have never gone as far as attempting to restore data automatically and have fixed tables manually. Often by deleting but that will depend upon your application.

If completing that is not possible from what I've said, then say so and if I get the chance in the next day or so I'll look at my programs and see if I can extract the code so that it could be amended to suit other databases and upload it to Code Repository on the forum. But it is not complex. Complex is when you need to fix the corruption and original data. Make no mistake, if you don't remove all of the corruption, what remains will simply expand and create more at a later date.

Take a quick look. Make a copy of the corrupted table(s) and then open it scroll down. You should see where the data is corrupted. It is not always at the end of the table. Very often the corrupted data is old and the bad records can be easily deleted. But take care that you are not isolating child from parent, or vis versa. Once you can see the corruption you'll better understand the problem and solution.

 

[JohnPapa]

From my experience corrupted records are often referred to as containing 'hieroglyphics' by users. But the content is usually higher ASCII characters. As opposed to any possible keyboard entered characters ASCII values. Usually more than one field is affected, sometimes whole records with the original data being replated with rubbish. Access records aren't stored as they appear, or as you expect them to be. They are a bit more like a simple sequential file. Quite the reverse of an OFX file, which is often displayed as individual records but is actually one huge string.

A single Access record is a block of records of a given size. (without looking it up I can't be specific) So if you have a record with ten fields of 8 chars long there will be many grouped within one Access record. If you have large fields in a record then far less will be contained and fields may even span over one Access record to another. As an Access record is filled with your table fields, if there isn't room it will store the rest of the record's fields in another Access record. So when you have corruption it will invariably involve several of your table records. You will only see your data as individual records. But as I say the data appears to shift from on to another when corrupted and its content replaced.

Corruption will therefore often appear to shift your records so that (say) some of the content of #5678 slips into #5679 and makes it difficult to restore the original data. If you create a query for a table you will extract all fields where within the contents contain a character higher than say 200. Once you have that query you can view it in a form and decide what you want to do. I have never gone as far as attempting to restore data automatically and have fixed tables manually. Often by deleting but that will depend upon your application.

If completing that is not possible from what I've said, then say so and if I get the chance in the next day or so I'll look at my programs and see if I can extract the code so that it could be amended to suit other databases and upload it to Code Repository on the forum. But it is not complex. Complex is when you need to fix the corruption and original data. Make no mistake, if you don't remove all of the corruption, what remains will simply expand and create more at a later date.

Take a quick look. Make a copy of the corrupted table(s) and then open it scroll down. You should see where the data is corrupted. It is not always at the end of the table. Very often the corrupted data is old and the bad records can be easily deleted. But take care that you are not isolating child from parent, or vis versa. Once you can see the corruption you'll better understand the problem and solution.

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.