Re: Stumped by Union Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Error 3146 means that MySQL has generated an error, not Access. It's
impossible to tell from the Access SQL what the MySQL ODBC driver has done
(unless anyone around here has intimate knowledge of the thing, which is
unlikely). Your mission is to find out exactly what SQL actually arrives at
the MySQL server, and what is wrong with it, and for that you need to be
asking MySQL experts.

"Biz Enhancer" <BizEnhancer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46177743-08B7-4D1E-A315-C63B00902793@xxxxxxxxxxxxxxxx
Hello learned guru's, boffins and hackers,

I have a perplexing problem.

The object of the exercise is to transpose the fields of one record into a
listbox. Using a UNION query I am able to get two SELECT queries into the
listbox but when I add more I get an" ODBC - call failed" error.

Listbox.rowsource =
SELECT clientsub.clisid, "First Name: " AS f1, [Fname] AS f2, "Fname" AS
flit FROM clientsub WHERE
(((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Last Name: " AS f1, [Lname] AS f2, "Lname" AS
flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Position: " AS f1, [Position] AS f2, "Position"
AS
flit FROM clientsub WHERE
(((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Location: " AS f1, [Location] AS f2, "Location"
AS
flit FROM clientsub WHERE
(((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Work Phone: " AS f1, [Wphone] AS f2, "Wphone" AS
flit FROM clientsub WHERE
(((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Mobile Phone: " AS f1, [mphone] AS f2, "mphone"
AS
flit FROM clientsub WHERE
(((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Email: " AS f1, [Email] AS f2, "Email" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]));

Any two SELECT statements joined with a UNION work fine, however add a
third
and the failure occurs.

Access 2003 linked via MySQL ODBC 3.51 driver into MySQL Server 5.0.21

I can not figure out why the error is occuring. Can anyone shed some light
on this problem?

Thanks in advance

Nick.


.

Relevant Pages