[AccessD] Access to Excel 65K row barrier

Paul Hartland paul.hartland at googlemail.com
Wed Apr 2 19:44:17 CDT 2014


I used to use CopyFromRecordset all the time used to work for all my needs
and was very fast as far as I recall.

Paul


On 3 April 2014 00:51, Stuart McLachlan <stuart.mclachlan at gmail.com> wrote:

> What is this CopyFromRS  you speak of?
>
> Access doesn't seen to know about it     :)
>
>
> On 2 Apr 2014 at 23:37, Darryl Collins wrote:
>
> > 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
> >
> > --
> > 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
>



-- 
Paul Hartland
paul.hartland at googlemail.com


More information about the AccessD mailing list