Access Table - mamimum number of fields 1

[AFKAFB]

Hi
I've inherited a DB.
At this stage i cannot change the structure and design as it will affect a rollout date.
Access is not letting me change the data type of some of the fields. All relationships have been removed yet access is saying there are still relatonships.
I've tried creating the table in a clean database but when i try to edit the field types access advises too many fields are defined. What is the maximum number of fields allowed in a db.
regards
chris

 

[Golom]

A table (not a database) is limited to 255 columns but that includes references to columns such as their use in indexes or as sort columns.

You may be prevented from converting data types because of the existence of relations but it may also be because the data currently in the table cannot be converted to the new data type.

Note that relationships can be hidden in the display. Right click and select "Show All" to see all of them.

 

[willir]

Chris

Access is not letting me change the data type of some of the fields.

I've inherited a DB.
At this stage i cannot change the structure and design as it will affect a rollout date.

Ouch.

What is the data type you are trying to change? In order for Access to allow the change, serveral things have to happend...
- Existing data does not break integrity rules in place for the table. For example, if you have string / text entries and are trying to change the field to a date or number, it will fail.

- Existing data does not break integrity rules in place for related tables. For example, deleting a foreign key, the two linked keys being different data types. You covered this -- "All relationships have been removed". How do you know the relationships have all been deleted? Just because the you do not see the links with the GUI Relationship tool does not mean the relationship does not exist. Create a query with what could be the "linked" with another table. When you add the two tables, does a relationship link automatically appear? If so, then the link is still there. You may have to add the tables back to the Relationship GUI tool and delete the relationship.

- Changing the data types does not break limitations within Access.

- You have enough room / temp files / working space to perform the change.

....Work around
Have you tried importing the tables into another database?

Create a new database, and the "File" -> "Get external data" -> "Import".

When importing the tables, select the "Option" button and un-select the "Relationships".

Did I state it is important to have backups? (Before "playing")

Lastly, if you are running into the 255 max limit for tables, I assume this is because you are creating working fields to copy data over from one field to another etc. "Table is in transition" type of thing. However, it is important to realize that if you are using / creating a lot of tables for "use", your database may not be "normalized"

The typical links for further reading...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read document on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Yet another sources...
Introduction to Relational Databases - Part 1: Theoretical Foundation (15 seconds)

Richard