3

Kind of new to this, I'm trying to execute an insert query(to an oracle database) with parameterized query in vba excel

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ccmd As New ADODB.Command

str = "Provider=MSDAORA;Data Source=db;Persist Security Info=True;Password=pword;User ID=uname"
Set cnn = CreateObject("ADODB.Connection")
cnn.Open str
Set rs = CreateObject("ADODB.Recordset")

ccmd.ActiveConnection = cnn
ccmd.CommandText = "Insert into Table Values(@col1,@col5,@col8,@col6,@col7,@col2,@col3,@col4)"
ccmd.CommandType = adCmdText

ccmd.Parameters.Append ccmd.CreateParameter("@col1", adVarChar, adParamInput, 50, Cells(i, 1).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col5", adVarChar, adParamInput, 50, Cells(i, 5).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col8", adVarChar, adParamInput, 50, Cells(i, 8).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col6", adVarChar, adParamInput, 50, Cells(i, 6).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col7", adVarChar, adParamInput, 50, Cells(i, 7).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col2", adVarChar, adParamInput, 50, Cells(i, 2).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col3", adVarChar, adParamInput, 50, Cells(i, 3).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col4", adVarChar, adParamInput, 50, Cells(i, 4).Value)

'execute the command here,  im having an error here. I'm not sure how to execute the command. I'm also not sure whether the error i'm getting is caused by how im executing the command or something else.

'I've tried:
'ccmd.Execute
'cnn.Execute(ccmd.CommandText)   
'rs = ccmd.execute

Automation Error

is what i'm getting

Edit:

Tried changing my query to Insert into Table Values(?,?,?,?,?,?,?,?) and i still get automation error. Also tried removing the '@' char in my parameter names and tried using ':' or '?'.

crimson
  • 221
  • 1
  • 7
  • 25

1 Answers1

0

There are a few ways to include Ranges in an insert but are you sure your getting a good connection? You can check with the following code (see the If after the Open) as Automation Error is a fairly general error and can include several different possible problems (a user not having correct permissions is common).

If this works the next step would be to either update your question or comment with the new problems and you can get help on the various ways to add cell ranges to Oracle. Also, MSDAORA is depreciated, see the accepted answer here for more information on that.

' Works on one of my test systems
Dim SQLString As String
str = "Provider=MSDAORA;Data Source=db;Persist Security Info=True;Password=pword;User ID=uname"

Set cnn = New ADODB.Connection
cnn.ConnectionString = str
cnn.ConnectionTimeout = 90
' Set the connection string directly and set timeout rather than open so
' We can check if connect worked.

cnn.Open
If cnn.State = adStateOpen Then
   MsgBox "Connected"
Else
   MsgBox "Sorry. Connection Failed"
   Exit Sub
End If

' Then I'd try a simple insert command and see if that works (to target error)
' If this works then the error is likely how the insert is created
SqlString = "Insert into table Values('Something Silly')"
cnn.Execute SqlString, ,adCmdText ' As this is a direct SQL you shouldn't need adCmdText but will later

cnn.Close
Community
  • 1
  • 1
LinkBerest
  • 1,281
  • 3
  • 22
  • 30
  • I actually have an update query that is working just fine before the insert so i'm sure the connection is ok. But i'm not using parameters for the update query. It's a simple `sqlStr = "Update Table set ColumnA = '" & Cells(i,1).Value & "'"` `cnn.Execute(sqlStr)` – crimson Jun 17 '15 at 03:52
  • @crimson First, *insert* is a different permission than *update* so I'd recommend testing both. Cannot get to computer to update this right now, only suggestion would be to remove the `ccmd` call and just use a direct call to `ccn` or use [ccmd *with*](http://stackoverflow.com/questions/4806409/creating-parameterized-sql-queries-in-excel-2010-with-vba?rq=1) – LinkBerest Jun 17 '15 at 04:50