VBA: Transfer range table data to a ADODB.RecordSet

inddon · May 8, 2015

Hello There,

I have the below code which gets the data from a PL/SQL procedure in a ADODB.recordset and transfers the same into a table.

I would like to know how this could be done in a reverse way i.e. transfer the range table data to a ADODB.recordset (without looping through the entire range table).

Could you please advice?

Thanks & regards
Don

Code:

Sub GetData()
  Dim cmd As New ADODB.Command
  Dim RecordSet As New ADODB.RecordSet
  Dim ExcelRange As Range
  Dim param1 As New ADODB.Parameter
  Dim param2 As New ADODB.Parameter
  Dim objErr As ADODB.Error
   
  'Connect to Oracle Database
  OracleConnect
  Set cmd.ActiveConnection = con
   
  'Set Input parameters
  DeptValue = 30
   
  'Set parameters
  With cmd.Parameters
  .Append cmd.CreateParameter("param1", adSmallInt, adParamInput, 10, DeptValue)
  .Append cmd.CreateParameter("param2", adSmallInt, adParamOutput)
  End With
   
  'Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = True
   
  'PL/SQl ref cursor is pl/sql recordset, which is not displayed in the parameter
  ' but the provider auto fetches it in RecordSet
  cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
  Set RecordSet = cmd.Execute
   
  'Disable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = False
   
  'Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set ExcelRange = ws1.Range("E11")
   
  '------------------------------------------------
  'Copy PL/SQL recordset array into excel range
  ExcelRange.CopyFromRecordset RecordSet
  '------------------------------------------------
   
   
  'Close connection to Oracle Database and Recordset
  If RecordSet.State = adStateOpen Then RecordSet.Close
  OracleDisConnect
End Sub

NARAYANK991 · May 9, 2015

Hi ,

See if the following links help :

http://www.globaliconnect.com/excel...access-to-excel-using-ado&catid=79&Itemid=475

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

Narayan

inddon · May 10, 2015

NARAYANK991 said:
Hi ,

See if the following links help :

http://www.globaliconnect.com/excel...access-to-excel-using-ado&catid=79&Itemid=475

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

Narayan

Thank you Narayan for the reference.

The code seems complex, looks good. I will study it.

Regards
Don

inddon · May 15, 2015

NARAYANK991 said:
Hi ,

See if the following links help :

http://www.globaliconnect.com/excel...access-to-excel-using-ado&catid=79&Itemid=475

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

Narayan

Hello Narayan,

I tried my best to study the links you provided, and I couldn't make it work in Oracle.

Therefore, through some search on the internet and a lot of trial and error I could do the export of data from excel table to Oracle in the below manner, which actually works.

My concern is for every excel Table column, I create a parameter and also create a parameter in Oracle package. In some cases I have 39 columns in a Table, which means I would have to create 39 parameters. This would become very tedious and increase in file size.

I have been working on this for > 1 week and could not go forward. Could you Please review the below code and advice the best option where I can pass key parameters of IN & OUT and the rest as a recordset or array and how this can be passed to Oracle?

Many thanks for your help.

Regards
Don

1. Oracle Database package

Code:

create or replace PACKAGE AD_PERSONS_PKG AS
  PROCEDURE Do_DML(p_person_id  NUMBER
  ,p_person_type  VARCHAR2
  ,p_title  VARCHAR2
  ,p_first_name  VARCHAR2
  ,p_id  OUT  NUMBER
  ,p_dml_status  OUT  VARCHAR2
  ,p_err_code  OUT  NUMBER);

END AD_PERSONS_PKG;

2. Connect Oracle Database

Code:

Option Explicit
 
Public con As New ADODB.Connection
Public SQLStr As String
Public cmd As New ADODB.Command

 
3. Public Sub OracleConnect()
  If con.State = adStateOpen Then con.Close
 
  con.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
  "Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=" & _
  "(ADDRESS=(PROTOCOL=TCP)(HOST=LocalHost)(PORT=1521)))" & _
  "(CONNECT_DATA=(SERVICE_NAME=XE)(SERVER=DEDICATED)));" & _
  "User Id=System;Password=oracle;"
 
  con.Open
 
  WsInitialiseObjects
End Sub

4. Disconnect oracle Database

Code:

Public Sub OracleDisConnect()
  If con.State = adStateOpen Then
  con.Close
  End If
End Sub

5. Pass Table records From Excel to Oracle
Sub to pick rows from the table. Stores each column as a parameter(x). These parameters are then send to the Oracle PL/SQL package(4 IN parameters(param1-param4), and others as out parameters).

Code:

Sub Persons_DML()
  Dim RecordSet As ADODB.RecordSet
  Dim lo As Excel.ListObject
  Dim lr As Excel.ListRow
  Dim l_status As String
  Dim ws As Excel.Worksheet
  Dim lc As Long

  Dim tc As Integer, rc As Integer
  Dim param1 As New ADODB.Parameter, param2 As New ADODB.Parameter, param3 As New ADODB.Parameter, param4 As New ADODB.Parameter
  Dim paramid As New ADODB.Parameter, paramuser As New ADODB.Parameter, paramcode As New ADODB.Parameter, paramstatus As New ADODB.Parameter
  Dim l_id As Integer
 
  Set ws = ThisWorkbook.Worksheets("Persons")
  Set lo = ws.ListObjects("Persons") 'Table
 
  OracleConnect
 
  Set cmd.ActiveConnection = con
 
  For Each lr In lo.ListRows
  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = True
 
  l_status = ""
  cmd.CommandType = adCmdText
  'call to Oracle package and pass the IN parameters
  cmd.CommandText = "{CALL AD_PERSONS_PKG.Do_DML(?,?,?,?) }"
 
  'Set parameters. Stores Table column names in their individual parameters
  Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, 100, Intersect(lr.Range, lo.ListColumns(lo.ListColumns(tc - (tc - 3)).Name).Range).Value)
  cmd.Parameters.Append param1
  Set param2 = cmd.CreateParameter("param2", adVarChar, adParamInput, 100, Intersect(lr.Range, lo.ListColumns(lo.ListColumns(tc - (tc - 4)).Name).Range).Value)
  cmd.Parameters.Append param2
  Set param3 = cmd.CreateParameter("param3", adVarChar, adParamInput, 100, Intersect(lr.Range, lo.ListColumns(lo.ListColumns(tc - (tc - 5)).Name).Range).Value)
  cmd.Parameters.Append param3
  Set param4 = cmd.CreateParameter("param4", adVarChar, adParamInput, 100, Intersect(lr.Range, lo.ListColumns(lo.ListColumns(tc - (tc - 6)).Name).Range).Value)
  cmd.Parameters.Append param4

'Set out parameters
  Set paramid = cmd.CreateParameter("paramid", adSmallInt, adParamOutput, , l_id)
  cmd.Parameters.Append paramid
  Set paramstatus = cmd.CreateParameter("paramstatus", adVarChar, adParamOutput, 100, l_status)
  cmd.Parameters.Append paramstatus
  Set paramcode = cmd.CreateParameter("paramcode", adSmallInt, adParamOutput, , g_error_code)
  cmd.Parameters.Append paramcode

  Set RecordSet = cmd.Execute

  'Update the Table "Persons" Status column for the selected row with the return value from Oracle package and also the column Person ID
  Intersect(lo.ListColumns("Status").Range, lr.Range).Value = cmd.Parameters("paramstatus").Value
  Intersect(lo.ListColumns("Person_ID").Range, lr.Range).Value = cmd.Parameters("paramid").Value

  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = False

  'clear the parameters
  cmd.Parameters.Delete ("param1")
  cmd.Parameters.Delete ("param2")
  cmd.Parameters.Delete ("param3")
  cmd.Parameters.Delete ("param4")
 
  cmd.Parameters.Delete ("paramid")
  cmd.Parameters.Delete ("paramstatus")
  cmd.Parameters.Delete ("paramcode")
  Next lr
 
  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = True

  OracleDisConnect
End Sub

NARAYANK991 · May 16, 2015

Hi ,

I have not understood why you are using parameters , in the first place.

Narayan

inddon · May 16, 2015

NARAYANK991 said:
Hi ,

I have not understood why you are using parameters , in the first place.

Narayan

Hello Narayan,

I wanted to use arrays reading table row by row and get the columns and send it to the database. But I could not understand it in how to build, pass values and pass the same to the oracle package, and therefore took the long path of parameters:

This is what I had in mind:
1. Build the array
2. For each row in table
2a.read individual columns and assign it to the array
call oracle package, pass the array, & other in & out parameters
(here the oracle package does the database operation. Returns the
status code: success or failure, through the above oracle package.
The status code is updated in the status column of the table row)
read next row of the table

Could you please help in how to achieve the above and if there is a better way to get this functionality?

Many thanks for your advice.

Regards
Don

NARAYANK991 · May 16, 2015

Hi Don ,

I am sorry but I have not worked well enough with Oracle to remember the proper syntax for the programming ; why don't you post this question in an Oracle forum , since it is more likely that the members of that forum would be likely to know about Excel , than the other way round ?

Narayan

inddon · May 16, 2015

NARAYANK991 said:
Hi Don ,

I am sorry but I have not worked well enough with Oracle to remember the proper syntax for the programming ; why don't you post this question in an Oracle forum , since it is more likely that the members of that forum would be likely to know about Excel , than the other way round ?

Narayan

Hello Narayan,

Thank you for your reply.

Could you help me writing the below code:
1. Build the array
2. For each row in excel table (attach: Printscreen of the table)
2a.read individual columns and assign it to the array
Read next row of the table

I will check with Oracle forum how to pass this in Oracle package.

Many thanks

Regards
Don

NARAYANK991 · May 16, 2015

Hi Don ,

Would it be possible to export the table to a .csv file , and then read the .csv file into Oracle ?

Narayan

inddon · May 16, 2015

NARAYANK991 said:
Hi Don ,

Would it be possible to export the table to a .csv file , and then read the .csv file into Oracle ?

Narayan

Hi Narayan,

That would land in writing a SQL*Loader script. Would like to do via VBA and Oracle package only.

Regards
Don

inddon · May 20, 2015

NARAYANK991 said:
Hi Don ,

Would it be possible to export the table to a .csv file , and then read the .csv file into Oracle ?

Narayan

Hello Narayan,

Would it possible for you to help me out for the requirement listed in post #8 above?

Many thanks.

Regards
Don

inddon · May 25, 2015

inddon said:
Hello Narayan,

Would it possible for you to help me out for the requirement listed in post #8 above?

Many thanks.

Regards
Don

Hello Narayan,

The solution is done. I am passing 2 parameters which is dynamically built in VBA one for Insert and the other for Update. May I ask you to kindly prefix the post subject to [SOLVED].

Below code. This (excel vba part) could be further refined, any advice/suggestions?:

Code:

Sub Persons_DML()
  Dim RecordSet As ADODB.RecordSet
  Dim lo As Excel.ListObject
  Dim lr As Excel.ListRow, lc As Excel.ListColumn
  Dim l_status As String
  Dim ws As Excel.Worksheet

  Dim SQLStringIns As String, SQLStringUpd As String, ColumnValue As String, personID As Integer
  Dim paramid As New ADODB.Parameter, paramuser As New ADODB.Parameter, paramcode As New ADODB.Parameter, paramstatus As New ADODB.Parameter
  
  Set ws = ThisWorkbook.Worksheets("Persons")
  Set lo = ws.ListObjects("Persons")
  
  Call WsInitialiseObjects
  
  If lo.ListColumns.Count <> wsSetup.Range("D4").Value Then
  MsgBox "Error: Detected changes in the table structure." & Chr(10) & Chr(10) & _
  "Possible cause: An additional column has been added." & Chr(10) & _
  "Unable to execute task. Contact Excel Administrator"
  Exit Sub
  End If
  
  OracleConnect
  
  Set cmd.ActiveConnection = con
  SQLStringIns = "INSERT INTO ad_persons values ("
  SQLStringUpd = "UPDATE ad_persons SET "

  
  For Each lr In lo.ListRows
  For Each lc In lo.ListColumns 'loop through columns to build Insert/Update statement
  
  If lo.ListColumns(lc.Name) = "Person ID" Then
  personID = Intersect(lr.Range, lo.ListColumns(lo.ListColumns("Person ID").Name).Range).Value
  SQLStringIns = SQLStringIns & ":1, " 'person_id
  GoTo nextrec
  End If
  
  If lo.ListColumns(lc.Name) = "Sr." Or lo.ListColumns(lc.Name) = "Status" Then GoTo nextrec
  If lo.ListColumns(lc.Name) = "Date of Birth" Then GoTo datevalue
  
  ColumnValue = MCI(Intersect(lr.Range, lo.ListColumns(lc.Name).Range).Value)
  SQLStringIns = SQLStringIns & ColumnValue
  SQLStringUpd = SQLStringUpd & Replace(lo.ListColumns(lc.Name), " ", "_") & " = " & ColumnValue
  
  GoTo nextrec
datevalue:
  ColumnValue = MD(Intersect(lr.Range, lo.ListColumns(lc.Name).Range).Value)
  SQLStringIns = SQLStringIns & ColumnValue
  SQLStringUpd = SQLStringUpd & Replace(lo.ListColumns(lc.Name), " ", "_") & " = " & ColumnValue
nextrec:
  Next lc
  
  SQLStringIns = Left(SQLStringIns, Len(SQLStringIns) - 1) & ")"
  SQLStringUpd = Left(SQLStringUpd, Len(SQLStringUpd) - 1) & "  WHERE person_id = " & personID
  'MsgBox SQLStringIns & Chr(10) & SQLStringUpd
  
  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = True
  
  l_status = ""
  cmd.CommandType = adCmdText
  cmd.CommandText = "{CALL AD_PERSONS_PKG.Do_DML1(?,?,?,?,?,?) }"
  
  'Set parameters
  Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, 100, personID)
  cmd.Parameters.Append param1
  Set param2 = cmd.CreateParameter("param2", adVarChar, adParamInput, 2000, SQLStringIns)
  cmd.Parameters.Append param2
  Set param3 = cmd.CreateParameter("param3", adVarChar, adParamInput, 2000, SQLStringUpd)
  cmd.Parameters.Append param3
  
  Set paramid = cmd.CreateParameter("paramid", adSmallInt, adParamOutput, , l_id)
  cmd.Parameters.Append paramid
  Set paramstatus = cmd.CreateParameter("paramstatus", adVarChar, adParamOutput, 100, l_status)
  cmd.Parameters.Append paramstatus
  Set paramerror = cmd.CreateParameter("paramerror", adVarChar, adParamOutput, 2000, g_error)
  cmd.Parameters.Append paramerror
  
  Set RecordSet = cmd.Execute
  
  If cmd.Parameters("paramstatus").Value = "Error" Then
  Intersect(lo.ListColumns("Status").Range, lr.Range).Value = cmd.Parameters("paramerror").Value
  Else
  Intersect(lo.ListColumns("Status").Range, lr.Range).Value = cmd.Parameters("paramstatus").Value
  End If
  
  Intersect(lo.ListColumns("Person ID").Range, lr.Range).Value = cmd.Parameters("paramid").Value
  
  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = False
  cmd.Parameters.Delete ("param1")
  cmd.Parameters.Delete ("param2")
  cmd.Parameters.Delete ("param3")
  cmd.Parameters.Delete ("paramid")
  cmd.Parameters.Delete ("paramstatus")
  cmd.Parameters.Delete ("paramerror")
  
  ' Enable PLSQLRSet property
  cmd.Properties("PLSQLRSet") = False
  
  SQLStringIns = "INSERT INTO ad_persons values ("
  SQLStringUpd = "UPDATE ad_persons SET "
  Next lr
  OracleDisConnect
End Sub

[code/]

Regards,
Don

VBA: Transfer range table data to a ADODB.RecordSet

inddon

Member

NARAYANK991

Excel Ninja

inddon

Member

inddon

Member

NARAYANK991

Excel Ninja

inddon

Member

NARAYANK991

Excel Ninja

inddon

Member

NARAYANK991

Excel Ninja

inddon

Member

inddon

Member

inddon

Member