Incompatibilities between Access 2016 and O365 lock files ?

[Tango1]

Hi Guys
One of my clients is still having problem with an Access system breaking a couple of times a day with an 'Unrecognized database format' error.

I posted back in November suspecting it was an intermittent network causing the problem. Previously the app had been running without problems for more than 10 years. The backend is hosted on a server in the IT dept. It seems that most of the time there are only two users; the Laboratory and Stores. The lab has full Access 2016, I believe, but the Stores have been running Access from O365.

I may be grasping at straws, but has anyone else had a problem running Access in what might be called a 'mixed environment'?

I ask because I have recently found that the Lab user had seen an error say the lock file was corrupt, followed by request to Compact and Repair the database.

For the record; the system is all in .accdb format, all DAO code has been replaced by ADO and recordsets fully closed and set to Nothing when finished with, the system uses a mapped drive path rather than UNC (why change?)

The standard remedy is to compact and repair the backend for which I wrote a mini app that deletes the lock file, backs up the Db then does a C&R, which the user can run from the client end without stopping to call IT.

The current work around is to co-ordinate only one user at a time! Which is ridiculous.

[NeoPa]

I cannot deny categorically that mixing the versions might cause such issues but I can say I've not heard of it elsewhere. I'm in constant communication with most other Access MVPs so, again while I can't be categorical, I can be pretty confident.

You say the app's been running without issue for ten years, yet in the another sentence you describe some fundamental changes that have been made to it. I would suggest you start looking at what changes have been made and possibly going back to the version you know was working and adding the changes one by one until you discover where it starts going wrong.

I don't know the full details of your situation so I won't simply assume that is all possible, but you get the idea. My advice is to take that concept on board and to do what you can to determine which changes can be certain to have had no such effect. Those left are a smaller list to focus on.

Access uses DAO natively. There was talk some years back about recommending ADO for preference but that's all been turned on its head since. I suggest you use DAO where you can. There are things that DAO simply doesn't support (and vice versa I believe), but for most things you can use either. I recommend using the one (DAO) that's most native to the product, and therefore most likely to have more people using the same approach. The more there are the more likely any issues will come to light of course.

Good luck with your work. I know it can be frustrating at times when things are going wrong and it's out of your direct control.

In my (quite limited) experience of database corruptions they result from limitations within the network. ACE & Access have always been very susceptible to even extremely brief outages of the network.

[Tango1]

Thanks for your sympathetic response Ade (I would have posted on UKAUG but am no longer a member)

Perhaps I wasn't clear (trying to keep brief). I made the changes AFTER the issue arose because having browsed for the issue I came up with the following statement from Microsoft about databases in an inconsistent state.

https://support.microsoft.com/en-us/...rs=en-us&ad=us

They seemed to imply the problem only applied to DAO. (And there was no way I could follow the other suggestion and edit the registry on a customer's server!)

My initial suspicions had been 'network issues'. In fact my initial post was fishing for some advice on creating a network logging app! But I was given lots of good advice on C&R.

I'm now wondering if its a 32-bit versus 64-bit issue. I write using 32-bit because I want backward compatibility with mscomm32.ocx when reading directly from the serial port into the database. (Not in this instance) But I'm wondering if IT departments are now adopting O365 which is coming down as 64-bit flavour?

Should I rephrase my question "Incompatibilities in lock files created by Access 32-bit when accessed by Access 64-bit clients"?

[isladogs]

The default M365 installation is now 64-bit. Whilst it is still possible to install the 32-bit version, that requires a custom installation and is easy to overlook.

The unrecognised database format error may be caused by corruption, out of memory issues etc, etc...but it is also related to a long standing bug that AFAIK hasn't been fixed after more than two years.
See MS Access Bug Listing and more specifically. Access – Bug – Database is in an Unrecognized Format

[NeoPa]

Ah. I can see you have the advantage of me there. I know you only as Tango for now. Ah Jim. Hello (Moderator privileges ;-) ).

This seems like I have led you astray. Now you give me that I do remember this has been discussed. My bad not to have recognised this was what you were suffering from. Forget anything I said about it not coming up in discussions. It has.

I'm afraid I'm not very well up on this though. I don't know that I've heard that the bitness makes any difference. In fact, I would expect the OS to be the factor here rather than which version of Access is used. Again, don't quote me but as far as I recall this was a Windows issue that impacted Access rather than an Access issue per se.

I can see I'm not being overly helpful here so apologies. I do suspect though that your best bet is to get the client's server updated with the Disable-Leasing update. This needn't be a change you make directly. Perhaps talk to their IT and explain the situation and see if they can help you. Bear in mind it's only the server that the BE is stored on that will need the change.

One last point, again from limited recollections, but I don't remember the issue being limited to opening databases from the code and using DAO. Just opening them by using a linked table may well be enough. The ADO approach was a clever one but it may unfortunately be based on unreliable information. I can't swear to that of course, but I'd certainly allow for that not being the case explaining why your changes didn't produce your hoped-for result.

[isladogs]

Thanks Adrian for editing the links to make them more user friendly. I'm currently on a tablet and was intending to do the same later when on my desktop PC

[NeoPa]

Always a pleasure my friend :-)

I saw they were links to my 'colleague' Daniel Pinault's web site which, as he's an MVP, is perfectly fine to link to :-)

[Tango1]

Thank you both for your comments. I know how much time it consumes.

I can find very little information on lock files. It looks as though I'm going to have to resurrect some old kit and set myself a multi-user system with different versions of Access. I'll post any useful results.

Coincidentally, another legacy Access application (not mine) in a different branch of the same client has started to break similarly, although I don't have full details. (May be an opportunity to Upsize to MSSQL !)

A final point; I've always used linked tables with Access, even with unbound forms and SQL Server backends. It was only when I was re-writing sections into ADO (and some C# I was looking at) I realised it was possible to not maintain the links and just call the data and populate the forms as required.

Thanks for your considerations.

[twinnyfo]

I can't speak universally on this, but I have experienced some similar issues when users have differing versions of Access (even the same major platform, but with a different patch update). Here is my example.

We had one new user to the office, but they were issued an older PC which did not have the latest patches of software. It could have been a hardware issue, as well, in which the hardware did not allow the latest updates. Either way, whenever this user logged into our DB, all was fine. Except, when they navigated to one particular form which used a table that had fields with Rich Text (this was the only difference in any of the forms that I could identify). When she did that, her DB would crash. However, even though everyone else could previously use the DB with no issues, after that point, every one else who tried to access the DB received an unrecognized DB format error.

So, that makes one think that it is an issue with the BE right? So, I open the BE, compact and repair. Still, no one can access it. Strange!

Then, I open the uncompiled FE, recompile and publish it, relinking all the tables and all works fine. Even stranger!

In the past, I have had issues with new versions of Access, even new updates and patches. In our experience (in an office of fewer than 10 users), these issues have been mitigated by 1) all users having the same exact hardware and 2) all users operating with the exact same version of Windows/Access.

It "shouldn't" have to be that way, but it has been successful for us. Some of these issues are outside your ability to control in your environment.

Not sure that hepps!

[Tango1]

Hi Twinnyfo
Thanks for your message.
Food for thought! I would certainly like everything to be at the same revision level. It used to be in 'the old days' but with Microsoft Updates to Access and the OS, its difficult to know. Were you impacted by the 'Query is Corrupt' fiasco caused by a Security Update in Oct 2019?

https://support.microsoft.com/en-us/...rs=en-us&ad=us

Sorry, I digress.
I haven't decompiled the FE on the Live system but have asked the user to re-install from the distribution copy. And subsequently re-issued the s/w with some 'grasping at straw' mods. (i.e. DAO to ADO conversion). So I'm happy with the FE.

Doing C&R on the BE fixes the problem, ... temporarily. Fortunately, no data is lost. The user can't easily navigate to the BE hence I did the mini-app in Access to run a C&R on the BE, from a desktop icon.

I think I said above, that I have recently been advised that there is (occasionally? it may be missed by the user) a message say the Lock file is corrupt, before the system crashes. (This is unpredictable and I have not witnessed it first hand.) Which has lead me to inquire about lock files and whether different structure or protocols are used by different versions of Access.

With only one user at a time the system seems to be stable.

[twinnyfo]

Yes - I was impacted significantly by the Query fiasco. I was able to reverse engineer my queries to function properly in a day or two.

It seems like between Microsoft and our Network Nincompoops, we spend more time reacting to updates and putting out fires than actually developing our projects........................................