Hello all, I am looking for some guidance. I have built up a database over the last 6 years when I started to first use access. The main table has now reached its maximum field number of 255 and in order to continue using it in its present format I need to insert more fields but cannot. The database has approximately 20 other tables and many forms and reports. My question is this. I now realise that the basic structure of having such a large main table is very inefficient and incorrect in its' set up. The problem is I have built it up bit by bit as my knowledge has increased but have now reached a limit. As I was new to access the large table was a convenient and easy way to run forms and reports. There are just under 16000 records. I know to restructure will involve a complete re-design of the main table (by splitting it up and normalisating etc) but I don't know the best way of then moving the existing information into any new structure without doing some serious damage.Does anyone have any idea of the best / safest way of doing a re-model excercise. Thanks for any reply.
Database restructuring
- Thread starter FrankieM
- Start date
Whenever I inherit a mess, I start from scratch. Open a new blank database and create the correct stucture without any data. Once you have that, create links to the tables in the database with the improper structure. Then, create queries to move the data from the old tables to the new ones.
Just because I explained it in one paragraph doesn't mean its going to be easy. Moving the data to a proper structure could take hours of work (especially with 255 fields), most likely setting up an Append query and running it a few dozen times, tweaking it just a little for every different field that you are reducing.
If you need help with the right structure post back here what you come up with for us to critique.
Just because I explained it in one paragraph doesn't mean its going to be easy. Moving the data to a proper structure could take hours of work (especially with 255 fields), most likely setting up an Append query and running it a few dozen times, tweaking it just a little for every different field that you are reducing.
If you need help with the right structure post back here what you come up with for us to critique.
Lightwave
Ad astra
- Local time
- Today, 22:52
- Joined
- Sep 27, 2004
- Messages
- 1,517
While a total database restructure is the optimal long term solution remember you can do a one to one relationship against this 'big' table and continue to create further fields directly related to a key within the 255 fields.
In this way you really have no limit on the number of fields you can have directly related.
In this way you really have no limit on the number of fields you can have directly related.
Fully agree with plog. This is the best you can do.
On the other hand, I think that is not necessary to do all at once.
Take it step by step.
Create a new table, develop the logic, design the interface and, when everything is OK, move the data from where actually is, to the new table.
Repeat until all your database will be normalized.
This way you can keep the track and the users will not receive a "boom" of information at once.
Good luck !
On the other hand, I think that is not necessary to do all at once.
Take it step by step.
Create a new table, develop the logic, design the interface and, when everything is OK, move the data from where actually is, to the new table.
Repeat until all your database will be normalized.
This way you can keep the track and the users will not receive a "boom" of information at once.
Good luck !
Similar threads
