Link to home
Start Free TrialLog in
Avatar of DarrenJackson
DarrenJacksonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Insert new rows from excel to oracle

Guys, I need some help.
I have a sample vba script which successfully gets data from oracle and places it into excel.

What I want to do is take information from excel sheet and insert into oracle

here is the script but please note I have just used static values but the idea is to use data from an excel sheet

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
Source = Login.TextBox1.Value
user = Login.TextBox2.Value
password = Login.TextBox3.Value

' open the database

Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & Source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset

    With rs
' open the recordset
   
'    sSQL = "SELECT p.PROJECT_ID, p.SUB_PROJECT_ID, p.ACTIVITY_NO, p.ACTIVITY_SEQ, p.DESCRIPTION, p.PROJECT_ID||p.ACTIVITY_NO as ID " & _
'           "FROM IFSAPP.PROJECT_ACTIVITY_ALL p WHERE (p.SUB_PROJECT_ID='50') "

    sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
           "values ( 10007, 11111, 'B0101', 'PAINT100', 99, 10, 1000, 12/03/2015, SYSDATE); "

    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
   
End Sub

Can someone please modify this so that I can insert records from SHEET1 in excel into a table called TESTTEST.

Your help is very much appreciated
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

This is quite straightforward ;-) You may start with some examples here: https://www.nsbasic.com/desktop/info/technotes/TN22.htm
More can be found with the help of Dr. Google :-))

When you want to perform DMLs using ADODB, you'll have to work with transactions explicitly, e.g.:
'SQL
'...
strSQL="INSERT INTO MY_TABLE(COL1,COL2) VALUES('" & myVAR1 & "','" & myVAR2 & "')"

'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Commit Transaction
objADO.CommitTrans
'...

Open in new window

Avatar of DarrenJackson

ASKER

Hi Alexander, Thanks for getting back to me.

I have viewed that website but couldn't get to work there solutions.

I have tried your solution but it errors but I am thinking that this isn't correct

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
       
    ' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
    sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
           "values ( 10007, 11111, 'B0101', 'PAINT100', 99, 10, 1000, 12/03/2015, SYSDATE); "
           
    'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Commit Transaction
objADO.CommitTrans
           
    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
   
   
End Sub



For now I have hard coded values to get the insert working once this is working I will attempt the method I want which is to take the information from an excel sheet.

Can you help with this the error is OBJECT REQUIRED

Thanks
Alexander, I have this working where it is now inserting static data.

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
       
' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
       " values ( 10007, 1111111, 'B0101', 'PAINT100', 99, 10, 1000, to_date('12/03/2015','dd/mm/yyyy'), sysdate) "
           
'Begin Transaction
cn.BeginTrans

'Commit Transaction
cn.CommitTrans
           
    rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next

  End With
     
   
End Sub


So how would I now get this to look at SHEET1 and starting from row 2 column A look down and insert all records it finds.

Thanks for Helping
First of all, I suggest you put a "Option Explicit" above all your code ;-) This helps a lot, believe me...
E.g. the variable "sSQL" is NOT defined, plus, you're trying to execute an "empty"/NULL statement:
objADO.Execute strSQL

Open in new window

Where gets "strSQL" populated?!

Where exactly does this error occur?! Does this object exists / can you access its properties: "Login.TextBox1.Value"?!
Alexander I have updated the code to allow for OPTION EXPLICIT as recommended plus added some error checking


Option Explicit

Sub Insert_Oracle()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim TargetRange As Range
Dim sSQL As String
Dim lngRecAFF
Dim source As String
Dim user As String
Dim password As String
Dim rg As Range
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
Set TargetRange = Sheets("Main").Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
   
sSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
       " values ( 10007, 1111111, 'B0101', 'PAINT100', 99, 10, 1000, to_date('12/03/2015','dd/mm/yyyy'), sysdate) "
           
'Begin Transaction
cn.BeginTrans

rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
       
'Check the error
If cn.Errors.Count = 0 Then

         cn.CommitTrans
    Else
         cn.RollbackTrans
    End If

'Close ADO
cn.Close
       
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next

  End With
     
   
End Sub

So now how would I now get this to look at SHEET1 and starting from row 2 column A look down and insert all records it finds.

 Thanks for Helping
Alexander I will attach the file that I am working on to help make this easier to understand
insert-into-oracle.xlsm
My suggestion would look something like this (needs to be adjusted to suite your needs):
Option Explicit


Sub Insert_Oracle()

Dim cn As ADODB.Connection
Dim source As String
Dim user As String
Dim password As String
Dim baseSQL As String
Dim strSQL As String
Dim rg As Range
Dim row As Long
    
    source = "bla" 'Login.TextBox1.Value
    user = "usr" 'Login.TextBox2.Value
    password = "pw" ' Login.TextBox3.Value
           
        ' open the database
    Set cn = New ADODB.Connection
        cn.Open "Provider=OraOLEDB.Oracle;" & _
               "Data Source=" & source & ";" & _
               "User Id=" & user & ";" & _
               "Password=" & password & ""
       
    baseSQL = "INSERT INTO IFSAPP.TESTTEST (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID)" & _
             "values ( $P1, $P2, '$P3', '$P4', $P5, $P6, $P7, $P8, SYSDATE); "
               
    cn.BeginTrans
    
    row = 2
    
    Do While Sheets("SHEET1").Cells(row, 1) <> "" ' compare with Excel column that is filled until the end ;-)
    
        strSQL = Replace(baseSQL, "$P1", Sheets("SHEET1").Cells(row, 1))
        strSQL = Replace(baseSQL, "$P2", Sheets("SHEET1").Cells(row, 2))
        ' and so on... (depends on what you need and how many columns need to be filled
    
        cn.Execute strSQL
        
        cn.CommitTrans
    
    Loop
               
    cn.Close
    Set cn = Nothing
   
End Sub

Open in new window

Alexander, When I run this it seems to ignore the first columns information and tries to insert the value $P1 instead of the actual value of 10007 then errors saying invalid character
If it helps use the excel file I attached
ASKER CERTIFIED SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect thankyou for all your help :)
You're welcome, no problem ;-)