Bill Benson
bensonforums at gmail.com
Wed Apr 2 20:24:05 CDT 2014
Yes I agree I like that method. However it is an Excel method of the Range object so you would have to automate Excel using a ADO or DAO recordset. However that will still overrun the excel 2003 worksheet so one would have to automate Excel 2007+, which Bob says he does not have .... Writing from Access to a text file was, I believe, Bob's only option... as has already been suggested. I have had problems with tab separated text files, but obviously they have a fan in Stuart. On Apr 2, 2014 8:45 PM, "Paul Hartland" <paul.hartland at googlemail.com> wrote: > 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 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >