System Resource Exceeded. (Run-time error '3035')

[Harmless]

I have a fairly large table, and I'm trying to change a field to
'Date' type. But I get the above error message. I'm running the query
from code:

CurrentDb.Execute "ALTER TABLE [myTable] ALTER COLUMN [F9] DATE;"

I get a similar error message when I try to manually change the field
type in the Table's Design mode.

The table has 22 fields and nearly 150,000 records. The table is
imported from a 23 Mb CSV file.

The field is currently Text(255). I get the same error when I try to
manually change it to Text(10). If I understand it correctly, Access
creates a temporary field, and copies data to/from it. So if the
temporary field is Text(255), then the amount of additional data
produced would be 255 x 150,000 which is around 38 Mb.

I'm using Access 2000. My PC is a P4 1000 MHz, with Windows 2000.
There's 3 Gb of disk space free.

I've tried fiddling with the virtual memory settings, currently set to
384 - 1024 Mb, but it makes no difference.

There are many posts related this error message, but I didn't find any
good answers. Most of the posts relate to more complex queries etc,
but mine seems very simple.

Should I be able to achieve this, or am I pushing the bounds of Access
2000 and/or my system?

Thanks,
David.

 

[david epsom dot com dot au]

Originally, DAO/Jet couldn't do ALTER COLUMN like that.
When ADO first came out with that capacity, we were
told that DAO couldn't do it. So that is not something
that has been tested extensively by a lot of users.

An error like that may mean that there is a problem with
the data somewhere in the column. If the text field has
a text date in it, Jet is probably not going to be able to
do the conversion, and even if it can, there will probably
be an invalid date which will not convert correctly.

But I see that you are testing with a text(10) field.
Could that be falling over on a field where the text
length is greater than 10?

Things you can try: (1) execute against the current
connection instead of the current db:
Application.CurrentProject.Connection.Execute
(2) Create a new text or date field, and copy the data.

(david)