can't change data type

[SirPoonga]

I am getting an error box
"Microsoft Access can't change the data type. There isn;t enough disk
space or memory."

My machine is a new Dell Precision 3.4ghz, 70g hd - 66g free, 1g
memory, 3-5g virtual memory.

The database is 350m in size. It was produced from importing data from
a text file dump of a mainframe database. I imported all the values as
text to be safe as to not loose any info. I am trying to convert the
date field from Text to Number as it is in yyyymmdd format so I can
filter out date ranges.

Any ideas?

 

[Jeff Boyce]

When do you get that error message?

How many fields are in the import data?

Jeff Boyce
<Access MVP>

 

[Joseph Meehan]

I am getting an error box
"Microsoft Access can't change the data type. There isn;t enough disk
space or memory."

My machine is a new Dell Precision 3.4ghz, 70g hd - 66g free, 1g
memory, 3-5g virtual memory.

The database is 350m in size. It was produced from importing data
from a text file dump of a mainframe database. I imported all the
values as text to be safe as to not loose any info. I am trying to
convert the date field from Text to Number as it is in yyyymmdd
format so I can filter out date ranges.

Any ideas?

I am no expert in this, but you can try what has worked for me from time
to time.

First do a repair and compact.

Next chose only one field to change.

repeat both steps for each new field.

BTW what size are those text fields (all 255 characters?)

 

[SirPoonga]

I get the error message when I try to save the design. There is about
30-40 fields.

 

[SirPoonga]

Joseph, that is what I am doing. Yes, it defaulted the import to 255
chars. I suppose that is taking up alot of space

 

[SirPoonga]

Actually, can you import a delimted file, specify a field as text, and
limit the size? I didn't see the option in access 2000. I could cut a
bunch of the "fat" if I could do that. Most fields are text IDs,
descriptions, etc.. where I don't need much more than 5-10 characters
for most.

 

[SirPoonga]

I would need to have an empty table setup before importing to import
into if I wanted to specify text field size, right?

 

[Joseph Meehan]

I would need to have an empty table setup before importing to import
into if I wanted to specify text field size, right?

I don't know what format the original data is in, but you might try
importing into a new table with existing fields with reasonable sized
fields, or you may try importing into a new table and set the size as part
of the import schema.

 

[SirPoonga]

Yep. That's what I decided to do. I created some empty tables with
the sizes I need. I also modified the import specs and saved them.
The weird thing is, when all the data was imported as text (with
default 255 characters) the db took up 333megs. The new format which
included integers, shorter text, etc, takes up 312meg. Not that big of
a difference.

 

[Dirk Goldgar]

Yep. That's what I decided to do. I created some empty tables with
the sizes I need. I also modified the import specs and saved them.
The weird thing is, when all the data was imported as text (with
default 255 characters) the db took up 333megs. The new format which
included integers, shorter text, etc, takes up 312meg. Not that big
of a difference.

For text fields, Access is only going to use as much of that 255
characters as the string to be stored in the field actually requires.
So you don't actually gain much by setting the field size smaller than
that, unless the incoming data is padded out with blanks; e.g.,

123,"some data ",01/24/2005

Another thing you might try is linking to the text file, rather than
importing it directly, and then using an append query to copy the data
from the linked table to your destination table, converting field types
in the process.

 

[SirPoonga]

So what does text size affect? With a smaller text size will it
increase performance or queries?

 

[Rick Brandt]

So what does text size affect? With a smaller text size will it
increase performance or queries?

Not really as Access only uses the storage space that is actually filled
with characters so a table with all text fields defined with a length of 255
would likely perform the same as one where they were defined as length 20
(providing that no more than 20 characters was ever actually entered).

The text length is more of a data integrity mechanism. For example if I
create a field to track Part Numbers and I know that valid Part Numbers are
always 15 characters or less then specifying that in the field's length
property automatically prohibits an entry that is known to be incorrect
because it exceeds that length.

 

[Dirk Goldgar]

So what does text size affect? With a smaller text size will it
increase performance or queries?

What Rick said.

 

[SirPoonga]

Ok, so back to my original problem. Why would changing sata type (or
text field size, that did the same thing) give me that error?

 

[Dirk Goldgar]

Ok, so back to my original problem. Why would changing sata type (or
text field size, that did the same thing) give me that error?

I'm not sure of everything that's going on behind the scenes, but
clearly you're working on a very big table. Access is probably going to
need to create, at a minimum, an additional copy of the field(s) you're
changing, and maybe a completely separate copy of the whole table. I
also expect that the operation is going to be wrapped in a transaction,
and that requires a lot of extra storage space. I rather think it
requires at least as much space as the original data you're updating,
but that's really just my guess.

Have you considered my suggestion about linking to the text file and
using an append query to populate your table? If necessary, you could
do using the CurrentDb.Execute method, avoiding the use of a
transaction.

 

[SirPoonga]

Yes, I considered it. I'm just thinking if I need to change something
int he future I mightnot have the liberty of reimporting.

I still don't know how I'd run out of space. it's a 330meg database. I
have 60gig free of hd, 1gig of ram, 3-5gig of virtual memory.

 

[Douglas J. Steele]

Under the covers, Access is actually creating a new copy of the table,
therefore it can increase the size significantly. If you were doing it for
more than one table, that's probably what happened.

 

[SirPoonga]

Just a single table. But yeah, if it is making a copy I could that
then. Though I'd think between ram and virtual memory i'd have enough
space, even if you doubled the size of the entire database, not just
one table.