Read-only Access data source

Question

We have a major VB6 trading application which uses MS Access (Don't ask!) It is always blasting trades into an MS Access database.

The rest of the infrastructure here has moved on considerably and I want to read this Access database periodically and copy any new trades into a SQL server database.

The SQL and C# needed to do this is trivially easy.

BUT I want to make sure I do it in such a way that does not lock the Access database or cause problems for the VB6 app. In other words when populating my DataTable from Access I do NOT want to lock the database and prevent the VB6 app writing to it. I seem to remember from old ADO there were share modes you could use for this purpose.

What sort of connection string should I use from .NET to accomplish this?

Answer 1

Just an idea ... but have you thought about upgrading the database so that the tables themselves are actually stored in SQL Server and Access just become the front end? It's been a little while, but if I remember correctly, Access should have a wizard (yuck!) to help you do this.

With old ADO you could set the cursor or lock type mechanism to one of the following:

Cursor Type

adOpenForwardOnly This type of cursor can only be used to move forward through the recordset. This option is used when a list box or combo box is to be populated.

adOpenKeyset This is the best type of cursor to use when we expect a large recordset because we are not informed when changes are made to data that can affect our recordset.

adOpenDynamic This cursor allows us to see all the changes made by other users that affect our recordset. It is the most powerful type of cursor but the slowest one.

adOpenStatic The static cursor is useful when we have a small recordset.

Lock Type

adLockReadonly This lock mode is used when no additions, updates or deletions are allowed from recordset

adLockPesimistic In pessimistic locking, the record is locked as soon as editing begins and remains locked until editing is completed.

adLockOptimistic This occurs when the update method is called on the record. the record is unlocked even while edit but is temporarily locked when the changes are saved to the database

adLockBatchOptimistic This option allows us to perform optimistic locking when we are updating a batch of records

Not sure how that exactly translates into the .NET world ... but maybe this can get you pointed in the right direction.

Answer 2

The first user that connects to the database determines the locking mode used for the duration of that database being open.

Setting the mode in the connection string to "Read" should do the trick.

"Data Source=C:\IronSpeed\TestAccessDB\TestTypes.mdb;
Jet OLEDB:Database Locking Mode=1;
Mode=Read"

Some links that might help.

• A decent explanation of connection string settings
• Page-Level Locking vs. Record-Level Locking
• The OLEBD Provider

Answer 3

To build on Matt's answer, I would recommend a combination of adOpenForwardOnly and adLockReadonly: ForwardOnly because you just need to insert those trades into SQL Server, and Readonly so you aren't locking out other processes (what else would hit these tables?). Fortunately, these are the default options. :)

Answer 4

You have multiple solutions for that like opening read-only recordsets through ADO(OLEDB) connections. These recordsets can even be locally saved/archived as xml files for further use.

But the most trivial solution would be to make a copy of your original mdb file (which can be done at any time even if some of the tables are locked). You can then do whatever you want with the copied database, without any risk of interference with the running VB6 application.