AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of DarrenJackson
DarrenJackson
Flag 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
Microsoft ExcelOracle DatabaseVB ScriptVBA
Avatar of undefined
Last Comment
Alex [***Alex140181***]
8/22/2022 - Mon
Alex [***Alex140181***]
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

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
DarrenJackson
ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Alex [***Alex140181***]
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"?!
DarrenJackson
ASKER
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
DarrenJackson
ASKER
Alexander I will attach the file that I am working on to help make this easier to understand
insert-into-oracle.xlsm
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Alex [***Alex140181***]
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

DarrenJackson
ASKER
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
DarrenJackson
ASKER
If it helps use the excel file I attached
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Alex [***Alex140181***]
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DarrenJackson
ASKER
Perfect thankyou for all your help :)
Alex [***Alex140181***]
You're welcome, no problem ;-)
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent