SQL: Statement too long. FoxPro 6.0 SQL querry.....
Posted on 2004-04-21
Hi, and thanks in advance for any help.
I have a set of FoxPro free tables that are linked to each other through keys. So, I first populate my first Recordset with an SQL table that matches the date the user puts in. In that table there are the File Id's that allow finding the correct data in each of the other tables. So, I just loop through all the file ID's in the first table and create an SQL querry in a loop to get the data in the other tables. This works great until I get too many records, and when that happens I get this Error. Even on a Querry whos length is >5000. :S Here is a rough loop to show what I'm doing. Kinda new to the whole programming thing, so if you could keep the answers kinda simple I would appreciate it.
MainSqL = "where (FILE_ID = '"
For I = 1 To FUN.Recordset.RecordCount
If I = FUN.Recordset.RecordCount Then
MainSqL = MainSqL & FUN.Recordset.Fields("file_id") & "')"
MainSqL = MainSqL & FUN.Recordset.Fields("file_id") & "') OR (FILE_ID = '"
GEN.RecordSource = "SELECT * FROM GEN " & MainSqL
DOC.RecordSource = "SELECT * FROM DOC " & MainSqL
(EDIT:) Sorry thought I'd better add that I build individual SQL's for each table with the above string.
Probably not the most exact code, so if you have any better suggestions please let me know. Also, I did try loading the entire database into an ADO control, but it had all sorts of problems with it. That is why I ended up moving to the free table access. Thanks again.