[AccessD] Access to Excel 65K row barrier

Darryl Collins darryl at whittleconsulting.com.au
Wed Apr 2 18:37:12 CDT 2014


Yes, agreed.  That is one of the better ways to go. I like to use CopyFromRS in many instances as it allows you to move large volumes of data quickly between Access and Excel.    Depends on the task and requirement though.

>From memory it is the "Export To Excel" functionality where the 65545 row limit is bothersome, but I could be wrong.  Never really use that approach so I haven't looked at it for years. 

Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, 3 April 2014 10:32 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access to Excel 65K row barrier

No limit at all if you open the query as a recordset and write each record out sequentially to a 
Tab delimited file.   That is always my preferred method.

--
Stuart

On 2 Apr 2014 at 23:09, Darryl Collins wrote:

> Yes, I think you are correct.  Even today some process will limit you 
> to 65K rows.  Legacy stuff that lurks deep in the machine...
> 
> Heh... took me a while to 'get' the baseball reference, but now I 
> understand :)
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Thursday, 3 April 2014 10:03 AM To: Access Developers discussion 
> and problem solving Subject: Re: [AccessD] Access to Excel 65K row 
> barrier
> 
> Are you sure that he is referring to the catcher and not the pitcher?
> 
> I didn't contradict for once just in case this was a limitation of 
> Access rather than Excel, though my thinking lacks much logic. On Apr 
> 2, 2014 6:48 PM, "Darryl Collins" <darryl at whittleconsulting.com.au>
> wrote:
> 
> > Double??
> >
> > Try 65,545 Rows (2003) vs 1,048,576 Rows (2007+)
> >
> > Much more than double!
> >
> > Cheers
> > Darryl.
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:
> > accessd-bounces at databaseadvisors.com] On Behalf Of John Serrano
> > Sent: Thursday, 3 April 2014 7:24 AM To: Access Developers 
> > discussion and problem solving Subject: Re: [AccessD] Access to 
> > Excel 65K row barrier
> >
> > Bob,
> >
> > The newer version of Excel has double the row size however as time 
> > goes on will this suffice? I guess the real question is why are you 
> > taking data from a query into excel? Do you need to perform 
> > additional analysis on the data or just report it out?
> >
> > Nevertheless, you could change your references in your library to 
> > Office 14.0 I beleive it is...
> >
> >
> > On Wed, Apr 2, 2014 at 4:12 PM, Bob Heygood <accesspro at gmail.com>
> > wrote:
> >
> > >  Hello to the list,
> > >
> > > I have been using the Access 2003 to export the results of a query 
> > > to Excel. All has been well until the query has grown to over 65K 
> > > rows/records. My client has Off 2010.
> > >
> > > I just don't want to upgrade to a newer version of Office.
> > > I have Google docs and SkyDrive office.
> > >
> > > So, the question is how to get him an Excel file in Off 2007 or
> > > 2010 format ? I can't create too much work on his end. He so far 
> > > is not able to splice two files together.
> > >
> > >
> > > TIA
> > >
> > > Bob Heygood
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> >
> > --
> > John Serrano
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list