By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,318 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,318 IT Pros & Developers. It's quick & easy.

Using Execute Method, Update becomes Select Query

P: 34
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"
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblinitiative SET tblinitiative.Estimate = " & Estimate & ", tblinitiative.FinalCost = " & FinalCost & " WHERE (((tblinitiative.InitID)=" & InitID & "));
When I do this:
Expand|Select|Wrap|Line Numbers
  1. CreateSPT "Addcost", str, "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0;UID=user"
  2. Set qdf = db.QueryDefs("Addcost")
  3. 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!
Jan 14 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 374
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub DemoUpdate()
  3.           Dim cn as ADODB.Connection
  4.           Dim cmd as ADODB.Command
  5.           Dim rs as ADODB.Recordset
  6.  
  7.           Set cn = New ADODB.Connection
  8.           Set cmd = New ADODB.Command
  9.           Set rs = New ADODB.Recordset
  10.  
  11.           With cn
  12.              .ConnectionString = "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0  ;UID=user"
  13.           End with
  14.  
  15.     With cmd
  16.             Set .ActiveConnection = cn
  17.             .CommandText = "{SELECT STATEMENT HERE}"
  18.             .CommandType = adCmdText
  19.     End With
  20.  
  21.    set rs = cmd.execute
  22.  
  23. 'You can also set the rs to the Forms Recordset Object by adding the following line of code
  24.     set me.recordset = rs
  25. end sub
  26.  
Jan 15 '08 #2

P: 34
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub DemoUpdate()
  3.           Dim cn as ADODB.Connection
  4.           Dim cmd as ADODB.Command
  5.           Dim rs as ADODB.Recordset
  6.  
  7.           Set cn = New ADODB.Connection
  8.           Set cmd = New ADODB.Command
  9.           Set rs = New ADODB.Recordset
  10.  
  11.           With cn
  12.              .ConnectionString = "ODBC;DATABASE=db;DSN=DB;OPTION=0;PWD=health;PORT=0  ;UID=user"
  13.           End with
  14.  
  15.     With cmd
  16.             Set .ActiveConnection = cn
  17.             .CommandText = "{SELECT STATEMENT HERE}"
  18.             .CommandType = adCmdText
  19.     End With
  20.  
  21.    set rs = cmd.execute
  22.  
  23. 'You can also set the rs to the Forms Recordset Object by adding the following line of code
  24.     set me.recordset = rs
  25. end sub
  26.  
Jan 15 '08 #3

P: 34
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?
Jan 15 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.