Access 2003 - Columns and Rows

[goldenbear]

What is the maximum number of columns and rows in any one table?

[Rabbit]

Microsoft Access Specifications
You are unlimited in the number of records you have, but you have a size limit.

[goldenbear]

Microsoft Access Specifications
You are unlimited in the number of records you have, but you have a size limit.

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?

[Rabbit]

Well, if you went to the link, it'd give you the information you needed. 255 columns and 2 gigabytes per database.

[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)>

[goldenbear]

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!

[Rabbit]

Not a problem, good luck.

[goldenbear]

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!

[goldenbear]

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?

[Rabbit]

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.

[goldenbear]

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?

[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.

[goldenbear]

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!

[Rabbit]

Not a problem, good luck.