Table field conversion

  • Thread starter Thread starter RR1976
  • Start date Start date
R

RR1976

I have an issue with MS access 2002. I tried to convert a field type from a
number to a text field but I have over 3,000,000 records (that I need to stay
in the table) and because of the size of the table it will not let me change
the field type. So I tried to create a blank version of the table with the
correct text field and then I tried to import, append, and copy and paste my
data and it would not work. I tried changing the field type back to number
and it still didn't work. I kept getting an error that stated "invalid
argument." Does anyone know how I can work around this issue?
 
Couple of questions first...

Are you able to create a new field in the exisiting table? If so you might
try the following... (assuming the field you want to change isn't a part of a
relationship. If so be sure the relation is unlinked first).

Add a new field to the table with the desired datatype

Create a query and drag the actual numeric field and the added field to the
querygrid. Now think in chunks...

Change your query into an updatequery and update the newly create field to
the actual numerical field but be sure to update let's say the first 100000.
If that works then try a second chunk and so on. Be aware that updating
30000000 rows will take some time.

When done delete the actual field and rename the new field to the name of
the old field.

ps make sure you try on a copy of the db first !!

hth
 
I have an issue with MS access 2002. I tried to convert a field type from a
number to a text field but I have over 3,000,000 records (that I need to stay
in the table) and because of the size of the table it will not let me change
the field type. So I tried to create a blank version of the table with the
correct text field and then I tried to import, append, and copy and paste my
data and it would not work. I tried changing the field type back to number
and it still didn't work. I kept getting an error that stated "invalid
argument." Does anyone know how I can work around this issue?
Click to expand...

How big (in megabytes) is the actual database? With three million rows you may
well be pushing the 2GByte limit.

I'd suggest creating a new, empty database. Import everything BUT this table;
import this table, *design mode only*. Change the datatype of the field.

Use File... Get External Data... Link to link to the existing table.

Then run an Append query appending the data into the new table. Depending on
the nature of the data you might need to use the Format() function to cast the
number into the desired text datatype (probably not if it's just an integer).

You will probably need to reestablish relationships.

The "Invalid Argument" error, though, strongly suggests that you're calling
some function with an invalid argument, and has nothing to do with the size of
your table; what specific steps did you take? If you used a Query please post
the SQL, this may be fixable.
 
Actually I was not running a query when I first got the message. I only
tried a simple append query later...after I started receiving this message
when I tried other options.

That was the strange part about the error message; it started when I simply
tried to import the records into the new table I made and wouldn't even let
me paste a copy of the table in the database I was currently working.
 
Back
Top