Access Database

[pink-lady]

I have created an Access Database, mainly using the 'Form' function. I have created tabs and have many different fields within the one Table which forms the basis of my Form.
However, when I come to input data, on several records it is coming up with 'Record is too large'. Now I cannot amend these records in any way - either to add or delete data.

How can I resolve this?

The 'help' function is giving me the following information:
"You defined or imported a table with records larger than 2K. This error occurs when you enter data into the record � not when you define the table structure. Redefine the table by making some fields shorter, removing unneeded fields, or moving some fields to other tables"

Do I need to re-build my entire database or is there a way round this?

Thanks.

[padanarm]

You've probably got too much data in each record.

Each field takes up a certain number of characters, roughly equal to the number of bytes (eg. 255 chars = 255 bytes). Total up the size of all the fields in a record and if it comes to more than 2000 bytes, you've too much data in each record.

You may have to redesign the table and limit the size of each field. Or you may have to normalize your data, which means splitting the data into linked tables.

Are you entering the data directly onto the form or do you import it from, for example, Excel?

[pink-lady]

Initially I imported it from Excel and now I update it directly on to the form.

If I amend my database so put half the data into one table and half into another table, will I then be able to use a query to link the two tables so can use the data from just one query to create my form or will the same problem then arise?

Thanks for your help!

[padanarm]

When you import data from Excel the field lengths are set to 255 chars by default, irrespective of the amount of data in a field.

Try changing the field lengths in Table Design mode to something more appropriate, for example a surname should be about 20-25 chars, a postcode about 9 chars.

Do you really need every field in the table?

[pink-lady]

Hmm, okay, I have 150 fields but can't remove any of them. It's basically my Marketing Database which contains information about the customer, images, display, promotions etc.

If I can link forms/tables somehow, I can possibly separate the data so I have different forms to update different sections...but I don't want to have to update the same information more than once as that's when I find mistakes occur (for example if accounts open/close and adding/removing information from more than one source).

I tried amending the field lengths however got the following message:

"too many fields defined"

I have deleted approx 20 fields that I could get rid of though.

Sorry - this is all new to me.....

[padanarm]

I can't believe you need 150 fields. Your data was probably set up in a way that made sense in Excel so you've probably got duplicated data.

Easiest things first.

1. Backup the database in case things go wrong. Depending on your version it should be under the Tools menu > Database Utilities > Backup Database.

2. Compact the database. Again it should be under the Tools menu > Database Utilities > Compact and Repair.

That should get rid of the 'too many fields defined' error. You should now be able to modify the field lengths.

Tables are used to organise and store data, forms simply display what's in the tables. If you can reorganise the tables you can link them by common fields or a query.

Have you had a look at the Microsoft Access sample databases? There are a couple of customer management samples in there.

[pink-lady]

Thank you! I have done the back-up and used the compact and repair as suggested and I can now amend the field length.

I think I need to re-assess where I input my data. I currently use the form to input all my data. Unfortunately, the fields are necessary so when I export data back into Excel, I can then simply copy and paste into the relevant order forms. I basically built my database from 4-5 different Excel sheets so I had all my information in one place....not such a great idea after all I see!

I'm off for a week now so I will have a play around again when I'm back in the office and see what I can do to minimise the size.

Thanks again for all your help - you've been great!

[beso]

You really should restructure your database. As its record count grows it will become slower and more unweildy for the computer to hold in memory and more hence more unreliable.

Separate out the data into tables whose fields record a particular property of a some key aspect in the relationship with the customer.

Databases usually work best with many records and few fields. For example one would not have a field for say "January Promotion" with a yes or no to indicate whether the customers was contacted. In this structure fields would need to be added every time a promotion was started. This is often how such wide tables originate.

Instead you have a Promotions table with PromotionKey field and any other properties of the promotion that apply to all customers who receive it. Then you have another table with PromotionKey and CustomerID with a record for each customer who received it. When you add another promotion you simply continue to add records without having to add more columns.

Wherever the promotion is referred to anywhere it in any table it is only every known by its PromotionKey. Everything else about the promotion is in the Promotions table.

To Excel users a database may at first seem a very clumsy disconnected way to store data. However this is the crucial point. In Excel the tables present the data. In Access the tables store it in the most efficient way for the computer to get to particular records.

In a database data entry and presentation are performed through Forms and Reports designed to make sense of the records scattered across a dozen tables.