Acc20: Working Querydef's SQL Property Yields Unsupported Join Expression 
Author Message
 Acc20: Working Querydef's SQL Property Yields Unsupported Join Expression

Thanks in advance to all who read this posting!  Please reply to

base article.  I've searched the Access Knowledgebase for "sql property"
and "join expression not supported", but could not find any related
articles.  This is an Access 2.0 problem.

Problem Description:
-----------------------------
I have a working select query object which I can run from the Database
window, from the query's Design View QBE grid, and from the query's Design
View SQL window.  When I establish a querydef object as follows:

        Dim ws As WorkSpace, db As Database, q As QueryDef
        Set ws = dbEngine.Workspaces(0): Set db = ws.Databases(0)
        Set q = db.QueryDefs("MyWorkingQuery")
        Dim Sql As String: Sql = q.Sql

the value of Sql (and q.Sql) yields SQL which is different from that which
appears in the query's Design View SQL window.  The SQL yielded by the code
above is invalid, and when copied and pasted into the Design View SQL
window of a new query, produces the following compilation error: "Join
expression not supported".  The compilation error occurs with any attempt
to switch to the Design View QBE grid or run the query.  

My application pastes the SQL to a range of cells in Excel, where it is
used by Excel's PivotTableWizard's SourceData property.  The invalid SQL
property value causes the PivotTableWizard method to fail by reason of no
data retrieved.

More information that might be meaningful:
-------------------------------------------------------------
The SQL string is very long, too long to post here if no one responds.  But
the query joins three tables with inner joins.  I began receiving the "Join
expression not supported." error only recently, after I added a field to
each of the joins.  (the tables were joined A-B-C on a single field.  I
found as my test data grew that I was getting duplicate records and needed
to better qualify the join, I connected the tables in the QBE grid with an
additional field).  This did not invalidate the query; indeed this revised
query is the working query mentioned above.  But this revision did
precipitate the problem.  Lastly, the query is a parameterized query with
two parameters.

The join clauses for comparison:
----------------------------------------------
The join clause from the working query is:
FROM (tblSheetObject INNER JOIN tblWorksheet ON (tblSheetObject.ObjectName
= tblWorksheet.ObjectName) AND (tblSheetObject.LocationID =
tblWorksheet.LocationID)) INNER JOIN tblSheetObjectSeries ON
(tblSheetObject.ObjectName = tblSheetObjectSeries.ObjectName) AND
(tblSheetObject.LocationID = tblSheetObjectSeries.LocationID)

The join clause from the SQL property is:
FROM (tblSheetObject INNER JOIN tblWorksheet ON tblSheetObject.ObjectName =
tblWorksheet.ObjectName) INNER JOIN tblSheetObjectSeries ON
(tblSheetObject.LocationID = tblSheetObjectSeries.LocationID) AND
(tblWorksheet.LocationID = tblSheetObject.LocationID) AND
(tblSheetObject.ObjectName = tblSheetObjectSeries.ObjectName)

My known workaround:
--------------------------------
I can (and will unless a correction is found) remove the second join and
use a where clause to ensure that the additional qualifying fields match,
so that duplicates are avoided.  This is slower, however, and also less
obvious than a straight join relationship.

If you've gotten this far, you're a real trooper!  Thanks, and please let
me know if you can point me to a fix or an article.

--
Paul Timothy (Tim) Patton
Shell Services Company



Mon, 25 Oct 1999 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. My Join Doesn't work - sql against cdb database

2. QueryDef and SQL JOINs

3. Help? QueryDef parameters don't work

4. Error 20639: SQL Expression error - Error in creating SQL Expression COM Parser (cpeaut32)

5. VB6 Text property of UserControl does not work with UpDown Control's buddy property

6. My expressions won't work (simple left or between functions)

7. table join with a sum works in sql

8. Help! Multiple Joins Aren't Working

9. HELP: SQL LEFT JOIN not working

10. where does querydef store expression fields?

11. Join QueryDef with two datasources

12. Webpage OCX boolean properties show unsupported variant type when put in VB6

 

 
Powered by phpBB® Forum Software