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',

 

[Pat Hartman]

Pass-Through queries MUST use the actual SQL Syntax of the target database so if your database is Oracle, the syntax might be slightly different than if it were SQL Server.

You actually only NEED to use the accent marks if you use names that include spaces or special characters.

Here's a stab at what you might need:

Dim strSQL As string
strSQL = " UPDATE dbo.contacts c "
strSQL = strSQL & " INNER JOIN dbo.contactlocation l  ON c.ContactId = l.ContactId "
strSQl = strSQL  & " SET 	c.AgentId = '" & [Forms]![ContactMain]![AgentId] & "'" 
strSQL = strSQL & " 	,c.ContactId = '" & [Forms]![ContactMain]![ContactId] & "'"
strSQL = strSQL & "	,c.ContactType = '" & [Forms]![ContactMain]![ContactType] & "'"
etc.

Once you have created the string, you can run it or save it and then run it. You have to create this query with VBA because pass through queries cannot take parameters.

PS - I don't seen any reason that this actually has to be a pass-Through query if the server-side table is linked.

 

[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.

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

If you are running this query from VBA using DAO or ADO and the table has an identity column as the primary key, you will need to add an argument to your expression.

qd.Execute dbSeeChanges
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

 

[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.