ADO Insert into Oracle from Access query

[citychap26]

Hi

I have a query with over 600k rows. I am trying to append this data into an Oracle table with is linked to Access. It took about 30 mins!!!

I am now thinking of going the ADO route. Here's what I plan.
Use a DAO recordset to capture the access dataset, loop thought the recordset and pass each line into a ADO.connection and execute a insert into statement.

What do you guys think? IS there a better way?

Cheers

SK

 

[RoyVidar]

In theory, executing a query is always faster than recordset approaches. This is also my experience. But just try it, and see for yourself - and please also post the results!

But both the Access query and the recordset approach here, would be executed in/by Access, i e all/most of the processing would be on the Access side.

I don't know how and if it is possible, but there could be a way to make Oracle execute this, that could perhaps make it faster. Perhaps you can find one of the Oracle fora, and ask there? I'm not sure, but I think I've seen the member cmmrfrds giving some samples of such with Access and SQL server in one of the Access fora.

Roy-Vidar

 

[PHV]

Anyway, 30 mins for 600k rows means 3 milliseconds by row.
Really so bad ?

 

[citychap26]

GRRRRRRR

I have written the following and now getting this error

ORA-01012: user requested cancel of current operation

Sub ADO_Connection()

Dim oconn As New ADODB.Connection
Dim rst As DAO.Recordset
Dim db As Database
Dim i As Integer
Dim sSQL_Value As String
Dim ssql As String
Dim ssqlFields As String
Dim sFULL_SQL As String

Set db = CurrentDb()

Set rst = db.OpenRecordset("select * from qryAppDPGRDW_RMP_Caustic_Accts", dbOpenDynaset)

oconn.ConnectionString = "Driver={Oracle ODBC Driver};" & _
"Dbq=FDMU;" & _
"Uid=FDM;" & _
"Pwd=NOTFDM"

oconn.Open


rst.MoveFirst

'--- Get the fields now so we only have to do it once

For i = 0 To rst.Fields.Count - 1
ssqlFields = ssqlFields & ", " & rst.Fields(i).Name
Next

ssqlFields = Right(ssqlFields, Len(ssqlFields) - 1)

'MsgBox ssqlFields

Do While Not (rst.BOF Or rst.EOF)
'--- create a string to insert
For i = 0 To rst.Fields.Count - 1

sSQL_Value = createCorrectValue(Nz(rst.Fields(i).Value, ""), rst.Fields(i).Type)
ssql = ssql & ", " & sSQL_Value
Next

ssql = Right(ssql, Len(ssql) - 1)


sFULL_SQL = "INSERT INTO DPGRDW_RMP_CAUSTIC_ACCTS (" & ssqlFields & ")" & "VALUES (" & ssql & ")"

oconn.Execute sFULL_SQL

Debug.Print sFULL_SQL

ssql = ""

rst.MoveNext
Loop

End Sub

Function createCorrectValue(sValue As String, iFieldType As Integer) As String

Select Case iFieldType
Case dbText
createCorrectValue = Chr(39) & sValue & Chr(39)
Case Else
createCorrectValue = sValue
End Select

End Function

 

[chris13]

That's not really bad, but depends on the amount of data and how complex the query is...
I'm doing it the other way: query data from an oracle-DB and insert the results into a local Access-DB. 120k rows in 10 minutes on a 2.4GHz-PC. Doing the same on a 350MHz-PC needs 25 minutes. When I enter the query in the Oracle-Client (SQL*plus) and spool to a CSV-File, everything is finished in less than 5 minutes. That's because the server does the computing and there is no overhead using JET-engine + ODBC etc...

 

[citychap26]

Hi Guys,

Here's an update. Got the code to work, I dropped the index and all permissions on the Oracle table and recreated it. And hey presto, it all works!!!

Anyone interested in the final code?

Cheers

SK

 

[Checking]

Yesss plzzz citychap26; could you post-it ?
Thks

Belgium

 

[citychap26]

Sub ADO_Update_Oracle(sTblNameLOAD_TO As String, sTblNameEXTRACT_FROM As String)

On Error GoTo ERR_HANDLER

Dim oconn As New ADODB.Connection
Dim ADOrst As ADODB.Recordset
Dim rst As DAO.Recordset
Dim db As Database
Dim i As Integer
Dim sEXTRACT_SQL As String
Dim lEXTRACT_SQL_COUNT As Long
Dim sSQL_Value As String
Dim sSQL As String
Dim ssqlFields As String
Dim sFULL_SQL As String
Dim x As Long

Set db = CurrentDb()

Debug.Print "time started " & Now()
'--- Create extract SQL statement
sEXTRACT_SQL = "select * from " & sTblNameEXTRACT_FROM
'--- Set the recordset that we are extracting from
Set rst = db.OpenRecordset(sEXTRACT_SQL, dbOpenDynaset)
'--- Create connection to Oracle
With oconn
.ConnectionString = "Driver={Oracle ODBC Driver};" & _
"Dbq=FDMU;" & _
"Uid=FDM;" & _
"Pwd=NOTFDM"
.Open
.CursorLocation = adUseServer
End With
'--- Initialise x, this is just used to update a progress in the debug window.
x = 0
'--- Get a record count of the extract SQL
With rst
.MoveLast
lEXTRACT_SQL_COUNT = .RecordCount
.MoveFirst
End With
'--- Get the fields from extracting table now so we only have to do it once
For i = 0 To rst.Fields.Count - 1
ssqlFields = ssqlFields & ", " & rst.Fields(i).Name
Next
' --- Trim the leading ","
ssqlFields = StrConv(Right(ssqlFields, Len(ssqlFields) - 1), vbUpperCase)

Debug.Print ssqlFields
'--- Lets create the SQL statment to insert and insert it!
Do While Not (rst.BOF Or rst.EOF)
'--- create a string to insert
For i = 0 To rst.Fields.Count - 1
sSQL_Value = createCorrectValue(Nz(rst.Fields(i).Value, ""), rst.Fields(i).Type)
sSQL = sSQL & ", " & sSQL_Value
Next
'--- Trim the leading "," from the SQL
sSQL = Right(sSQL, Len(sSQL) - 1)
'--- Create the actual SQL insert statement
sFULL_SQL = "INSERT INTO " & sTblNameLOAD_TO & " (" & ssqlFields & ")" & "VALUES (" & sSQL & ")"
'Debug.Print sFULL_SQL
'--- Load the insert statement into Oracle
oconn.Execute sFULL_SQL
'--- Initialise sSQL
sSQL = ""
'--- move on to the next one
rst.MoveNext
'Stop
x = x + 1

If x Mod 1000 = 0 Then
Debug.Print x / lEXTRACT_SQL_COUNT * 100 & ", " & Now()
End If
Loop

NORMAL_EXIT:

oconn.Close
Set oconn = Nothing

Set ADOrst = Nothing

Exit Sub

ERR_HANDLER:

MsgBox Err.Number & ", " & Err.Description
MsgBox "line " & x & "has an error in it"
Debug.Print sFULL_SQL
Resume Next

End Sub

Function createCorrectValue(sValue As String, iFieldType As Integer) As String

Dim iPos As Integer
Dim x As Integer

'--- Initialise
x = 1
'--- we want to quote text strings with single quotes
Select Case iFieldType
Case dbText
'--- Oracle uses single quotes, so we need to remove them as they will create an incorrect string to be returned
If InStr(sValue, "'") > 0 Then
'--- There may be more than one instance of the dreaded single quote in the string
Do Until x = Len(sValue)
iPos = InStr(x, sValue, "'")
If iPos <> 0 Then
x = iPos
'--- I'm just being lazy )
sValue = Left(sValue, x - 1) & Trim(Mid(sValue, x + 1, 255))
End If
x = x + 1
Loop
End If
createCorrectValue = Chr(39) & sValue & Chr(39)
Case dbDate
createCorrectValue = Chr(39) & Format(sValue, "dd mmm yyyy") & Chr(39)
Case Else
'--- we have already converted nulls to "" so if it isn't a text it'll get changed to a 0
If sValue = "" Then
createCorrectValue = 0
Else
createCorrectValue = Nz(sValue, "''")
End If
End Select

End Function