Can anyone throw any light on the restrictions of the VFP ODBC Driver with
respect of the number of joins allowed in a SQL Select statement?
I am generating a pivot table in Excel via VFP Automation and whenever I put
an external source SQL select with more than one join the Excel Pivot Table
Wizard call throws an error and I can't work out if it is the ODBC driver
that is erroring out or the Excel Pivot Table Wizard Code (This is not a
wizard as in beginners - it is a proper Excel automation Method)
Setting aSource[2] to the
Anyone any ideas, clues or pointers?
For those of you who want to see the problem I enclose some code - change
the "aSource[2]=" line(s) to an appropriate select statement for your own
test data.
**********
* Start Code
*
* 1. Launch Excel via OLE-Automation.
oExcel = createobject("excel.application")
oExcel.Application.Visible = .T.
* 2. Create a new workbook.
oWorkbook = oExcel.Workbooks.Add()
* 3. Define an Excel range object to dump the results into.
oTargetSheet = oWorkbook.Sheets.Add()
oTargetRange = oTargetSheet.range("A2")
cDatabase="c:\dev\flexipol\flexispec\data\"
* 4. Define ODBC connection string and SQL statement
* that Excel will use to read data.
dimension aSource[2]
aSource[1] = "Driver={Microsoft Visual FoxPro Driver};" ;
+ "SourceDB=" + cDatabase + ";" ;
+ "SourceType=DBF;"
********
* Comment out as appropriate
*
* This works OK as it has only one join in it
aSource[2]= ;
"select all " ;
+"Timesheet_Detail.Primary_Key " ;
+"from Timesheet_Detail " ;
+"join Machine on
Machine.Primary_key=Timesheet_Detail.Primary_Machine_Key " ;
+"Join Timesheet_Header on
Timesheet_Header.Primary_Key=Timesheet_Detail.Primary_Header_Key " ;
+"Where Timesheet_Detail.Primary_Key<100"
* This does NOT works as it has more than one join in it
aSource[2]= ;
"select all " ;
+"Timesheet_Detail.Primary_Key " ;
+"from Timesheet_Detail " ;
+"join Machine on
Machine.Primary_key=Timesheet_Detail.Primary_Machine_Key " ;
+"Join Timesheet_Header on
Timesheet_Header.Primary_Key=Timesheet_Detail.Primary_Header_Key " ;
+"Where Timesheet_Detail.Primary_Key<100"
* 5. Create a pivot table object.
oPivotTable = oExcel.Sheets[1].PivotTableWizard( 2,; && external data.
@aSource, ;
oTargetRange, ;
"PivotTable", .T., .T. )
*
* End of Code
Dave Crozier
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.11.10/943 - Release Date: 08/08/2007
17:38
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
