[AccessD] Access to Excel 65K row barrier

Bill Benson bensonforums at gmail.com
Wed Apr 2 21:14:11 CDT 2014


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.
>
>


More information about the AccessD mailing list