Use ADODB Recordset to INSERT Oracle DB

Question

I am converting some Excel-VBA code that uploaded a DAO recordset to an Access database. My new code uses ADODB objects and needs to push the data to Oracle 12c.

I reviewed some articles, and found a handy equivalency chart here: From-DAO-to-ADO. Using this information I created the following code.

This first bit just loads up the source recordset. No problems here, but published if it's relevant:

Dim CN As New ADODB.Connection, RS As New ADODB.Recordset
Dim SRC_CN As New ADODB.Connection, SRC_RS As New ADODB.Recordset, SRC_CMD As New ADODB.Command
strSQL = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Src_WB_nm & _
        ";Extended Properties='Excel 12.0 Xml;HDR=NO';"
SRC_CN.Open strSQL
Set SRC_CMD.ActiveConnection = SRC_CN
SRC_CMD.CommandType = adCmdText
SRC_RS.Close
Set SRC_RS = Nothing
strSQL = "SELECT * FROM [" & TableNm & "]"
SRC_CMD.CommandText = strSQL
With SRC_RS
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open SRC_CMD
End With

In this segment, I open the destination connection and attempt to open the destination recordset. Thismethodology (AddNew, RS...value = RS.value, RS.Update...) worked when I was in DAO. I expect it may need to be modified somewhat, but its the RS.Open command that I can't get past now.

CN.Open CSTRG
strSQL = "DELETE FROM DFSTOOL.C_QUERYLIST"
Set RS = CN.Execute(strSQL)
Set RS = Nothing
RS.Open "DFSTOOL.C_QUERYLIST", CN, adCmdTable
Do Until SRC_RS.EOF
    RS.AddNew
    RS.Fields(0).Value = SRC_RS.Fields(0).Value
    RS.Update
    SRC_RS.MoveNext
Loop
RS.Close
SRC_RS.Close
Set RS = Nothing
Set SRC_RS = Nothing

The error thrown is:

I appreciate any help you can provide!

Mike Shanahan · Accepted Answer · 2018-01-30 17:00:30Z

0

So happily this was a simple syntax issue. Perhaps I should delete the post, but I'll leave it for now in case it helps anyone else. The equivalency table I referenced noted that the "adCmdTable" is an option in ADO, whereas the equivalent "dbOpenTable" was a type in DAO. Thus, I was required to skip a few fields so that it was in the right location. Other fine-tuning followed and the final iteration works as expected:

RS.Open "DFSTOOL.C_QUERYLIST", CN, adOpenForwardOnly, adLockOptimistic, adCmdTable
Do Until SRC_RS.EOF
    RS.AddNew
    RS.Fields(1).Value = SRC_RS.Fields(0).Value
    RS.Update
    SRC_RS.MoveNext
Loop

Thanks for your patience community!

answered Jan 30, 2018 at 17:00

Mike Shanahan

2391 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use ADODB Recordset to INSERT Oracle DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related