Error: Unrecognized Database

[evictme]

Hey all,

It has been a while. Here is my current issue:

I have a split database - Frontend and Backend. I also have a security file for the backend.
Front-end is .accdb
Backend is .mdb
and security is mdw

I have about 10-15 users, not including myself. Both, the backend and frontend files live on a share drive. Its a Qnap NAS with a Raid1 using 3 SSD drives for about 1TB of data.

I've set up a folder for each user with their own copy of the front-end file, each of which are linked to the 1 backend file. I've done it this way so it is easier to update all the user Front-ends without having to go to the individual PCs. So, when a user logs in they do so thru a shortcut on their desktop that opens MS access and their respective front-end file from the sharedrive.

Lately, we have been getting corruption on the back-end file. Users will report database disconnection and I will see the error - 3343 - Unrecognized database format '\\eaa-database\Share\BE\EAA-Backend.mdb'.

When this happens it also brings with it a few issues with our tables;

1. Some records become corrupted and display "####" instead of any values
2. Some of our key Tables will lose their Primary Key, sometimes.
3. having everyone disconnect from the backend file, I open the backend file in exclusive mode and sometimes it will say file needs to be repaired and sometimes it will just open the file.

I know that some of this relates to known MS access bugs - like the Autonumber bug, among others...

But it is happening so often, Im not sure where to even look anymore or what else I can do to mitigate the situation.

Our backend File is only about 120 mb and our front-end just a little more at about 180 mbs, so file sizes are not even close to MS Access limits.

We were working fine for years with this set up and now I have to rebuild/reconfigure the backend file almost everyday. Sometimes I repair and clean up the same file and sometimes I create a new file from scratch and import the tables from the old file.

Is there an issue with MS Access im not aware of? What can I do?

Any suggestions or leads i can look into?

 

[theDBguy]

Just curious, which version of Access are you using?

 

[evictme]

Just curious, which version of Access are you using?

MS Office Professional Plus 2019 (32-bit). Unfortunately, we have a mixed bag of user PCs with varying processing capabilities. I developed it on 32-bit and have kept it that way to ensure compatibility for all users.

 

[theDBguy]

MS Office Professional Plus 2019 (32-bit). Unfortunately, we have a mixed bag of user PCs with varying processing capabilities. I developed it on 32-bit and have kept it that way to ensure compatibility for all users.

Were any of your users still using 2010? If so, are they the ones having this problem?

 

[Pat Hartman]

1. Convert the BE to .accdb
2. As long as every user is using a separate copy of the FE, that is most important but there are simple distribution methods. You don't need to keep 15 copies on the server.

 

[evictme]

Were any of your users still using 2010? If so, are they the ones having this problem?

I believe the issues did start when we had users on older versions of office but I have since upgraded everyone to 2019.

 

[evictme]

1. Convert the BE to .accdb
2. As long as every user is using a separate copy of the FE, that is most important but there are simple distribution methods. You don't need to keep 15 copies on the server.

I will try this. I believe at one point, way in the beginning of all these issues (months now), I had found the BE file as .accdb the next day...despite not doing that conversion myself nor are there any users that would know how to do that or even where to look.

Can you suggest any better distribution methods based on my setup?

 

[Pat Hartman]

You can use a batch file (simplest) or a separate db that does the distribution. Both are described here in detail and you will find sample distribution dbs also.

Here is a simple .bat file

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Keep the .bat file on the server (so it is easy to change if you need to). Create a shortcut that opens the .bat file. Distribute the shortcut to each user. Then get rid of the extra FEs on the server.

It is best if you also change your FE to add a version table and add another version table to the BE. When the FE opens, it matches the version in the local FE table to the linked BE table. If they don't match, give the user a message to only use the shortcut to open the app and then close down.

The .bat file makes a directory in case this is a new user.
Deletes the existing FE in case this is an existing user
Copies a fresh copy of the FE to the local directory
Opens it.

There is no error checking. I've never bothered but you could add it if you need any. The first two statements do not raise errors if they fail so the only error would be that you can't find the server copy of the FE.

 

[evictme]

You can use a batch file (simplest) or a separate db that does the distribution. Both are described here in detail and you will find sample distribution dbs also.

Here is a simple .bat file

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Keep the .bat file on the server (so it is easy to change if you need to). Create a shortcut that opens the .bat file. Distribute the shortcut to each user. Then get rid of the extra FEs on the server.

It is best if you also change your FE to add a version table and add another version table to the BE. When the FE opens, it matches the version in the local FE table to the linked BE table. If they don't match, give the user a message to only use the shortcut to open the app and then close down.

The .bat file makes a directory in case this is a new user.
Deletes the existing FE in case this is an existing user
Copies a fresh copy of the FE to the local directory
Opens it.

There is no error checking. I've never bothered but you could add it if you need any. The first two statements do not raise errors if they fail so the only error would be that you can't find the server copy of the FE.

One of the issues we encountered was that users would go into local folders and open the file directly if they had any issues, bypassing security measures

1. Do you think having a local copy for all the users on their desktop might eliminate some of the issues we are seeing?
2. Could I make the .bat file open the file with the security file as well?
for Example, this is what I use for the user shortcut currently: "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "Sharedrive:\Users\User\Desktop\Database-Update\EAA-Database.accdb" /wrkgrp "\\EAA-DATABASE\share\BE\Security.mdw

 

[The_Doc_Man]

You ABSOLUTELY want each user to have a copy of the FE file on their individual desktops. You want this because of file-share locking issues. You see, if you open a file remotely, you use network communications to interact with the file locks, whereas if you have a local FE, all the file locks are on your local machine. That right there would be probably a 10-fold speed improvement in a Gigabit network environment and even more of a boost if the network is of the 100 Mbit variety. Everything that you can keep off of the shared server (or minimize touches to it) is to your advantage.

The .bat file surely can include whatever switches you wish to include.

 

[Pat Hartman]

One of the issues we encountered was that users would go into local folders and open the file directly if they had any issues, bypassing security measures

Try reading my comments again. They tell you how to solve this problem.

Yes, the .bat file can use the string with the Wrkgrp parameter.

HOWEVER, why? The .accdb does not support WorkGroup security. If you are worried about security of the data, you should convert to SQL Server.

 

[gemma-the-husky]

Is anyone connecting wirelessly to the servers? It's really not ideal and can increase the likelihood of corruption.
All these suggestions - separate front ends, wired only, running de database rather than db, etc etc all help to reduce the chance of corruption.
The Database design also helps.

This 3343 error was an unresolved issue a while back (I'm pretty sure no-one ever got to the bottom of it). Its worth making sure that all users have all system updates applied.

My pet theory was that the locking database ldb/laccdb file on the backend was trying to exceed 15Kb (more than 255 sessions) and then crashed. It's worth checking the size of that file. It holds 16 users for 1Kb, so 15Kb holds the maximum of 255 users.

 

[evictme]

Try reading my comments again. They tell you how to solve this problem.

Yes, the .bat file can use the string with the Wrkgrp parameter.

HOWEVER, why? The .accdb does not support WorkGroup security. If you are worried about security of the data, you should convert to SQL Server.

I did, i saw. What I meant by that comment was that in the past. But this is different. I think I can manage with this set up a little better.

Im using wrkgroup security because that is what weve had baked in since it's inception. Also, we have a strict auditing system that needs each user ONLY using their credentials. Im pretty sure I can do this with forms and vba but Im saving that for when I need to rebuild from scratch...which I think may be coming sooner than later.

Speaking of wrkgrp parameter/security; Should the MDW security file be a local file as well? Its currently set up to be shared by the users (it's in the same folder as the BE file).


UPDATE: I moved everyone over to their own LOCAL copies and it is smoothing sailing so far.

 

[Pat Hartman]

The .mdw MUST be in the folder with the database it is securing. Otherwise it cannot manage concurrent users.

Here's a simple version of security.

 

[evictme]

Is anyone connecting wirelessly to the servers? It's really not ideal and can increase the likelihood of corruption.
All these suggestions - separate front ends, wired only, running de database rather than db, etc etc all help to reduce the chance of corruption.
The Database design also helps.

This 3343 error was an unresolved issue a while back (I'm pretty sure no-one ever got to the bottom of it). Its worth making sure that all users have all system updates applied.

My pet theory was that the locking database ldb/laccdb file on the backend was trying to exceed 15Kb (more than 255 sessions) and then crashed. It's worth checking the size of that file. It holds 16 users for 1Kb, so 15Kb holds the maximum of 255 users.

No, no wireless connections. Everyone is networked directly in.

The database is running a design updated/added to/edited over 10+ years...I might just need to bite the bullet and start from scratch.

I think you may be onto something. After getting all users off the database. the only thing out of the ordinary was the ldb file for the Security file, deleting it requires a reboot of the NAS drive.

 

[gemma-the-husky]

if you cant delete the ldb file, then there IS definitely a session to the database that has not been closed. You might be able to see the active sessions in the administration panes

 

[The_Doc_Man]

HOWEVER, why? The .accdb does not support WorkGroup security. If you are worried about security of the data, you should convert to SQL Server.

The OP has pointed out that .MDW and .MDB are in use. So I looked it up. I found on-line MS documentation and 3rd party documentation that suggest that the user features would be honored in .MDB files even for Ac2019 and Ac365. Since I have never actually TRIED to do that and no longer have a version of Access that treats .MDB as the default format, I can't easily test it.

 

[Pat Hartman]

But without an interface to add/change the user's security, how can it be useful? Wouldn't you always have to work in the mdb and then convert to the .accdb to make FE changes?

 

[gemma-the-husky]

I do produce mdb's, but it's a long while since I used any intrinsic security, so as far as the database goes, all users are admins. I occasionally wondered why that was still there at all, so I guess it's a legacy thing.

@Pat Hartman
You can build/supply an mdb/mde in current versions of access, so you don't have to change between accdb and mdb. You can't include accdb features though in the mdb (I think). I am pretty sure the only thing I tried to do was use rich text boxes. In accdb they show as rich text. In mdb you just see the http (is that the right description?) control characters as plain text. Is that what you meant, or did I misunderstand?

 

[Eugene-LS]

Any suggestions or leads i can look into?

Sometimes helps that method :
01. Rename the corrupted database
02. Create a new (empty) database with the name of the old one
03. Import all objects from the old database to the new one
04. Check that everything has been imported and the relationships between tables are correct