ADODB .Execute error

[Jayyde]

The following code is throwing the ever-informative "ODBC--call failed"
error on the cmd.Execute line of the following code:

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim index As Integer

index = DLookup("[NextPKofCategoryHierarchy]", "qryCategoryHierarchy")

strSQL = "INSERT INTO dbo_tblProductCategoryHierarchy " _
& "(iProductCategoryHierarchyId, " _
& "iProductCategoryId, " _
& "iProductCategoryParentId) " _
& "VALUES(" _
& index & ", " _
& id & ", " _
& intParentID & ");"

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = strSQL
cmd.Execute

Not sure why, I've stared at the SQL til my right eye went blind (honest)
and it's fine. I also have similar code in a few of my other forms all
working fine. If any of you gurus can shed some light on this it'd be
awesome! Thanks ahead of time!

-Jayyde

 

[Douglas J Steele]

Do you have values for all 3 of index, id and intParentID?

Have you looked in the Errors collection?

Dim errObject As ADODB.Error

For Each errObject In cnn.Errors
Debug.Print errObject.Number & ": " & errObject.Description
Next errObject

That may give you some clues.

 

[Jayyde]

I checked the strSQL as it was entered into .CommandText and it was fine,
had values for all 3. Just did what you suggested and it spit the same
thing back at me along with a number-- -2147467259 (just in case you
recognize it lol)--along with the "ODBC--call failed" and that was it...

Do you have values for all 3 of index, id and intParentID?

Have you looked in the Errors collection?

Dim errObject As ADODB.Error

For Each errObject In cnn.Errors
Debug.Print errObject.Number & ": " & errObject.Description
Next errObject

That may give you some clues.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following code is throwing the ever-informative "ODBC--call failed"
error on the cmd.Execute line of the following code:

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim index As Integer

index = DLookup("[NextPKofCategoryHierarchy]",
"qryCategoryHierarchy")

strSQL = "INSERT INTO dbo_tblProductCategoryHierarchy " _
& "(iProductCategoryHierarchyId, " _
& "iProductCategoryId, " _
& "iProductCategoryParentId) " _
& "VALUES(" _
& index & ", " _
& id & ", " _
& intParentID & ");"

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = strSQL
cmd.Execute

Not sure why, I've stared at the SQL til my right eye went blind (honest)
and it's fine. I also have similar code in a few of my other forms all
working fine. If any of you gurus can shed some light on this it'd be
awesome! Thanks ahead of time!

-Jayyde