473,836 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using Execute Method, Update becomes Select Query

34 New Member
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
3 9128
PianoMan64
374 Recognized Expert Contributor
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
RAG2007
34 New Member
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
RAG2007
34 New Member
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

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

Similar topics

4
5064
by: Thomas Scheiderich | last post by:
Why would you use the Recordset object over the Execute method of getting data from your Sql database. For example, I have the following: Execute Method *************************************************************** Set connectionToDatabase=Server.CreateObject("ADODB.Connection") connectionToDatabase.ConnectionTimeout = 60 connectionToDatabase.Open "DSN=Customer;user id=SA;password="
6
4044
by: PG | last post by:
When deleting a row from the database, that id is now missing. So what I'm trying to do is update in a loop (maybe an sql loop if there is one) of all of the id numbers - sort of like renaming them. It did partly work because all the id's were set to 22. Thats because there was 22 rows. Here's the code I used: $result = mysql_query ("SELECT * FROM counter"); for($counting = 1; $row = mysql_fetch_row ($result); ++$counting)
0
2429
by: Marko Poutiainen | last post by:
Situation: We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish, Swedish or English). There are about a dozen tables with columns that need localization. Doing this in the application level was a no-goer. It would have taken far too much time (there is a *lot* of code and unfortunately most of the...
1
2952
by: Joris Kempen | last post by:
Hi people, I know that the question has come around sometimes: How to open an Access Report using ASP and export it to for example RTF. I'm trying to implement the first method of David Cline: http://www.15seconds.com/issue/981216.htm
4
6481
by: banz | last post by:
Hello I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to define the servername / hostname in my Perl Progrem.. Here is the code:
5
4340
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one child table (StudentProgress). The course progress records how a student progresses on a course. I have one course (History) and one student called Maya. I now want to record her grade (64). If I do this in Access using a form, then the form...
10
12725
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be certain that MyVar will be set to the key-field value that was created when the Append query ran. Now, there are other ways to do it - I know - that will ensure you 'nab' the correct record. But I was wondering
9
4808
by: =?iso-8859-1?B?Sm/jbyBNb3JhaXM=?= | last post by:
Hi there guys, My doubt is related with MySQL and Transactions (InnoDB) so here it goes: I have a query like this: $query = 'START TRANSACTION; '; $query .= 'UPDATE sections '; $query .= 'SET position=position-%d ';
0
4136
by: David | last post by:
- Are there any peculiarities with using curs.executemany(...) vs. multiple How many times are you calling execute vs a single executemany? The python call overhead will add up for thousands of calls. The relevant source code is here if you're interested: http://svn.python.org/projects/python/trunk/Modules/_sqlite/cursor.c
0
9670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9374
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5649
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4452
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.