Data Transfer using ADO

[question911]

Need sample code for transferring records from two disparate databases like Informix to Oracle and vice versa using ADO. I'll be transferring anywhere between 100 records to 16,000 records.

 

[pkailas]

There's no special code needed that I can think of. The only thing you should need to do is set up the connections to the different systems. It could be as simple as 2 different DSN connections or OLEDB.

 

[bjd4jc]

Or, if you have a SQL Server, you can setup a DTS package (even though neither source nor destination is SQL Server) to transfer the data. This would be my preferred method.

 

[question911]

How would the code like if I needed to get the results from the first database to the second database. For example I can get the recordset from my MS SQL databse, but how do I pass these records over to the Oracle connection to perform an insert of these records?

 

[bjd4jc]

If you're wanting to do it based on a recordset then you will have to loop through each record and do INSERT statements, or write an output file that is imported into Oracle.

I am not an Oracle user so I don't really know if they have anything comparable to SQL Servers DTS.

 

[question911]

Do you have sample code to loop through the recordset to insert into Oracle table? This sounds like it would take forever to insert 200,000 records.

 

[bjd4jc]

There is plenty of code on this forum that will loop you through a recordset and insert values. Do a keyword search here or look through the FAQ section.

 

[fredericofonseca]

ADO will be too slow.

Look at using SQLLDR (SQL*LOADER) from Oracle, and search for similar tool from Informix.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[question911]

Thanks for feedbacks.

I used Access to link the two tables and created a query to perform inserts. Man, it would take 4 hrs to complete the job. Does anyone know if ADO is being used when using an Access query to perform an insert?

One of the reason I preferred avoiding using sqlldr was my inexperienced knowledge of Unix, since this Oracle instance runs on a Solaris box. I guess just suck it up and learn how to shell script and schedule in Cron.

Thanks again.

 

[dilettante]

My guess would be that Access is using its own interface directly to Jet. If not that, then it would more likely use DAO, but I doubt it. ADO seems the least likely though unless you've coded to use ADO in VBA.

 

[bjd4jc]

>>My guess would be that Access is using its own interface directly to Jet

Jet would be integrating with the ODBC drivers that would be used for Oracle and MySQL.