VBA - Insert Into Oracle Table

[Noz2k]

Hey, I'm trying to insert data into a table in an Oracle database but not having much luck with what I can find online. Below is the code which I currently have, any help would be really appreciated.

Sub Insert()


Set ws1 = Sheets("Sheet1")

With ws1
   inputData = ws1.Range("A1").Value
End With

Dim cnn As ADODB.Connection




   Dim SqlString As String
   Set cnn = New ADODB.Connection
   ' Set properties of the Connection.
   cnn.ConnectionString = "Provider=MSDAORA;Data Source=xxxxxx;user ID=xxxxxxx;password=xxxxxxx;"
   cnn.ConnectionTimeout = 90
   ' Open the connection.
   cnn.Open
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
   
   Else
      MsgBox "Sorry. Connection Failed"
      Exit Sub
   End If
 
        
        'Define Query
        SqlString = "Insert into TABLE_NAME Values('" & inputData & "'); " & vbNewLine & _
        "Commit;"
                  
        ws1.Range("A10") = SqlString
        cnn.Execute SqlString
        
   'Next x
   ' Close the connection.
   cnn.Close
   Set cnn = Nothing
End Sub

 

[Noz2k]

I get an "invalid character" error, but the SQL works if I paste what it returns in Cell A10

 

[Noz2k]

Just for anyone who may come across a similar problem, I was missing the "adCmdText"

So the code now looks like

Sub Insert()


Set ws1 = Sheets("Sheet1")
'Define last row
With ws1
   inputData = ws1.Range("A1").Value
End With
 Dim cnn As ADODB.Connection




   Dim SqlString As String
   Set cnn = New ADODB.Connection
   ' Set properties of the Connection.
   cnn.ConnectionString = "Provider=xxxxxxx;Data Source=xxxxxxx;user ID=xxxxxxx;password=xxxxxxx;"
   cnn.ConnectionTimeout = 90
   ' Open the connection.
   cnn.Open
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
   
   Else
      MsgBox "Sorry. Connection Failed"
      Exit Sub
   End If


        'Define Query
        SqlString = "Insert into TABLE_NAME Values('" & inputData & "')"
                  
        ws1.Range("A10") = SqlString
        cnn.Execute SqlString, , adCmdText
        
   'Next x
   ' Close the connection.
   cnn.Close
   Set cnn = Nothing
End Sub