Field size reduction on import of Excel, txt, etc files

[Peter]

Hi,

I import numerous files from various clients. The files usally come in Excel
or Text Delimited format. And the files are always different in terms of
fields used.

When I import such files I get the usual max length for each text field.
However, I need all text fields to be no larger than 100 characters.

After doing a lot of searching it seems that there is no way to make Access
default at 100 characters field width with imports.

I do not have tje option of importing into a pre existing table that serves
as a template since all our clients provide different kinds of data.

What I am looking for is a way that I can quickly change the fields widths
to 100 once the table exists in the database. I am getting tired of going
into the table design mode and manually changing the field size to 100 for
every field.

Perhaps this question is better suited for Table design, but I'm hoping that
there's an off chance of fixing the field size on import. Again, I need
something that works for both Excel and Text delimited files . . .

Any ideas would be appreciated.

Thanks!

Peter

 

[John Nurick]

Hi Peter,

Do you actually have to set a maximum field width of 100 characters, or
do you just want to truncate any values that are longer than 100
characters?

If the latter, you can just use an update query:

UPDATE TheTable
SET Field1 = Left(Field1, 100),
Field2 = Left(Field2, 100)
...

If the former, one approach is to build and execute a series of DDL
statements like this, one for each field in the table:

ALTER TABLE TheTable
ALTER COLUMN Field1 TEXT(100);

Either way it's not hard to write VBA code that gets the field names
from the TableDef object representing the table, assembles them into the
necessary SQL statements, and executes the SQL.

 

[Aaron Kempf]

tabledef?

are you kidding me?

people should use Access Data Projects, DAO has been dead for a decade!