QUESTION POSED ON: 02 February 2007
I developed an application using VB.NET 2003 and MS Access XP. This app is running in low config machines. It is a standalone application. This app is running in five different places and so I have given place code in the primary key field to distinguish records and facilitate merging into one database.
In the second level of development, the user will enter two dates and the related records will be written in a temporary database that has the exact structure as of its original MS Access database. We call this Buffer Database. As the user enters two dates and presses the EXPORT button, old records of Buffer Database will get erased and the resulting records between entered two dates will fill the Buffer Database. This Buffer Database will be copied onto pen-drive or CD and will be brought to the main office where all data is to be merged. Every place will send its own Buffer Database on month-end, and the data will be merged on the main office computer.
The main office computer is having Oracle 10g Express Edition installed. Initially we want to test this with the Express Edition. Later we can move to the Standard Edition. The VB.NET program installed on the main office computer will read records table-by-table and insert into the Oracle database that also has the same structure as of the original Access and Buffer Database. Here we are facing problems.
Firstly, we need to check each row of a table to be inserted in the Oracle database to ensure that unique record is being inserted. Secondly, we also need to ensure that any existing record, if it might have changed, then updating is to be done in the Oracle database.
We tried available migration tools, but they will migrate whole for the first time but will not track existing records. And there I cannot find any synchronization tool for Access-Oracle. Though a few are available for Access-MySQL.
Please suggest some tips on how to effectively merge records in the Oracle database so that the database maintains integrity.
|
