trouble changing data type

[Kevin]

I am running MS Access 2007 on Windows XP Professional 2002 (SP2).

I need to change the data type of a single field in a table containing just
over 1.4M records in order to link it with another table and run a Make-Table
Query, but Access is telling me I do not have enough memory to change the
data type. I have 1GB of RAM and a Pentium 4, 2.79 GHz processor. Am I out
of luck based on the equiptment I am working with, or is there a way around
this?

Much appreciated.

 

[KARL DEWEY]

First backup your database. Next Compact and Repair.
Third add the new field datatype. Create an update query to update new
field from old.
Compare date in the two fields - use a query to compare such as new text
field to number by criteria Val([TextField]).
Delete old field. Compact and repair. Make a new backup, saving old backup.

 

[John W. Vinson]

I am running MS Access 2007 on Windows XP Professional 2002 (SP2).

I need to change the data type of a single field in a table containing just
over 1.4M records in order to link it with another table and run a Make-Table
Query, but Access is telling me I do not have enough memory to change the
data type. I have 1GB of RAM and a Pentium 4, 2.79 GHz processor. Am I out
of luck based on the equiptment I am working with, or is there a way around
this?

Much appreciated.

What's the size in MByte of your database? If it's pushing the 2048MByte size
limit you might need to do this in a more roundabout way. One suggestion would
be to create a completely new .mdb file; import this table *design view only*
and change the datatype in the (empty) table. Then link to your original
database and run an Append query to migrate the data into the new table, and
import all the other tables and objects. This will have the advantage that
you'll get a clean set of systems tables... and the disadvantage that you may
need to redo any Workgroup security.

 

[Kevin]

Thank you. I'll take your advice.

But that brings up another issue for me... I'm familiar with compacting and
repairing databases in Access 2003, but I can't figure out how to do it in
2007. Where is the tool located?

--
Kevin
Philadelphia, PA

First backup your database. Next Compact and Repair.
Third add the new field datatype. Create an update query to update new
field from old.
Compare date in the two fields - use a query to compare such as new text
field to number by criteria Val([TextField]).
Delete old field. Compact and repair. Make a new backup, saving old backup.
--
KARL DEWEY
Build a little - Test a little

I am running MS Access 2007 on Windows XP Professional 2002 (SP2).

I need to change the data type of a single field in a table containing just
over 1.4M records in order to link it with another table and run a Make-Table
Query, but Access is telling me I do not have enough memory to change the
data type. I have 1GB of RAM and a Pentium 4, 2.79 GHz processor. Am I out
of luck based on the equiptment I am working with, or is there a way around
this?

Much appreciated.