In Excel VBA, I've established an ODBC connection to a SQLServer db, and I can do a

successful SELECT query, but I'm having trouble doing an UPDATE query. When I run the

following code, I get the error, "Cannot Execute a Select query."


Dim dbsCurrent As Database
Dim qdfChange As QueryDef
Dim rs1 As Recordset
Dim sqlStmt As String

sqlStmt = "UPDATE DATA_99.dbo.IMINVLOC_SQL SET std_cost = .550000 WHERE

(IMINVLOC_SQL.loc='LYN') AND (IMINVLOC_SQL.prod_cat Not In ('RM','PG'))"

Set dbsCurrent = OpenDatabase("VW")

Set qdfChange = dbsCurrent.CreateQueryDef("", sqlStmt)

With qdfChange
.Connect = "ODBC;DATABASE=DATA_02;DSN=LabelData;UID=sa;PW D="
.Execute
End With

dbsCurrent.Close

Okay, so I guess I need to change the QueryDef type to dbQUpdate, but I can't figure

out how to do this. For example, adding this code produces the error, "Invalid use of

property":
Set qdfChange.Type = dbQUpdate

Has anyone come up with a solution to this? Thank you.