I have a 1.5 Million record database. When it was originally designed field widths were set to 255 bytes wide. My database size is almost 2GB and I would like to a shorter length. When I use the design window it errors out due to lack of memory. When I copy the structure to a new table, modify the column widths and then do an append query from old to new, during the append query the old file sizes are moved to the new file. Are there any suggestions that I have not tried?

Thank you in advance.

Do you mean you want the fields to be limited to fewer characters, like a text field that can only contain 10 characters instead of 255, or are you talking about the physical column widths, I am a little confused.

All my text fields are set to 255 character width. I want to make the table / database a smaller file, I want to change the 255 character widths to 30-100 characters to shrink the file.

Hmm.

It's off the top of my head but I believe Access doesn't pre-allocate the spaces for text so it only consumes just as much bytes as actually need.


But... have you tried Compact and Repair? If you can't do that, then the next thing is to create a new database, then import in tables from the existing database. Of course you should have a backup.

Compact and repair did make the file size less. I am not as familiar with the access format, but dbf's were fixed length records so modifying the text width made a difference in file size.

Did you already try the import anyway?

Erm, to be more elaborate as I realize you may not want to just blindly import; you may want to start off by importing "structure only" then modify all the fields, *then* import again but this time choosing "data only". See if it helps any.

It's off the top of my head but I believe Access doesn't pre-allocate the spaces for text so it only consumes just as much bytes as actually need.That is correct - Access stores only the characters in the field, not the empty space - it doesn't even store additional space characters trailing at the end of a string.

Also, in order to work out the acceptable length to which a field can be shortened without losing data, you can determine the maximum length of text the field with a query like this:

SELECT TOP 1 TableName.FieldName, Len([FieldName]) AS Expr1
FROM TableName;