Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Forums › Off topic › RESOLVED: MS Access -> Excel - Row Limitations??


397 posts

Ultimate Geek

Trusted

# 17614 4-Dec-2007 12:08
Send private message

I've been using Access to export data to Excel all year... this data has been increasing in size as the project nears completion and now I find that when I export a table (or query) to Excel using the OfficeLinks Tools option "Analyze it with MS Excel" I receive an error message complaining about row limitations. We're only talking about 16,000 - 18,000 rows.

I know that Excel can handle ~64,000 rows when just working within that application. Is there something going on in Access that needs addressing?

Further notes: I'm exporting to Excel so that I can carry out a final review of the data before I convert it to a tab delimited text file, which I then use in an upload tool (for upload into a SAP database). Any option to export direct as a text file? I could then import into Excel...

Using MS Access 2000, Excel 200, XP SP2




Cheers, Stevo

Create new topic


397 posts

Ultimate Geek

Trusted

# 98190 4-Dec-2007 12:23
Send private message

RESOLVED:
When you export to excel using this method MS Access exports it to excel in the 5.0/95 format.
As is discussed here:
http://support.microsoft.com/kb/237576

Further explanation here:
http://support.microsoft.com/kb/201589

Quote:
CAUSE
For backward compatibility, Microsoft Access defaults to the Excel 5.0 specification, which is limited to 16,384 rows. Also, Access does not check the row count before attempting to send the data to Excel.

RESOLUTION
To work around this behavior, use one of the following methods:
• Use Excel to import the data from Access.
• In Access, click Export on the File menu, and then export to Microsoft Excel 97-2000 format. Make sure that the Save formatted check box in the Export Table dialog box in Access is not selected. Then open the file in Excel.
• Limit the number of rows sent to Excel by creating a query in Access and setting the TopValues property to 16,384. Then, analyze the query with Microsoft Excel.

Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

OPPO Reno2 Series lands, alongside hybrid noise-cancelling Wireless Headphones
Posted 24-Oct-2019 15:32

Waikato Data Scientists awarded $13 million from the Government
Posted 24-Oct-2019 15:27

D-Link launches Wave 2 Unified Access Points
Posted 24-Oct-2019 15:07

LG Electronics begins distributing the G8X THINQ
Posted 24-Oct-2019 10:58

Arlo unveils its first video doorbell
Posted 21-Oct-2019 08:27

New Zealand students shortlisted for James Dyson Award
Posted 21-Oct-2019 08:18

Norton LifeLock Launches Norton 360
Posted 21-Oct-2019 08:11

Microsoft New Zealand Partner Awards results
Posted 18-Oct-2019 10:18

Logitech introduces new Made for Google keyboard and mouse devices
Posted 16-Oct-2019 13:36

MATTR launches to accelerate decentralised identity
Posted 16-Oct-2019 10:28

Vodafone X-Squad powers up for customers
Posted 16-Oct-2019 08:15

D Link ANZ launches EXO Smart Mesh Wi Fi Routers with McAfee protection
Posted 15-Oct-2019 11:31

Major Japanese retailer partners with smart New Zealand technology IMAGR
Posted 14-Oct-2019 10:29

Ola pioneers one-time passcode feature to fight rideshare fraud
Posted 14-Oct-2019 10:24

Spark Sport new home of NZC matches from 2020
Posted 10-Oct-2019 09:59


Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron


Updates »

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.