Upper row and column limit in an access 2000 table 1

[Queryman]

Does anyone know the upper limit for rows and columns in an access 2000 table



Michael

 

[bluedragon2]

I may be wrong, but I do not know of a limit other then it haveing problems with DB's over 1 gig.

Other then performance issues, I have never came accross any other information on limits for access

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Queryman]

I am trying to merge three tables which have over 150 columns each and I get a message saying too many columns and I was wondering whether the 255 or 256 column limit applies.



Michael

 

[xlbo]

Not as far as I am aware but the memory for each table will be defined by Columns*Rows*DataType. If you have a lot of columns and rows, you may be reaching the limit - especially if you have memo fields or the like

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Queryman]

Any suggestions for merging these three tables, they only have one unique common field, all three have 228 rows and about 150 columns.



Michael

 

[bluedragon2]

How about making three seperate tables and making the common field relational.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Are the columns the same for each table ?? ie is it going to be the rows or columns that are expanding ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Queryman]

The columns are expanding, all three tables have different columns and only one column in common



Michael

 

[xlbo]

From Access help:

Query

Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement approximately 64,000


Looks like the 255/256 limit IS enforced in Access as well :-(

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[AnotherHiggins]

I tried to import a very simple table from Excel - just a number in each cell going across - 256 columns. I received a subscript error. I reduced the columns used to 250 and the table imported just fine. By going into the design view of this table, I quickly learned that the max number of "columns" is 255. So there's the answer to that question.

But I have a question -
I don't understand why you would try to combine three tables that have a common field into one "flat" table. Access' power comes from the fact that it is a relational database. For that matter, I don't understand why you'd even have tables with 150 column headings, but if you're tracking that much information - more power to you. I'd go with Geoff's idea and use a query to combine the information needed. Assign the common column as a Primary Key.

As far as the information a table can hold, we have an Access table with well over 2 million records (rows). It takes a while to query, but it has a LOT of history in one place.

Hope that helps,
John

 

[Queryman]

It's not my idea to create a 228 row table with 350 columns, it's the tail wagging the dog, in this case the users...

I am trying to push Geoff' sidea over, so far looking good!
Thanks everyone!



Michael