Using UPDATE with ADODB

[dwessell]

Hi,

I'm having a bit of an issue..

I'm using ADODB to connect to a Firebird DB via an ODBC driver. I need to run an UPDATE SQL statement. But am having an issue with the ADODB procedure. This is all in an excel Macro.

Dim dbMain As New ADODB.Connection
dbMain.Open "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;DBNAME=c:\xxxxx.fdb"

dbMain.Execute ("UPDATE stock SET stm_lot = '19113', LOT_APL_RO_COST = 'T' WHERE pn = '" & pn & "' AND stock_line = '" & sl & "' AND serial_number = '" & serial & "'")

I have verified that all of the variables in the SQL statement are correct. And run the corresponding string in iSQL and it works just fine.

Is there another step that I'm missing?

Thanks
David

However, when I use the code above, I get an automation error.

 

[PWise]

try

dbMain.commandtext ="UPDATE stock SET stm_lot = '19113', LOT_APL_RO_COST = 'T' WHERE pn = '" & pn & "' AND stock_line = '" & sl & "' AND serial_number = '" & serial & "'"
dbmain.Execute

 

[dwessell]

I tried that one... I'm told that the argument for dbMain.Execute is not optional..



dw

 

[PWise]

try
dbMain.Execute "UPDATE stock SET stm_lot = '19113', LOT_APL_RO_COST = 'T' WHERE pn = '" & pn & "' AND stock_line = '" & sl & "' AND serial_number = '" & serial & "'

 

[dwessell]

Still with the automation error... I tried a SELECT statement using a recordset, just to test the connection to the DB.. And that worked just fine.

I've take the string that the above code products, and it runs fine with the DB.

The automation error is on the Execute command. I've done this before, and didn't have this issue.. While I don't get to use VBA or ADODB very often.. I've never seen this issue before.. I'm stumped..

dw

 

[JoeAtWork]

The SQL statement looks fine to me (assuming none of the fields you are updating are numeric). Does the error give you any useful information or is it just "Automation error"?

Have you checked the ADO connection's Errors collection? It usually gives more useful information.

 

[Golom]

Is your password really "masterkey" or did you just change it for this post?

I ask because ConnectionStrings.Com uses that in their example but it's probably not the password in most Firebird installations.