Read Only Data Access

Question

We have the main VB6 trading application that uses MS Access (do not ask!). These are always explosive deals in the MS Access database.

The rest of the infrastructure has made significant progress here, and I want to periodically read this Access database and copy any new transactions into the SQL server database.

The SQL and C # required for this are trivially simple.

BUT I want to make sure that I do this so as not to block the Access database or create problems for the VB6 application. In other words, when populating my DataTable from Access, I DO NOT want to lock the database and prevent the VB6 application from writing to it. I think I remember from the old ADO that you can use sharing modes for this purpose.

Which connection string should I use from .NET to accomplish this?

Answer 1

To build on Matt's answer, I would recommend a combination of adOpenForwardOnly and adLockReadonly: ForwardOnly, because you just need to paste these transactions into SQL Server and Readonly so that you don't block other processes (what else could affect these tables?). Fortunately, these are the default settings. :)

Answer 2

Just an idea ... but have you thought about updating the database so that the tables themselves are actually stored in SQL Server and Access just becomes an interface? It was a bit, but if I remember correctly, Access should have a wizard (yuck!) To help you with this.

ADO :

adOpenForwardOnly . , .

adOpenKeyset , , , , .

adOpenDynamic , , . , .

adOpenStatic , .

adLockReadonly , ,

adLockPesimistic , , .

adLockOptimistic , . , ,

adLockBatchOptimistic ,

, .NET... , , .

Answer 3

, , , .

"" .

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

, .

• OLEBD

Answer 4

, , ADO (OLEDB). / xml .

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). Then you can do whatever you want with the copied database, without any risk of interference with the running VB6 application.