Windows 2000, VFP 6.0 with SP 5 Installed, using SQL Server 2000.

Experience: Many reports requiring one table have been created and the users really like them.

Desired: Create a SQL statement using 6 tables to be used in a more complex report.


Tried: SQL Pass through.
Results: Only able to use one table.

Scenario 1.

Code:
*Simplified.

Load:

STORE SQLSTRINGCONNECT('Driver={SQL SERVER};Server=N103;Database=Development;uid=User;pwd=User');
TO tConnection
IF tConnection < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF

Thisform.pConnection = tConnection


Method:

*Simplified.
*A filter is built called lcFilter

thisform.grdData.recordsource = SPACE(0)

tConnection = Thisform.pConnection

SQLEXEC(tConnection, 'SELECT ReworkNumber, SupplierCode, SupplierName, ;
PartNumber, PartName, Kanban, Problem, ;
Model, DateCreated ;
FROM Tally ;
&lcFilter ORDER BY ReworkNumber DESC ', 'rvLineCall')

*This works fine. It you precede more than one field name with the *table name it bombs.
*If you try to use more than one table it bombs.
*It works great for one table!

Next thought: Use VFP with ADO. I have done this for a number of years again with one table.

Scenario 2.

Code:
*Simplified.

Load:

STORE SQLSTRINGCONNECT('Driver={SQL SERVER};Server=N103;Database=Development;uid=User;pwd=User');
TO tConnection
IF tConnection < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF

Thisform.pConnection = tConnection


Method:

*Simplified.
*A filter is built called sString

thisform.grdData.recordsource = SPACE(0)

tConnection = Thisform.pConnection

# DEFINE adOpenStatic 3
# DEFINE adLockOptimistic 3



* Create object...
oRecordSet = CreateObject("adodb.RecordSet")

oRecordSet.Open(sString, tConnection, adOpenStatic,adLockOptimistic)



*Create a Cursor
*Fill Cursor from a For Loop.




Scenario 2 has worked in the past with single tables using ODBC but this does not work. I have considered a remote view but will save that till last.

Thank you for any thoughts or ideas.

Tom