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

Topic # 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

Reply # 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 »

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 »

Orcon announces new always-on internet service for Small Business
Posted 18-Apr-2019 10:19

Spark Sport prices for Rugby World Cup 2019 announced
Posted 16-Apr-2019 07:58

2degrees launches new unlimited mobile plan
Posted 15-Apr-2019 09:35

Redgate brings together major industry speakers for SQL in the City Summits
Posted 13-Apr-2019 12:35

Exported honey authenticated on Blockchain
Posted 10-Apr-2019 21:19

HPE and Nutanix partner to deliver hybrid cloud as a service
Posted 10-Apr-2019 21:12

Southern Cross and ASN sign contract for Southern Cross NEXT
Posted 10-Apr-2019 21:09

Data security top New Zealand consumer priority when choosing a bank
Posted 10-Apr-2019 21:07

Samsung announces first 8K screens to hit New Zealand
Posted 10-Apr-2019 21:03

New cyber-protection and insurance product for businesses launched in APAC
Posted 10-Apr-2019 20:59

Kiwis ensure streaming is never interrupted by opting for uncapped broadband plans
Posted 7-Apr-2019 09:05

DHL Express introduces new MyDHL+ online portal to make shipping easier
Posted 7-Apr-2019 08:51

RackWare hybrid cloud platform removes barriers to enterprise cloud adoption
Posted 7-Apr-2019 08:50

Top partner named at MYOB High Achievers Awards
Posted 7-Apr-2019 08:48

Great ideas start in Gisborne with hackathon event back for another round
Posted 7-Apr-2019 08:42


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.