CODEWITHSUNDEEP
ms-accessimportcolumn-width
Brendan Kidwell
Brendan Kidwell

Reputation: 866

MS Access: How can I automatically reduce Text columns to an appropriate size for the data stored in them?

MS Access has a passable text file import wizard. (File -> Get External Data -> Import.) Unfortunately, text columns (fields) are always imported with 255-character size, regardless of the actual data found in the input file.

How can I reduce these 255-character Text columns to a reasonable size without manually doing a bunch of "SELECT Max(Len(_column_)) FROM _data_" queries?

I already have a solution and I will post it below.

Upvotes: 0

Views: 1640

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

Is there any reason to do this? Reducing the field size only reduces the amount of text the field can store, not the amount of space the file takes on disk.

Upvotes: 2

Brendan Kidwell
Brendan Kidwell

Reputation: 866

I have created a tool that will scan through an imported table after the fact, report the actual minimum and maximum sizes found in Text columns, and allow you to change the column sizes all at once:

http://www.glump.net/software/microsoft_access_table_optimizer

This tool does not affect the MS Access import wizard, nor does it replace it. So, if you're importing a huge number of records or columns with Text values, you might run into space issues before you get to the point where you can run my tool. YMMV.

The tool is a module of VBA code that 1) creates a report of all columns in a given table and minimum and maximum lengths of values and 2) after you edit the report table, resizes columns with your specified new size.

Upvotes: 1

Related Questions