How do I handle MS Access transactions in a multi threaded application, and how do I syncronise MS Access and Oracle tables?

Question

I'm in a project to write a program using local database.

We use Delphi, MS Access and Oracle.

The issue are:

1. There are several threads needed to insert and update MS Access DB. I haven't test it now, and I know access have a multi-thread issue here. Whether I need to use a lock or put MS Access DB operation into on thread?
2. We need move same table from Oracle to MS Access, for performance considerations. (personally I'm not suggesting that, but I'm not the project manager). We still need to synchronize the data from MS Access to Oracle for a period of time (maybe several days). I have to do a lot of work on it, is there any easy way(we use BDE)?

Answer 1

Issue #1
I would consider migrating the MS Access portion to MS SQL (Oracle is possible too, but I'm not sure of the issues related to that). That would handle a lot of the multi threading/multi user issues that MS Access has.

I've had few issues converting other MS Access applications to MS SQL (front end stays in MS Access, and the tables are linked to the MS SQL DB through ODBC). So it's low pain, and you get speed/reliability too.

Issue #2
If there is something technological that forces you to use MS Access, the easiest thing to do would be to just create an ODBC link to the Oracle table through MS Access. That way you are only updating it one place. I also question the decision to move the table from Oracle to MS Access. 1) I'm more trusting of Oracle DBs right off the start and 2) MS Access/Jet is not known to be highly performing over Oracle. Add more memory to the Oracle server; give it a faster processor; etc.

Your tightest bottle neck is not going to be the database anyway. It's going to be your network. So you are moving the problem from one server to another and you are adding to the network congestion by having to synchronise the MS Access table with the Oracle table.