Programs hangs when deleting elements with ADO DB and Oracle

[gernot.bauer]

Hello!

I have a problem concerning ADO DB 2.8 and Oracle 10g. I wrote a small
VBA (v6.0) program to use Excel 2003 SP2 as a front-end to the
database.

I did not encounter problems trying to query the database or update or
insert data by using SQL statements. However, I can't manage to delete
data from the database. Excel hangs when I try to execute the
statement.

The essential part of the code looks as follows:
------------code--------------------
Private connection As ADODB.connection

Public Function GetConnection() As ADODB.connection
If connection Is Nothing Then
Set connection = New ADODB.connection
connection.Open (CONNECTION_STRING)
End If

Set GetConnection = connection
End Function

Public Sub TestDelete()
GetConnection.Execute ("DELETE FROM TEST_SCHEMA.TEST_TABLE")
End Sub
------------code--------------------

I can execute the SQL statement without any problems using TOAD for
Oracle with the same connection parameters I used for this example (in
the connection string which is a const in my code snippet). I have
tried to use the OLE DB provider and the ODBC driver Microsoft offers,
but the result was the same: my VBA program and Excel freeze, and I
have no idea what to do.

I would appreciate any help. This is a crucial part for my program,
and I have already spent much time trying to find a solution for this
problem.

TIA && kind regards,
Gernot

 

[Les]

I believe that you need to add an ADODB command object. My ADO is a little
rusty, but try:

Dim myConnection As ADODB.Connection
Dim myCommand As ADODB.Command
Dim mySql As String

Set myConnection = New ADODB.connection
myConnection.Open (CONNECTION_STRING)

mySql = "DELETE FROM TEST_SCHEMA.TEST_TABLE"

Set myCommand = New ADODB.Command
myCommand.ActiveConnection = myConnection
myCommand.CommandType = adCmdText
myCommand.CommandText = mySql
myCommand.Execute

 

[gernot.bauer]

Hello!

I believe that you need to add an ADODB command object. My ADO is a little
rusty, but try:

[snip Code]

No, that was not the problem. I locked the table by looking at it with
TOAD, which I unfortunately didn't realise until someone told me.
Thank you anyway for your help, I appreciate it very much.

Kind Regards,
Gernot