ODBC Oracle Table, Delete From Excel?
 

Greetings,
A work associate has created an ODBC Excel that populates from an Oracle
table.
We were wondering if it is possible, since the ODBC connection is already
there, to run VB code from Excel to delete a record from the table.

For example, if we encounter a row of data that needs deleted from the
table, and we want to use the cell containing the Part #, how do we tell
Excel to delete the record from the table using the part # as the qualifier?

Thank you for all your help.
Ron

ODBC Oracle Table, Delete From Excel?
 

If you have an ODBC connection you can use it to pass any SQL code recognized
by the ODBC driver. The standard SQL syntax for deleting a record is as
follows:
DELETE tablename.*
FROM tablename
WHERE criteria
I assume you know how to use ADO to set up the connection. Just set up a
string variable to hold the SQL command created as above, using the specific
part# in the criteria, then use the Connection.Execute method to pass the SQL
command to the database. Basic outline of code:

Dim Cn as ADODB.Connection, SQLCmd as String
Set Cn = New ADODB.Connection
Cn.Open(...) ' need to supply the connection string here, assume you have that
' ... Retrieve your records and find the one(s) you want to delete
SQLCmd = "DELETE MyTable.* FROM MyTable WHERE PartNo='" & PartNumber & "'"
'assuming an alphanumeric part number, so I put single quotes around it
Cn.Execute SQLCmd
....
--
- K Dales


"nowon" wrote:

Greetings,
A work associate has created an ODBC Excel that populates from an Oracle
table.
We were wondering if it is possible, since the ODBC connection is already
there, to run VB code from Excel to delete a record from the table.

For example, if we encounter a row of data that needs deleted from the
table, and we want to use the cell containing the Part #, how do we tell
Excel to delete the record from the table using the part # as the qualifier?

Thank you for all your help.
Ron

ODBC Oracle Table, Delete From Excel?
 

Thanks for the response,
I really don't know what the Cn.Open connection string is.
The excel file already makes the connection and already contains the
data from the table.
I open the file and it refreshes with data from the table.
do i still need to use the Cn.Open statement, or could the
Set Cn = Current ADODB.Connection? or something like that.

I only know that it is an oracle table and i have the table name:
REPORT1

Or since the connection is already established, can we just execure the
SQLCmd?

I'm sorry, but Oracle and Databases are all new to me