Large .accdb gets corrupted (3 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 cause problem. Never had a problem with A2013 Runtime, but It appears that there is a problem with later versions. Would replacing the runtime with Access help?