Split DB not updating record then unrecognized format (1 Viewer)

Morphies

Member
Local time
Today, 08:26
Joined
Dec 8, 2021
Messages
30
Good morning everyone.

We have a large split database, where the backend resides on a nas and 3 - 4 users have the front end on the desktop.

The front end is version controlled so everyone is running the same front end.

We recently moved the file systems from a NAS to a Debian server with SAMBA shares - I don't see this causing any issues as the SMB2 drive maps are routine.

During the working day, we can have a couple of errors where the DB falls into an unrecognised format. The usual recover compact and repair works.

This morning, I was updating an existing record and my colleague next to me also had this record in view but was not editing it. I noticed that after I had updated the record and left the record that it had not updated in her view. I waited a few seconds then the unrecognised database format error occurred.

So the record set was not updated and something went wrong.

We have also had this issue where a record data was replaced with #'s - very dangerous.

The database has been in development for around 15 yeas, so most likely originally built in office 2003. We are now using a plethora of versions, 2017, 2019, 2021. 2021 is the most recent addition which coincided with the move to the new server.

Could this be an office version conflict? Or likely something else I'm missing?
 

bob fitz

AWF VIP
Local time
Today, 08:26
Joined
May 23, 2011
Messages
4,717
The front end is version controlled so everyone is running the same front end.
I believe that every user should have their own copy of the front end. Each front end should be linked to the back end.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:26
Joined
Sep 21, 2011
Messages
14,050
I believe that every user should have their own copy of the front end. Each front end should be linked to the back end.
I believe they do, from what the O/P stated Bob. Just that they all have the same version as well?
@Morphies The NAS would have had SMB shares as well would it not?
At least mine does?
 

Morphies

Member
Local time
Today, 08:26
Joined
Dec 8, 2021
Messages
30
All users have the same version of the front end locally stored on their own desktops. This version is controlled so each time a user opens their FE the database checks the FE filename against a table in the BE and if different, runs a .bat file to copy down the latest edition.

A user cannot open the DB unless it is the latest FE.

Yes the nas had SMB, but for some reason I had SMB2 disabled. I'm not entirely sure why.

I have briefly tried updated the .mdb to an accdb file but this resulted in lots of object errors in the crude VB i have written over the years - so this is a larger peace of work in order to 'upgrade' the BE to accdb.

I 'could' bring a NAS back online and move the BE to that to check for server share issues if we think that's a possibility?

Users are currently running a mix of Office pro plus 2019 / 2021 AFAIK.
 

bob fitz

AWF VIP
Local time
Today, 08:26
Joined
May 23, 2011
Messages
4,717
I believe they do, from what the O/P stated Bob. Just that they all have the same version as well?
@Morphies The NAS would have had SMB shares as well would it not?
At least mine does?
Ahh....that's what comes of my commenting on something that I don't fully understand much less have any experience of.
I have only ever had multiple users on a wired peer to peer network, so when the OP said,".......so everyone is running the same front end" I took that to mean that only one front end existed.
My apologies if I have clouded the issue in any way.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:26
Joined
Sep 21, 2011
Messages
14,050
All users have the same version of the front end locally stored on their own desktops. This version is controlled so each time a user opens their FE the database checks the FE filename against a table in the BE and if different, runs a .bat file to copy down the latest edition.

A user cannot open the DB unless it is the latest FE.

Yes the nas had SMB, but for some reason I had SMB2 disabled. I'm not entirely sure why.

I have briefly tried updated the .mdb to an accdb file but this resulted in lots of object errors in the crude VB i have written over the years - so this is a larger peace of work in order to 'upgrade' the BE to accdb.

I 'could' bring a NAS back online and move the BE to that to check for server share issues if we think that's a possibility?

Users are currently running a mix of Office pro plus 2019 / 2021 AFAIK.
Well putting it back on the NAS would cerainly help I would have thought?
If all is well when back there, then you have pinpointed the issue. If still happening, have to investigate further.
I am assuming wired netork for all? Where was the NAS box in relation to the Debian server, network wise?
 

Morphies

Member
Local time
Today, 08:26
Joined
Dec 8, 2021
Messages
30
Agreed putting it on the NAS would eliminate any possibility of the debian server causing the issue. I will try this.

The NAS was taken out of commission when we opened a new site up. Local machines to the server are all on GB Lan. VPN trunk to site 2 but the DB is only ever run remotely via RDC on windows VM's on the debian server. So essentially everything is local and GB Lan.
 

Morphies

Member
Local time
Today, 08:26
Joined
Dec 8, 2021
Messages
30
Ahh....that's what comes of my commenting on something that I don't fully understand much less have any experience of.
I have only ever had multiple users on a wired peer to peer network, so when the OP said,".......so everyone is running the same front end" I took that to mean that only one front end existed.
My apologies if I have clouded the issue in any way.
No problem, appreciate you reading the thread and contributing.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 28, 2001
Messages
27,001
Only as a comment in passing regarding the disabling of SMB2 protocol (but leaving SMB(1) available): For a while there was a known bug with SMB2 having to do with "disk buffer reservation" that could transparently defer disk write-backs but that fact wouldn't be caught by user programs that didn't fully appreciate the situation. That "disable SMB2" trick was the suggested workaround for the corruption that occurred because of that reservation that improperly deferred data writebacks. You didn't say which version of Windows and of Access you are running, so older versions might have tripped over the SMB2 problem. What is interesting is that you didn't mention SMB3, which is out now for Win10, so that's why I mentioned "older versions."
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 19, 2002
Messages
42,981
Updates made by others are not immediately visible. Access refreshes an open form periodically and when the form refreshes, the user will see the updates.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 28, 2001
Messages
27,001
This morning, I was updating an existing record and my colleague next to me also had this record in view but was not editing it. I noticed that after I had updated the record and left the record that it had not updated in her view. I waited a few seconds then the unrecognised database format error occurred.

Looking more closely at the original discussion, my new question is whether the copies of the FE database files are set for pessimistic locking. I'll explain my thoughts here.

When you open a record in the back end, you open a disk block that contains the record. Access doesn't actually do record-level locking; it does disk-block-level locking. Depending on the record-locking style (None, Optimistic, Pessimistic), different effects occur when someone else opens the same block - or tries to.

This next part gets complex, so I'll simplify it. IF you have Pessimistic locking for some reason, you lock the block the moment you open it. If you have Optimistic locking, you merely establish interest in the block. (For those who wonder what I mean by "interest", think of OpenRecordset with dbSeeChanges.)

If two users with Pessimistic locks open the same block (which is exactly what was described), it might be possible to make a change in your local copy of the record buffer - because remember that Access brings things to the FE host machine for all work - but if neither one of the locks releases soon enough, you get "stale data" and that can cause a form of corruption. Of course, if you use "No Locks" then there ARE no locks. The default for most DB operations would be either NO LOCKS or OPTIMISTIC locks. But it would pay to verify the lock settings. There is also a "refresh interval" in the File >> Options >> Current Database settings that would determine how old data must be to count as "stale."
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 19, 2002
Messages
42,981
Has the FE ever been converted from an .mdb to an .accdb?

Is it possible that the network if faulty and dropping packets?
 

Users who are viewing this thread

Top Bottom