The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access-Oracle Regular Migration

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 exact structure as of its original MS Access database. We call this Buffer Database. As the user enters two dates and press 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 were 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 might have changed, then updation 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 synchronisation tool for Access-Oracle. Though few are availale for Access-MySQL.

Please suggest some tips on how to effectively merge records in the Oracle database so that the database maintains integrity.
Rohit Send private email
Monday, January 29, 2007
>> Please suggest some tips on how to effectively merge records in the Oracle database so that the database maintains integrity

Use the MERGE command ...
David Aldridge Send private email
Monday, January 29, 2007
Any reason why you are going with Oracle and not SQL Server? SQL Server has free editions as well and seems like a more natural migration path from Access. They also now have SQL Server Compact Edition which is a free embedded database like Access' Jet Database Engine was.

Sorry I'm no help on your particular question. But being a .NET guy who was forced to use Oracle I can tell you that I wouldn't wish it on my worst enemy!  ;)
dood mcdoogle
Monday, January 29, 2007
+1 on keeping it within the MS world if possible. Access is often (if not always) a dead end, but the migration to Oracle will kill you. I'm an Oracle guy, I've done it, it's terrible.

If you really have to migrate it to Oracle, I'd just rewrite it, at least the database portion. It'd minimize your end time - start time.
Steve Hirsch Send private email
Monday, January 29, 2007
"We call this Buffer Database."

I have some concerns about the way you're using a buffer database to begin with. From experience, it doesn't really solve any problems that couldn't be solved with simplier solutions. In fact, the only situation that I can think of that calls for a buffer database, is if the entire database can't fit on a CD, DVD or a pen drive.

Since you wrote the Access code in VB.NET and you'll presumably write the Oracle code in VB.NET as well, it's trivial to write a conversion program that literally reads in a record from Access and attempts to insert/update it in Oracle.

Keep in mind that the Access databases are essentially the "authoriative" copies. If a record has been updated in Access, it needs to be replaced in Oracle. It never goes back the other way, from Oracle to Access. As a result, there's no harm no foul in overwriting an Oracle record with what's essentially the same Access record.

Once you have loaded a substantial number of records in the Access database, modify your VB.NET conversion program to accept a start date and only load records in after that date.

Of course, you'll want to make the program a little more robust, check for errors, etc etc, but based on the way the problem is written, you're just looking for a "backup mechanism", and intend for the home office to work off the copies instead of the originals.
Wednesday, January 31, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz