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.