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

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. :)