SQL Syntax - Parsing code Vs VBA

[Guest]

The below is the equivalent SQL created by Access by parsing from the query
that I made from the user interface.

SELECT Gate_Data.page_1, Gate_Notes!note_num & ") " & Gate_Notes!note AS
Note1
FROM (Gate_Data INNER JOIN Gate_Note_tbl ON Gate_Data.page_1 =
Gate_Note_tbl.Page_1) INNER JOIN Gate_Notes ON Gate_Note_tbl.note_num =
Gate_Notes.Note_num ORDER BY Gate_Data.page_1, Gate_Notes!note_num & ") " &
Gate_Notes!note;

It is to be noted that the connector "." is used by the parsing code and
expressions created by the 'build' code uses "!" connector.

The VBA string that I am using to create multiple queryDefs using variables

str1 = "SELECT " & new_d & "!page_1, " & new_notes & "!note_num, note AS
Note1 " _
& "FROM (" & new_d & " INNER JOIN " & new_note_tbl & " ON " & new_d & "" _ &
"!page_1 = " & new_note_tbl & "!Page_1) INNER JOIN " & new_notes & "" _ & "
ON " & new_note_tbl & "!note_num = " & new_notes & "!Note_num " _ & "ORDER BY
" & new_d & "!page_1, " & new_notes & "!note_num, note;"

Set qdfNew = dbsTemp.CreateQueryDef(new_q, str1)

When the code step runs this is result of the str1

SELECT Globe_Data!page_1, Globe_Notes!note_num, note AS Note1 FROM
(Globe_Data INNER JOIN Globe_Note_tbl ON Globe_Data!page_1 =
Globe_Note_tbl!Page_1) INNER JOIN Globe_Notes ON Globe_Note_tbl!note_num =
Globe_Notes!Note_num ORDER BY Globe_Data!page_1, Globe_Notes!note_num, note

seems to match the original expression created by the parsing code, except
that the variables replace the 'gate' with 'globe', and the small difference
in the ORDER BY expression.

However the query fails to run from within VBA with the error

"Join expression not supported".

Why does not the VBA support the SQL expression created by the parsing code?

Do I need to redo the SQL using WHERE conditions instead?


Sajit Viswan

 

[Roger Carlson]

Have you tried just using the dot (.) instead of the bang(!) in your SQL
statement?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tim Ferguson]

SELECT Globe_Data!page_1,
Globe_Notes!note_num,
note AS Note1

FROM (
Globe_Data
INNER JOIN Globe_Note_tbl
ON Globe_Data!page_1 = Globe_Note_tbl!Page_1
)
INNER JOIN Globe_Notes
ON Globe_Note_tbl!note_num = Globe_Notes!Note_num

ORDER BY Globe_Data!page_1,
Globe_Notes!note_num,
note

seems to match the original expression created by the parsing code

No it doesn't. You have changed a number of periods (.) to exclamation
points (!) -- but you already know this because you mentioned it higher
ip in the message.

It is kind of interesting to note the Jet complains first about the
illegal characters in the JOIN...ON clause; if it had not fallen over
there, next would probably have been the errors in the SELECT list, and
last would have been in the ORDER BY clause. Any way you look at it, this
is rubbish SQL: why did you think it would be legal?

Put the exclamations back to periods and it should work as expected.

FWIW, a bang operator(!) is a valid function in VBA, but this is not VBA.

Best wishes


Tim F