Solved Unrecognised Database Format (1 Viewer)

[kevnaff]

Hi there.

I have recently taken over the management of my department's access database. I work in a Hospital and the database is used mainly to keep a record of any medical equipment that we own and also any repair or service that is carried out on it.

Each user has a copy of the front end database on their computer. The backend data is stored on a shared drive which is frequently backed up by the Hospital's IT Team.

The last 3 weeks, when I have come in on Monday morning, a user has reported to me that they are seeing an unrecognised database format message. I have performed a compact and repair on the database and this has solved the issue. However, I'm worried that as this issue keeps occurring, that I may eventually have to revert back to a version before any corruption was occurring. This would mean reverting the database back to the middle of April, which will result in a big loss of data.

The database is not used over the weekend. However some users do stay logged in over the weekend, sometimes accidentally, other times just through old habits. I've read somewhere on the forums that having the database open over a long period of time, can cause an issue like this. Today, users had logged in to the database, but nobody had entered any data in to any tables, before receiving the message. However, one user had been logged in the whole weekend, and was able to navigate through the various forms without receiving any error message.

I was wondering whether anyone had any experience with an issue occurring frequently like this?

Also if anyone had any tips on how to handle a large loss of data? As I am able to repair and compact the database, would it be possible to export the data out of the repaired version in to an excel document, and then import it back in?

Any advice is much appreciated.

 

[arnelgp]

Import all objects to New database.
The peoblem with access db is it get easily corrupted.

 

[Ranman256]

Msoft has a bug with multiple users on the BE db on a network. (yes, thats the whole purpose of it)
Fix: get everyone out of their FE., open the BE, it will run repair, then all is good until it happens again.

This is an annoying bug.

 

[cheekybuddha]

See if anything here applies in your case.

 

[kevnaff]

Import all objects to New database.
The peoblem with access db is it get easily corrupted.

Thanks for your reply.

If I import all objects in to a new database, will this be able to identify any corrupt elements?

Or do you mean import the objects so that I have a copy that I can import back in to the earlier (hopefully non-corrupt) version of the backend?

I hope I've understood you response.

 

[kevnaff]

Msoft has a bug with multiple users on the BE db on a network. (yes, thats the whole purpose of it)
Fix: get everyone out of their FE., open the BE, it will run repair, then all is good until it happens again.

This is an annoying bug.

Yes this is what I'm having to do at the moment. My fear is however, if this continues to occur, that when I am away on holiday or taking annual leave that the team may not be confident in doing this, although it is straight forward.

I am going to write up a quick manual to help if this occurs whilst I'm away.

 

[isladogs]

The article reference by @cheekybuddha is very detailed and may well avoid the need for you to write a manual.

Importing into a new database usually works without importing any corrupted items...but occasionally that isn't the case...depending on what is corrupted. In my experience, running a compact and repair will not solve this issue.

Another thing you can try, is decompiling. This often does solve the unrecognised format error, See Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption (fmsinc.com)

Good luck

 

[kevnaff]

I have gone through and exported all of the tables one-by-one in to a new access database.

Do I just replace and rename this new database with the backend MDB file that is shared between all of the users?

Thanks for your reply

 

[isladogs]

If it was your backend database, then get all users out of the FE, then replace the old BE with this one.
With luck, there won't be any need to relink tables but you may not be so fortunate.

However, if the database contained other objects apart from tables, all of those will also need to be transferred.
There is no need to do so one object at a time.. It is also faster/easier to import into the new database rather than export from the old database

I mentioned decompiling. That isn't relevant for a backend database with tables only as there is no code.
In my experience, it is usually the frontend database that gets corrupted with the unrecognised database format error...though others may have different experience on that matter

 

[conception_native_0123]

In my experience, it is usually the frontend database that gets corrupted with the unrecognised database format error...though others may have different experience on that matter

i've gotten it multiple times by attempting to open a previous year's file format (extension) with a newer version (bit different also possible) of access, and vice versa. this is the only time i've ever seen this error. I had no idea there was a recognized bug by MS.

 

[kevnaff]

i've gotten it multiple times by attempting to open a previous year's file format (extension) with a newer version (bit different also possible) of access, and vice versa. this is the only time i've ever seen this error. I had no idea there was a recognized bug by MS.

Thanks all for your help.

The issue has occurred again this morning. It is becoming a running theme that first thing in the morning or after a weekend, the error message is popping up. I have checked the user log, and the majority of users remained logged in overnight, which I've read somewhere that this can cause this issue. I am going to send a reminder email to ensure all users are logged out. I don't want to take any further steps until I can guarantee that this is definitely not causing the issue. The backend is located on a shared drive/server that is controlled and updated by our IT team, and lately they have been running plenty of updates on the network, usually overnight.

Is there an easy way to check the extension that I am using and what extension I should be using?

The only thing I can see is that the extension is a .mdb and I assume that it is running using Access 2010, as the rest of the MS Office applications are all 2010.

 

[conception_native_0123]

Is there an easy way to check the extension that I am using and what extension I should be using?

The only thing I can see is that the extension is a .mdb and I assume that it is running using Access 2010, as the rest of the MS Office applications are all 2010.

I would seriously doubt that the updates being run by your IT team has anything to do with it. in my experience, this has everything to do with the internal formatting of the actual file. now, whether MDB vs ACCDB is relevant, that is probably yet to be seen. but, it is known that newer versions of access that create ACCDBs by default extension have many more features included in them than MDBs would, if they were created by older versions of access. to me, the bottom line here is that there really is no way to know what the offending op is because you have so many people involved and different file extensions available.

what do you know about when the initial files were created back whenever that was? my personal guess is, just like everyone else here, that the only way to hopefully guarantee you get rid of the error, if indeed it is not being caused by your IT team, is to export all objects from the current files into brand new files (fe and be) and make sure ALL extensions are ACCDBs. furthermore, make sure ALL files who have those extensions are created brand new by the same version of access, and that all people using the files are using the same version of access.

I would assume that would solve the issue for good, if anything will. but that's just one man's opinion.

 

[kevnaff]

I would seriously doubt that the updates being run by your IT team has anything to do with it. in my experience, this has everything to do with the internal formatting of the actual file. now, whether MDB vs ACCDB is relevant, that is probably yet to be seen. but, it is known that newer versions of access that create ACCDBs by default extension have many more features included in them than MDBs would, if they were created by older versions of access. to me, the bottom line here is that there really is no way to know what the offending op is because you have so many people involved and different file extensions available.

what do you know about when the initial files were created back whenever that was? my personal guess is, just like everyone else here, that the only way to hopefully guarantee you get rid of the error, if indeed it is not being caused by your IT team, is to export all objects from the current files into brand new files (fe and be) and make sure ALL extensions are ACCDBs. furthermore, make sure ALL files who have those extensions are created brand new by the same version of access, and that all people using the files are using the same version of access.

I would assume that would solve the issue for good, if anything will. but that's just one man's opinion.

Thanks for your help.

If the issue arises again then I will follow your advise and make all extensions ACCDBs.

Is there a simple way to export all objects in to a new file?

 

[Extra_Cover]

Thanks all for your help.

The issue has occurred again this morning. It is becoming a running theme that first thing in the morning or after a weekend, the error message is popping up. I have checked the user log, and the majority of users remained logged in overnight, which I've read somewhere that this can cause this issue. I am going to send a reminder email to ensure all users are logged out. I don't want to take any further steps until I can guarantee that this is definitely not causing the issue. The backend is located on a shared drive/server that is controlled and updated by our IT team, and lately they have been running plenty of updates on the network, usually overnight.

Is there an easy way to check the extension that I am using and what extension I should be using?

The only thing I can see is that the extension is a .mdb and I assume that it is running using Access 2010, as the rest of the MS Office applications are all 2010.

Are you running Windows 10? I had a similar problem but running this fix on the server solved it.

Access reports that databases are in an 'inconsistent state' - Microsoft Support

support.microsoft.com

 

[conception_native_0123]

Is there a simple way to export all objects in to a new file?

of course. create a brand new file, and under the "external data" > "new data source" menu, select an access database and the rest should be hunky dory from there. see image below. but my advice doesn't mean it is a catch all, and remember, the file extension is probably irrelevant. what I did say is that the internal formatting of objects in the file itself is what I have found to be offending, not the file extension. don't forget to try what extra_cover posted too.

 

[kevnaff]

Are you running Windows 10? I had a similar problem but running this fix on the server solved it.

Access reports that databases are in an 'inconsistent state' - Microsoft Support

support.microsoft.com

Thanks for your reply.

Unfortunately I don't have the admin rights to perform this fix so easily. Getting our IT team involved will likely take a few weeks for any action. I will get this reported to them and request for them to perform this workaround. I will try a few other things in the meantime.

 

[kevnaff]

of course. create a brand new file, and under the "external data" > "new data source" menu, select an access database and the rest should be hunky dory from there. see image below. but my advice doesn't mean it is a catch all, and remember, the file extension is probably irrelevant. what I did say is that the internal formatting of objects in the file itself is what I have found to be offending, not the file extension. don't forget to try what extra_cover posted too.

View attachment 91653

Sorry, I don't understand exactly what you mean by internal formatting?

I have updated the Front End a couple of times this year to include new features, do you mean the formatting in the code in the front end could be causing the issue? In the past this unrecognised database format error has occurred pretty quickly after a new update going out, and usually I would revert the update back to the previous version which is backed up regularly on the server throughout the day, therefore usually only losing a days worth of data, which people can usually input back in to the database.

In this case since the last update I put out, the error did not occur straight away. Instead it has occurred pretty regularly from around 3 days after the update went out. I am now in a position where If I was to revert back to the previous front end and distribute this out to users (from the middle of April), I believe this would lose all of the data since then.

Would a workaround for this be, to export all tables in to a new database like you've advised above. Then revert back the front end for everybody. Then put in place the new database with all of the copied tables?

 

[conception_native_0123]

well.....just a few things first:

Sorry, I don't understand exactly what you mean by internal formatting?

what I mean is that the "architecture" or "structural layout" of new files changed between 2007, 2010, 2016 versions etc... for instance, the introduction of split forms and data macros years ago. so, in essence what I mean are the types of objects being used in a file and what version of access is being used to try and open, read, and use them. but that's just one problem I've noticed. there are probably many others. I think the overall answer here is that no one really fully understands why this error occurs and it can probably happen for more than one reason - outside of the office bug that microsoft has acknowledged.

also,

I am now in a position where If I was to revert back to the previous front end and distribute this out to users (from the middle of April), I believe this would lose all of the data since then.

re-distributing a front end should not lose any data whatsoever because tables should not be in a front end.

Would a workaround for this be, to export all tables in to a new database like you've advised above. Then revert back the front end for everybody. Then put in place the new database with all of the copied tables?

what I suggested was to create new files for everyone. everything new, doesn't matter who is using it or weather it is front or back end. all files brand new and make sure everyone is using the same version of access to run your application. that's only me though. I have never seen anyone else here make that suggestion but that's what I have always done. That way, you guarantee yourself minimal inconsistencies with regard to pretty much anything that should be off base. Does that make sense?

and no, I do not think there is a workaround. I think you have to go all in to try and fix it. although it is your app, not ours.

 

[TonyE]

FYI I utilise a 3rd party bit of software to auto backup and compact all my databases on a regular basis i.e. overnight.
Software is Total Visual Agent which can be found at https://www.fmsinc.com/, works really well .... set it and just check it every so often.

 

[RogerCooper]

I used to have frequent problems with database corruption, which ended with a major office update last year. So the usual advice applies, make sure everyone is running the latest version of Access and update the old MDB files..