SQL command problem

J

Jeff McKay

Hi, I am porting an ODBC application that currently uses SQL Server, so that
a MS Access database is the back end instead. It is mostly working but I am
having
problems with one particular SELECT command. Here it is:

SELECT MailData.MessageKey, Subject, SenderName, SenderEmail, MessageDate,
Priority, Replied, Forwarded, MessageRead, MessageID, Codepage, TimeZone,
Selected, RecipientKey, RecipientName, RecipientEmail, RecipientType,
AttachmentKey, AttachmentName, AttachmentFile, AttachmentDate,
AttachmentSize, MessageText, MessageHtml, MessageHeaders, AttachmentData
FROM MailData LEFT OUTER JOIN Recipients ON MailData.MessageKey =
Recipients.MessageKey LEFT OUTER JOIN Attachments ON MailData.MessageKey =
Attachments.MessageKey WHERE FolderKey = 4

The error message I get back is:

Syntax error (missing operator) in query expression 'MailData.MessageKey =
Recipients.MessageKey LEFT OUTER JOIN Attachments ON MailData.MessageKey =
Attachments.MessageKe'

I get the same error whether I do this via ODBC or enter a query in the
Access UI.
It does appear that maybe the SQL engine is cutting off the expression - is
there a
maximum length? If so is there a way I can cut this down to size without
losing any
variables? I'm not sure if I can do 'Select *' because in ODBC I need to
bind the
column names to variables in my program. I did an experiment where I cut
out
the second outer join and it worked, but of course, I cannot do that in my
application.
Also, the above command works fine when communicating with SQL Server.
 
J

John W. Vinson

Syntax error (missing operator) in query expression 'MailData.MessageKey =
Recipients.MessageKey LEFT OUTER JOIN Attachments ON MailData.MessageKey =
Attachments.MessageKe'
Click to expand...

JET is pickier about parentheses than is SQL/Server. Try

SELECT MailData.MessageKey, Subject, SenderName, SenderEmail, MessageDate,
Priority, Replied, Forwarded, MessageRead, MessageID, Codepage, TimeZone,
Selected, RecipientKey, RecipientName, RecipientEmail, RecipientType,
AttachmentKey, AttachmentName, AttachmentFile, AttachmentDate,
AttachmentSize, MessageText, MessageHtml, MessageHeaders, AttachmentData
FROM (MailData LEFT OUTER JOIN Recipients ON MailData.MessageKey =
Recipients.MessageKey) LEFT OUTER JOIN Attachments ON MailData.MessageKey =
Attachments.MessageKey WHERE FolderKey = 4


i.e. enclose the first pair of joined tables in parentheses to make one
composite object, and then left join the other table to that object.

John W. Vinson [MVP]
 
Top