I have MS Access data on various machine. I want to import that data to Oracle after regular intervals, so that a centralised database can be maintained(a replica of various databases runnning on MS Access)
Plz suggest me how can this be achieved.
This topic has been discussed many times on this form and a quick search will probably yield you some useful info.
But a couple(there are many) of ways :
- You can create ODBC tables in Access that point to Oracle "Target" Tables. You can write an Access Delete and Append Query to delete the Oracle Table, and then insert all records. This will take care of an updates, inserts, or deletes that happen in the Access database and replicate those changes to the Oracle database. You can wrap that in modules, VBA, or VB code and schedule it on an NT box using AT.
- You can create scripts that use SQL_LOADER to import Flat Files extracted from Access to the Oracle Database.