Using Execute Method, Update becomes Select Query

[RAG2007]

I'm using the QueryDef and Execute method to update a record in my MySQL backend.

Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use the Execute Method.

Instance:
The following code is defined as a query called "AddCost"

UPDATE tblinitiative SET tblinitiative.Estimate = " & Estimate & ", tblinitiative.FinalCost = " & FinalCost & " WHERE (((tblinitiative.InitID)=" & InitID & "));

When I do this:

CreateSPT "Addcost", str, "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0;UID=user"
Set qdf = db.QueryDefs("Addcost")
qdf.Execute

At the qdf.Execute I get the message cannot execute a Select query. Indeed, looking at the created query, it has become a SELECT query, not an UPDATE query.

Are Update queries considered Select queries? And if so, how can I set it up to use the Execute method as to update a record using passthrough query?

In normal circumstances, not using ODBC, I know I could do it another more simple way, but as I've scoured the web, using a passthrough query and execute methods are the only ways to get around another problem linking MySQL Backend and MS Access Front end.

Help!

[PianoMan64]

You could just simply do a Docmd.RunSQL and between the " " you would add your Update SQL String.

The other option if you're wanting to control an active Recordset in ADO using MySQL, you would need to do the following code example

 
Sub DemoUpdate()
          Dim cn as ADODB.Connection
          Dim cmd as ADODB.Command
          Dim rs as ADODB.Recordset
 
          Set cn = New ADODB.Connection
          Set cmd = New ADODB.Command
          Set rs = New ADODB.Recordset
 
          With cn
             .ConnectionString = "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0  ;UID=user"
          End with
 
    With cmd
            Set .ActiveConnection = cn
            .CommandText = "{SELECT STATEMENT HERE}"
            .CommandType = adCmdText
    End With
 
   set rs = cmd.execute
 
'You can also set the rs to the Forms Recordset Object by adding the following line of code
    set me.recordset = rs
end sub
 

[RAG2007]

How do I do Docmd.RunSQL making it Pass-Through?

I'll try the ADO version as well. Which do you think is better/stronger/faster, or is it two ways to get the same result?

Thanks

You could just simply do a Docmd.RunSQL and between the " " you would add your Update SQL String.

The other option if you're wanting to control an active Recordset in ADO using MySQL, you would need to do the following code example

 
Sub DemoUpdate()
          Dim cn as ADODB.Connection
          Dim cmd as ADODB.Command
          Dim rs as ADODB.Recordset
 
          Set cn = New ADODB.Connection
          Set cmd = New ADODB.Command
          Set rs = New ADODB.Recordset
 
          With cn
             .ConnectionString = "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0  ;UID=user"
          End with
 
    With cmd
            Set .ActiveConnection = cn
            .CommandText = "{SELECT STATEMENT HERE}"
            .CommandType = adCmdText
    End With
 
   set rs = cmd.execute
 
'You can also set the rs to the Forms Recordset Object by adding the following line of code
    set me.recordset = rs
end sub
 

[RAG2007]

The ADO example didn't work, gave me this message:

Run-time error '3709':
Requested operation requires an OLE DB Session object, which is not supported by the current provider.

Is it that MySQL doesn't support this?