Bill Benson
bensonforums at gmail.com
Wed Apr 2 23:17:10 CDT 2014
This was a pretty good primer on ADO and the section how to bring in data to a host excel application in part 3 of 4: http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=173:import-export-data-from-access-to-excel-using-ado&catid=79:excel-vba&Itemid=475 On Wed, Apr 2, 2014 at 10:14 PM, Bill Benson <bensonforums at gmail.com> wrote: > Ok I lost my train of thought towards the end. Given that one way or > another I had forced a numeric index field into my select statement, I > would NOT do what I said about using MOVENEXT 65K times, that would void > the value of using MAXRECORDS (I was being stupid). > > I would use MaxRecords as a property of the recordset, use > CopyFromRecordset to put the records on the sheet, and MOVELAST to get the > value the subsequent index field values must exceed in the next recordset, > also with MAX RECORDS set to 65000. > > So I would definitely use MaxRecords property of my recordset so that I > could control the number of records so that CopyFromRecordset will not > blowup the sheet. > On Apr 2, 2014 10:06 PM, "Bill Benson" <bensonforums at gmail.com> wrote: > >> >>'breaking' the CSV / TAB output before you hit the 65545 line limit. >> >> 65535 I assume? >> >> But if writing to a text file for the client to import into Office 2010, >> using methods already described here, there is no such limit, right? >> >> As for writing to Excel using Excel 2003 I had already written about that >> in my first response - did not think I needed to repeat it when I used the >> generic term export. >> >> Thing is, this can be challenging, (as you point out!) Certainly so if >> there is no index to use in the first query. Still doable however. >> >> If no index create a temp table with the same fields and data types >> expected to come from the original query and then append an Auto number >> field using data definition language >> >> CURRENTDB.EXECUTE "ALTER Table TEMP ADD MYINDEX AUTOINCREMENT" >> >> Then INSERT INTO that, and then create the recordset from the temp table >> using bands in the WHERE CLAUSE >> >> 1st >> MYINDEX <=65000 >> >> 2nd >> MYINDEX> 65000 AND MYINDEX <=130000 >> >> etc. >> >> You can of course select max (MyIndex) ahead of time to know how high a >> value to loop until. >> >> A lot of work but fun too. >> >> No other easy way to do it that I can think of without an index. Even >> with an index, the numbers might skip so if you wanted to get exactly 65k, >> you could select all as basis for your recordsey, MoveNext 65k times, note >> the index, next time select all where index > that value, MoveNext 65k >> times, note the index, next time... thereby creating a band on index >> numbers (i would store them in an array) then write some code to pull >> records with these limits in the where clause. >> >> Not sure it is worth using MaxRecords (a property of the recordset object >> in ADO) = 65000 but it might assist. Either way each subsequent fetch uses >> the last index value as something the next group of records must exceed. >> >>