Does access 2000 pivot table have a limit on rows

[Guest]

I have Access 2000 and a database that has over 360,000 records. When I try
to run a pivot table form, I get an error in accessing the data. I assume
that since Access 2000 is using an excel object, that the row limit in Excel
is that same for the Access Table. Is this correct?

If I were to upgrade to Access 2003, would this solve the row/record limit
problem in pivot tables (assuming that is the problem here)?

Thanks

 

[dbahooker]

I dont think that there is a row limit for using an Access DB against
an Excel pivotTable.

what error are you getting?

Access does have it's own flavors of pivotTables; they're pretty neat;
and they're only available in the 2002 / 2003 versions; I believe

-Aaron

 

[Mike Labosh]

I have Access 2000 and a database that has over 360,000 records. When I

try
to run a pivot table form, I get an error in accessing the data. I assume
that since Access 2000 is using an excel object, that the row limit in
Excel
is that same for the Access Table. Is this correct?

1. What is the error? To get an intelligent response from us, we need that
error message.

2. I personally have not worked much with an Excel Pivot Table, but Access
has something very similar that it calls a Crosstab Query. You could try
that. It has no row limitations that I am aware of, but it doesn't have the
cool expandable drill-down features that the Excel Pivot Table has. You
might also try building a Select Query in Access that presents the
recordbase that the Crosstab / Pivot Table would use, and have Excel build a
Pivot table sourced directly from that query instead of a series of tables.

3. Access limits and Excel limits are *very different*. And it doesn't
matter which version of Office you have. It is because of the differences
in the inherent natures of databases and spreadsheets. A database assumes
there is no logical limit to the numer of rows. A spreadsheet assumes that
the user does not need an unlimited number, and therefore, imposes a limit.
Excel can handle up to 65,536 rows. Access is limited only to the maximum
allowable size of an MDB file, and there are filthy McGyverized hacks to get
around that.

If I were to upgrade to Access 2003, would this solve the row/record limit
problem in pivot tables (assuming that is the problem here)?

Nope. Upgrading Excel might, but I doubt it. IMHO, your problem is
architecture.

--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Veritas e aequitas; in nominae Patri, et Fili, et Spiritus Sancti."
-- The Boondock Saints

 

[Guest]

Thanks for your comments Mike. I will try to elaborate.

I have a table in MS Access 200 (9.0.8948 SP-3) that has eight columns. The
columns are text (even though some only contain numbers). There are no Null
or empty values, no missing data, and no negative numbers or calculations.
There are about 360,000 records in the table.

When I try to run the Pivot Table wizard, I push all 8 field into the
"Fields Choosen for Pivoting" list, and hit the 'next' button.

The next form of the wizard has several buttons, including "Layout ..." and
"Finish". If I click the "Layout ..." button, I get the message: "Microsoft
Excel: Problems obtaining data".

If I click the "Finish" button, I get the message:
"An error occured while trying to use the Microsoft excel PivotTable wizard.
Microsoft excel returned the following error: 'Problems obtaining data.'"

Since Access is trying to use an Excel Object, and Excel can only handle
65,536 rows, as you said, I thought the problem was a limitation of Excel,
and not Access.

Since it looks like Access 2003 does not use an Excel Object to run pivot
tables, I thought it would not be encumbered with the limit on the number of
rows.

1. So my question is, since I am using a valid table, with no calculations
or anything, why could I be receiving an error?

2. Does the row limit in Excel equate to a row limit in the Excel Object
being used in Access 2000 Pivot tables.

3. Does the Access 2003 software no longer use Excel Objects for Pivot
tables, and if so, would I likely not receive an error when running a pivot
on a table with 360,000 records?

Thanks Mike for your insights. Hopefully, this will help clarify the
question and make it easier to answer.

Robert

 

[dbahooker]

so only drag 6 columns over

for the record; Excel pivotTables aren't as powerful as Olap
PivotTables; accessible through offline cubes or an Analysis Services
server.

I reccomend offline cubes; since you're using Access; my brother had
quite a bit of success with that combo; they're just not as fast as
Analysis Services is why I didnt get into them.

Even with 8 measures though-- all on rows; you won't have very good
performance

when you're trying to display the pivotTable you should display less
and let us know if it works.

in effect; you CAN have a pivotTable against a huge database and you
won't have problems... until you try to display more than 64,000 rows
in your actual pivotTables.

you should drill up in other words; not display all the dimensions at
once.

you should have the same results with nearly any version of Access I
believe.
I just think that you need to make sure to set it up as Access instead
of a linked sheet to an access query for example... directly against
Access ODBC or Ole DB or something

-Aaron

 

[frank]

hi,

I met a problem when i try to show my table or query in pivotchart and
pivottable, the program shows "set property can not set one or more
property", anybody know what's the problem.

Thanks.

Frank

 

[frank]

hi,

I met a problem when i try to show my table or query in pivotchart and
pivottable, the program shows "set property can not set one or more
property", anybody know what's the problem.

Thanks.

Frank