Passthrough Query

[TexRanger]

I developed the attached query in Access QBE,
How do I convert this query to a passthrough query

 

[billmeye]

With PassThrough queries you need to pass exact data as the server does not understand references to forms etc. For the actual control names, surround them with ` (accent mark, not appostrophy, the character that is the upper left key on your keyboard). For the values, use apostrophe regardless of data type. So,

UPDATE `contacts`
INNER JOIN `contactlocation` 
ON `contacts`.`ContactId` = '100'
SET 	`contacts`.`AgentId` = 'John Doe',

 

[TexRanger]

Dear Pat
The only reason, for using a pass through update queries is because the BE is mysql db, and I am facing problems trying to update the required fields from within access.

 

[Steve R.]

The only reason, for using a pass through update queries is because the BE is mysql db, and I am facing problems trying to update the required fields from within access.

How so?
I assume that your are using an ODBC connection. So the issue could potentially be with the configuration in the ODBC connection.
I have an Access front end with a MySQL back end, that is working as expected.

 

[Steve R.]

ODBC tables must have primary keys or unique indexes to allow Access to update them. Permissions can also be an issue if you don't have authorization to update the data. Aside from these two issues, I'm not sure why you would have a problem.

For me, the MySQL ODBC configuration requires that the box "all affected rows" be checked. Otherwise I get an error message that "another user has changed the data" and the proposed revisions are cancelled.

 

[TexRanger]

hi,
I am enclosing a snapshot of the MYSQL odbc config, I am not very sure if this would help, but my sincere request to all to have a look at the attachment and help me, understand or figureout the problem area

many thanks once again, in advance

 

[TexRanger]

oops sorry, i missed out the attachments will upload it as soon as i get home. Access returns a ÏNSERT FAILED error, only when i try to insert a new record into the mysql table, if the record already exists, then i am allowed to edit and save / update the record without any problem

 

[TexRanger]

freinds here is the mysql odbc config kindly advise if any changes are required

 

[TexRanger]

pat,
many thanks to you, went through your post, and got the udpates part working flawlessly, how ever, i have attached the mysql config files so may be you can suggest changes to mysql odbc driver. As you, even i am at sea, as to why i should need a pass through query. howver it is the only option that seems to be working.

 

[TexRanger]

It seems that the mysql config, is the source of all the problems, I am using MYSQL Server 5.x on LAN, so would appreciate it you all could look at the configs, or alternativly provide me with config that works.

many thanks

 

[Steve R.]

It seems that the mysql config, is the source of all the problems, I am using MYSQL Server 5.x on LAN, so would appreciate it you all could look at the configs, or alternativly provide me with config that works.

You state that the MySQL server is on a LAN. So I assume that you are attempting to access it remotely from a remote computer. Given that, the ODBC connection should NOT be "localhost". Use the name of the computer on which MySQL resides in the ODBC connection box. (I will assume that you have successfully connected to MySQL which may make some of my remarks unnecessary.)

As for the my.cnf file, comment-out all the bind-addresses. This "forces" MySQL to listen to the network. The default configuration is for MySQL to listen to the "localhost", which is the computer that MySQL resides on.

Of course you will need to configure MySQL (Use phpMyAdmin) to allow connections from other computers located on the network.

Here is a link to the MySQL forum. Unfortunately, the forum has not proven to be that useful from my perspective. But in searching it you may find some useful information. Here is one "sticky" topic: Using Microsoft Access as a Front-end to MySQL.

On the ODBC Connection box, you have a lot more check boxes marked that I do, I can't comment on them since I have have not experimented with them to confirm or dismiss their respective usefulness.