Dave,
I don't know why the query crashes, but since you mention that it works with smaller data sets, you may hit the limits of Access?
A few things to try:
- Does the same problem occur if you run the query from Access?
- Maybe you could split the query into two seperate queries? Try reducing the number of records with the first query and then join the second table with the result set of the first query.
HTH
>The following code crashes an instance of VFP 6 SP3 every time.
>
>lcQuery = "SELECT Finder.Source FROM Finder, Orders WHERE Finder.FinderNo = Orders.FinderNo GROUP BY Finder.Source"
>
>lnResult = SQLEXEC(lnConnect,lcQuery,"Sample")
>
>Finder table contains roughly 1,000,000 records.
>Orders table contains roughly 60,000 records.
>All tables are indexed.
>When the tables are smaller the SELECT statement works correctly and retrieves expected results.
>
>I am wondering if there are stored procedures I can execute?
>What about the SQLSTRINGCONNECT command ?
>Is there something in that command that I can change to allow this kind of query to not crash the system...
>
>lcConnect = "DBQ=" + "\\opserver2\data1\opdb\backend\usmts.mdb" + ";" + ;
> "DEFAULTDIR=" + "\\opserver2\data1\opdb\backend\" + ";" + ;
> "Driver={Microsoft Access Driver (*.mdb)};DriverId=281;" + ;
> "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
>
>I tried changing MaxBufferSize to a significantly larger number and the hard drive just kept going and going... until I issued {CTRL-ALT-DLT} and clicked "End Task" command button.
>I tried increasing the size of the 'PageTimeout' command switch.
Daniel