Executing a parameterized insert query in vba excel

Question

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 '?'.

Community · Accepted Answer · 2017-05-23 12:29:58Z

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jun 17, 2015 at 3:35

LinkBerest - SO sold our work

1,3103 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

crimson Over a year ago

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)

LinkBerest - SO sold our work Over a year ago

@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

Collectives™ on Stack Overflow

Executing a parameterized insert query in vba excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related