Prev: SQL ID last login report
Next: Still column space after making row group hidden
From: Martha on 4 Jan 2010 13:08
This question has been asked a couple times, but the answer seems to be unclear so I will ask again.

I have query results in Access that I would like to work with in Excel - I like Excel pivot tables better than Access pivot tables. If I highlight the 287K line Access table, right click on copy, then paste into the 1 million plus row spreadsheet capacity in Excel, it only pastes 65,000 lines. Exports from Access to the million row plus spreadsheet work similarly only exporting 65,000 lines of the much larger table. I can successfully export the data from Access to a text file, then import the text file to Excel and retain all 287K lines, but it would seem the two (Access and Excel) should be able to play together a little more nicely. It is as if Access does not recognize Excels upgraded abilities.

Is there a better way (more automated) of getting more than 65,000 lines from an Access database to a Excel 2007 million row plus spreadsheet?


Submitted via EggHeadCafe - Software Developer Portal of Choice
Test-Driven Development in Microsoft .NET
http://www.eggheadcafe.com/tutorials/aspnet/a20c307c-a1d6-4733-9de0-a14465096ab0/testdriven-development-i.aspx
From: Paul Shapiro on 4 Jan 2010 19:17
You could try linking the Excel sheet as an Access table and using an append
query. I haven't tested, but it sounds like either Access or the Excel
driver are assuming an older Excel version that only supports 64K rows.

"Martha Matthews" wrote in message
news:20101413830mamatthews(a)midamerican.com...
> This question has been asked a couple times, but the answer seems to be
> unclear so I will ask again.
>
> I have query results in Access that I would like to work with in Excel - I
> like Excel pivot tables better than Access pivot tables. If I highlight
> the 287K line Access table, right click on copy, then paste into the 1
> million plus row spreadsheet capacity in Excel, it only pastes 65,000
> lines. Exports from Access to the million row plus spreadsheet work
> similarly only exporting 65,000 lines of the much larger table. I can
> successfully export the data from Access to a text file, then import the
> text file to Excel and retain all 287K lines, but it would seem the two
> (Access and Excel) should be able to play together a little more nicely.
> It is as if Access does not recognize Excels upgraded abilities.
>
> Is there a better way (more automated) of getting more than 65,000 lines
> from an Access database to a Excel 2007 million row plus spreadsheet?

 | 
Pages: 1
Prev: SQL ID last login report
Next: Still column space after making row group hidden