Access .ldb file & multiple connection.

Question

I have an API which opens an access database for read and write. The API opens the connection when it's constructed and closes the connection when it's destructed. When the db is opened an .ldb file is created and when it closes it's removed (or disappears).

There are multiple applications using the API to read and write to the access db. I want to know:

1. Is ldb file used to track multiple connections
2. Does calling an db.close() closes all connections or just one instance.
3. Will there be any sync issues with the above approach.

Answer 1

db.Close() closes one connecton. The .ldb is automatically removed when all connections are closed.

Keep in mind that while Jet databases (i.e. Access) do support mutiple simultaneous users, they're not extremely well-suited for a very large concurrent user base; for one thing, they are easily corrupted when there are network issues. I'm actually dealing with that right now. If it comes to that, you will want to use a database server.

That said, I've used Jet databases in that way many times.

Not sure what you mean when you say "sync issues".

Answer 2

1. Yes, it's required to open database in shared mode by multiple users. Seems it stands for "Lock Database". See more info in MSDN: Introduction to .ldb files in Access 2000.
2. Close() closes only one connection, others are unaffected.
3. Yes, it's possible if you try to write records that another user has locked. However data will remain consistent, you will just receive error about write conflict.

Actually MS Access is not best solution for multi-connection usage scenario. You may take a look at SQL Server Compact which is light version of MS SQL Server. It runs in-process, supports multiple connections and multithreading, most of robust T-SQL features (excluding stored procs) etc.

Answer 3

As an additional note to otherwise good answers, I would strongly recommend keeping a connection to a dummy table open for the lifetime of the client application.

Closing connections too often and allowing the lock file to be created/deleted every time is a huge performance bottleneck and, in some cases of rapid access to the database, can actually cause queries and inserts to fail.

You can read a bit more in this answer I gave a while ago.

When it comes to performance and reliability, you can get quite a lot out of Access databases providing that you keep some things in mind:

• Keep a connection open to a dummy table for the duration of the life of the client (or at least use some timeout that would close the connection after like 20 seconds of inactivity if you don't want to keep it open all the time).

• Engineer your clients apps to properly close all connections (including the dummy one when i'ts time to do it), whatever happens (eg crash, user shutdown, etc).
  Leaving locks in place is not good, as it could mean that the client has left the database in an unknown state, and could increase the likelihood of corruption if other clients keep leaving stale locks.

• Compact and repair the database regularly. Make it a nightly task.
  This will ensure that the database is optimised, and that any stale data is removed and open locks properly closed.

• Good, stable network connectivity is paramount to data integrity for a file-based database: avoid WiFi like the plague.

• Have a way to kick out all clients from the database server itself.
  For instance, have a table with for instance a MaintenanceLock field that clients poll regularly. If the field is set, the client should disconnect, after giving an opportunity for the user to save his work.
  Similarly, when a client app starts, check this field in the database to allow or disallow the client to connect to it.
  Now, you can quick out clients at any time without having to go to each user and ask them to close the app. It's also very useful to ensure that no client left open at night are still connected to the database when you run Compact & Repair maintenance on it.