Home » RDBMS Server » Server Utilities » Load data from access database in oracle (oracle 9i)
Load data from access database in oracle [message #621672] Fri, 15 August 2014 13:18 Go to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
We are using machines for attendance/access control and these machines stores data into access database but my attendance system is developed into oracle Forms9i therefore I want to create a routine, which import data from access database to oracle database on daily basis.

Your urgent reply would be highly appreciated.

Re: Load data from access database in oracle [message #621674 is a reply to message #621672] Fri, 15 August 2014 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
>Your urgent reply would be highly appreciated.
please explain why it is urgent for us to respond to this thread.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

V9 Oracle has been unsupported & obsoleted this whole Century.

MS ACCESS using ODBC can connect directly to Oracle database & issue SQL against it.
The details of exactly how this is done is OFF TOPIC for this forum, because Oracle database does not know or care about which "flavor" of client that connects to it.
Any and all activities to satisfy your requirement must be done on the Microsoft side since Oracle accepts data from any INSERT statement.

[Updated on: Fri, 15 August 2014 13:34]

Report message to a moderator

Re: Load data from access database in oracle [message #621676 is a reply to message #621674] Fri, 15 August 2014 13:53 Go to previous message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
You can also export your Microsoft Access data to either fixed position or delimited text files and then either use Oracle SQL*Loader to load the data into Oracle tables or, if you can put the text files on your server, you can also use Oracle external tables to access the data in the files and insert it into Oracle tables. I don't know what your situation is, in terms of connectivity, where your data comes from, and how much needs to be automated or manual. A long time ago, I used to receive Microsoft Access mdb files on disks, use Microsoft Access to export the data to text files, then use SQL*Loader to load the data into Oracle 8i tables. I started trying to find a way to automate the process, but found that the source format was unpredictable. They tended to change the order, size, and names of columns, and add new ones. So, each month, I had to review the new format and revise the SQL*Loader control files to load the state data into the county system. If you are loading data daily, then you probably want to automate the entire process. If you upgrade to a current version of Oracle, you can set up things like dbms_scheduler.file_watcher, such that as soon as a text file appears on the server, it is automatically processed.






Previous Topic: how to access sqlldr on network
Next Topic: Datapump question
Goto Forum:
  


Current Time: Mon Jul 22 00:27:43 CDT 2019