access crash when I change filed length in a sheet with 999400 re.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with almost 1 million records, I am trying to truncate
(change the size of one column to a lower number to exclude characters. I
get not enogh memory or disk space error and access refuse to update.
Can I restrict the field size during importing a comma delimited text file ?
Because this will do the same thing
 
Hi,

First, make sure to back up and compact your database before and after
attempting table design changes when you have a lot of data.

Second, if you just want to truncate the data there is no need to alter
the column width. Unlike older databases, Access only stores the actual
data in text fields (plus a little overhead): a field containing 10
requires the same amount of storage whether it's a 10-character field or
a 255-character field. So you can simply use an update query to update
the field to (e.g.)
Left([XXX], 10)
where XXX is the name of the field.

If you want to control the field widths when importing from a delimited
text file, you can create the table manually first with the correct
number and types of fields (and field names if these are included in the
text file) and the desired field widths. Then import the data to this
existing file.

But if the fields you define are too narrow for the data you are
importing you will get error messages. So as an alternative you could
temporarily link to the text file, create the "permananent" table
manually, and then use an append query to move the data, truncating
strings (with the Left() function) and making any other changes as you
do.
 
Back
Top