The company is using an old Delphi software, that uses a .mdb file as database.
I can't change the way the application works, I don't have it's source.
We are facing many problems such as:
The lock problems are solved for now, currently, the problem is that only the first user that connects to the .mdb can edit, add and delete entries, the other users do that to, but the changes are not applied to the db, it seems it's only apply locally, on the user machine.
My question is, is .mdb supposed to work with many users over the network(3~4 people)?
What Is there anything I can do to make this software works well with the .mdb file, with many users?
Remembering that I cannot split the .mdb, as I cannot edit the Delphi application.
You don’t mention that if this in the past work fine in a multi-user environment. However, keep in mind that users MUST have full read/write AND ALSO file create and delete rights.
The reason for the create and delete rights is that because this is a “file based” system without a server process, then a locking file is created in the same directory as the back end mdb file. When the first user opens the file, and they don’t have FULL RIGHTS to the folder, then the file is opened in exclusive mode, and all additional users will be read only. This thus explains your symptom of first user in, but additional users don’t work. (the additional users will be read only – and obviously the Delphi software does not detect this problem). So you can view data – but no saving of data can occur.
So you need to ensure that all users have the ability to create that locking file. It is created by the first user, and any additional user opening the back end ALSO needs rights to that file (so don’t by mistake have permissions to the folder set on a per user basic, since then the first user in will cause JET to create the locking file but with permissions to that first user!. Once again this will mean additional users will be read only. So additional user MUST be able to open the locking file, and the first user in MUST be able to create the locking file. In fact open Access will open the database (mdb) as exclusive and prevent multi-user when the locking file cannot be created.
When the last user leaves the database (or in your case leaves the Delphi application), then the back end file is closed and ALSO THE locking file is deleted by the database engine (JET)
So this sounds like your IT folks did not give wide open permissions to the back end folder. You “can” run the database with file delete rights removed, but I much suggest that the locking file being re-cycled is also a good thing since over time it can become damaged etc.
So based on your symptoms, this is a VERY common occurrence in Access, and this problem is fixed by giving all users sufficient permissions to create files in the same folder as the back end. (and not permissions based on per user).
ALL users must be able to create + open + use that locking file created by the first user opening the mdb file. This locking file creation is automatic and managed by the JET database and not the Delphi program.