Access 2003 - Columns and Rows | Newbie | | Join Date: Sep 2007
Posts: 28
| | |
What is the maximum number of columns and rows in any one table?
|  | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
Microsoft Access Specifications
You are unlimited in the number of records you have, but you have a size limit.
| | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
THanks for the quick reply! OK, what size and how do i figure that? I have one table with 1MM records and 25 columns and another main table with 5000 records and 100 columns. Most of the data is text and date type. The entire db is 500mb now. I want to add more columns to the 100 column table, will this cause me a problem down the road?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
Well, if you went to the link, it'd give you the information you needed. 255 columns and 2 gigabytes per database.
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,999
| | | re: Access 2003 - Columns and Rows
And if, down the road, you start running up against the 2 gigabyte limit, there are ways around this, without too much pain. You can split your database into frontend/backend, which you probably should do, anyway, with a db this large. Presumably with an app this large you have multiple users. Once spit, you can actually link to tables in multiple backend DBs, each with the 2 gigabyte limit, or you could move your backend to a SQL server. There's really no reliable way to calculate how large an Access db will be using the "I've got X number of records with Y number of columns and I want to add Z more number of columns, how many more records can I add?" There's simply too many variable besides these things that come into play.
Welcome to TheScripts!
Linq ;0)> | | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by Rabbit Well, if you went to the link, it'd give you the information you needed. 255 columns and 2 gigabytes per database. Duh, missed the link..VERY HELPFUL..THANKS!
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
Not a problem, good luck.
| | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by missinglinq And if, down the road, you start running up against the 2 gigabyte limit, there are ways around this, without too much pain. You can split your database into frontend/backend, which you probably should do, anyway, with a db this large. Presumably with an app this large you have multiple users. Once spit, you can actually link to tables in multiple backend DBs, each with the 2 gigabyte limit, or you could move your backend to a SQL server. There's really no reliable way to calculate how large an Access db will be using the "I've got X number of records with Y number of columns and I want to add Z more number of columns, how many more records can I add?" There's simply too many variable besides these things that come into play.
Welcome to TheScripts!
Linq ;0)> I have 30 users. I have considered frontend/backend but I thought there were more liimitations on how and where I can make design changes. I have upsized about a dozen of the largest tables to SQL as a test and so far it seems to be working OK. You have been most helpful.
Love The Scripts!
| | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by Rabbit Not a problem, good luck. I see 255 fields is max. Any downside to adding 50 more to my 5000 records table that has 100 fields?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by goldenbear I see 255 fields is max. Any downside to adding 50 more to my 5000 records table that has 100 fields? Only if you need to run a query on all of the fields. But otherwise, the extra time is negligible. It's definitely going to increase your database size by a bit because even though the field may be blank, Access reserves the space for it. So use the smallest datatypes that will work for you but still give you some leeway.
| | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by Rabbit Only if you need to run a query on all of the fields. But otherwise, the extra time is negligible. It's definitely going to increase your database size by a bit because even though the field may be blank, Access reserves the space for it. So use the smallest datatypes that will work for you but still give you some leeway. OK then..excellent advice. I am trying to decide if I should add the extra fields or create another table in a one-one relationship and add those fields in the new table?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
It shouldn't be a problem to have them in the same table if they belong together. The two reasons you would want to separate them is to get around the 255 limit or for organization's sake. Two separate tables take up more space than one table with all the columns.
| | Newbie | | Join Date: Sep 2007
Posts: 28
| | | re: Access 2003 - Columns and Rows
Quote:
Originally Posted by Rabbit It shouldn't be a problem to have them in the same table if they belong together. The two reasons you would want to separate them is to get around the 255 limit or for organization's sake. Two separate tables take up more space than one table with all the columns. Then I will proceed with one table. It makes it easier that way to clone the form, remove fields I don't need, and add thee new ones that I add. Thanks once again!
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: Access 2003 - Columns and Rows
Not a problem, good luck.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
