Problem solve Get help with specific problems with your technologies, process and projects.

Merging records in Access and Oracle

I cannot find any synchronization tool for Access-Oracle. How can I merge records in the Oracle database so that the database maintains integrity?

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.

Part of the problem is that your source database and your destination database are two different platforms. For this type of data transfer, I look towards one of two avenues. One, using Oracle's Transparent Gateways to move the data into Oracle from a non-Oracle data source. Two, leverage a neutral source to act as a middle man. Since you are using XE, you cannot use Transparent Gateways. That leaves a neutral playing field to facilitate the transfer. One option is to dump the data from your MS Access database to flat files. Then use Oracle's External Tables to read from the flat files. You can have SQL statements perform your data validation and insert into Oracle tables those records that are valid from the External Table. The External Table is just a pointer to a flat file. The last option I can think of is to write a utility in some language like Perl which can access databases using ODBC. The Perl program can read from MS Access and write to Oracle.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.