can't change data type in table definition

G

Glenn Harwood

I have a table with 950,000 records. It's size is 850
MB. the default text field size is 255 bytes. There are
number fields which I need to change to text fields.
When I do so, even if it's for a single field, and try to
save it, i get an error message saying I have
insufficient memory or disk space to complete the task
and errors off. I have 1MB of memory and 17GB of disk
space, certainly more than enough.

Am I beyond the limits of Access or is there another
approach to solving the problem?

Any help would be most appreciated.
 
A

Allen Browne

An Access database cannot be larger than 2GB (1GB for A97 and earlier). The
conversion process is probably overflowing this limiation. You can work
around it by creating a new database, setting up the structure you desire,
and then importing the data.

1. Create a new (blank) database.

2. Import just the structure of this table:
File | Get External | Import
Click the Options button, and select Definition Only.

3. Modify the structure, changing the field(s) to Text.

4. Attach the table from the original database:
File | Get External | Link

5. Create a query into the linked table.

6. Change it to an Append query: Append on Query menu.

7. Drag all the fields into the grid. Because they have the same names,
Access should map them correctly.

8. Run the query.

The import should work, but if you receive an error that the field types do
not match, use Str() around the affected field names in the Field row to
convert them to text.
 
V

Van T. Dinh

Which Access version?

I believe the problem is that Access actually creates the
new Field first, populate this new Field and then delete
the old Field when you try to convert the data type.

Not sure but you can try creating a new database file with
an empty Table of the new modified structure. Teporarily
link the old Table to this new database. Use an Append
Query (with some data manipulation to append the existing
Records to the new Table with the correct structure.

HTH
Van T. Dinh
MVP (Access)
 
G

Glenn Harwood

Thank you Allen
-----Original Message-----
An Access database cannot be larger than 2GB (1GB for A97 and earlier). The
conversion process is probably overflowing this limiation. You can work
around it by creating a new database, setting up the structure you desire,
and then importing the data.

1. Create a new (blank) database.

2. Import just the structure of this table:
File | Get External | Import
Click the Options button, and select Definition Only.

3. Modify the structure, changing the field(s) to Text.

4. Attach the table from the original database:
File | Get External | Link

5. Create a query into the linked table.

6. Change it to an Append query: Append on Query menu.

7. Drag all the fields into the grid. Because they have the same names,
Access should map them correctly.

8. Run the query.

The import should work, but if you receive an error that the field types do
not match, use Str() around the affected field names in the Field row to
convert them to text.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
 
Top