Error "not enough memory or disk space "

[joeb]

I am trying to combine 2 tables that contain the same data
but that were designed separately and so have different
datatypes, field sizes and other properties. The different
properties caused certain fields to show different results
depending upon the source of the record. For example, one
text field had 2 spaces in front of 8 digits for those
records that came from one source table while the same
field from the other source table showed 8 digits only
with no leading or trailing spaces. I don't know how
relevant these differences were but they did cause sorting
discrepancies so I thought it best to mak ethem more
uniform.

I managed, through a make-table query, to make it so that
all records' data was the same. My issue now is that when
I make any changes to the field size property of any field
and try to save the table, I get the following error:

"Microsoft Office Access can't change the data type.
There isn't enough disk space or memory."

Some background notes:
- I'm using Access 2003 in Windows XP Home
- I have an HP Celeron 2.0 with 256MB RAM and 26GB
available on my 56GB drive
- The table in question has about 1.5 million records
- I changed the paging file setting to 1000MB-2000MB with
no effect.

Any guidance would be greatly appreciated.
Thanks in advance...Joe

 

[Jason Sharp]

To be honest I am not sure about table specs for Access 2003. At first glance they do not appear to be much different than xp2002. Going with that idea, it would be impossible for you to have an Access table that contains over 65536 records. Additionally, there are other "z-coordinate" considerations relating to data management when trying to merge data from separate tables into one table, especially if you cannot use a union query followed up by a make-table query. Considering this situation, it is not surprising that you are receiving a memory error. The only exception (other than new capability in 2003) is that you are running a SQL project in Access, which I believe would allow you at least 2 million records if not unlimited... I am not as current as I would like to be, nor nearly so well versed in Microsoft SQL 7 engine

It sounds like you have encountered this error because of the other memory considerations relating to fields, then record, then table. Additionally, if I am right about the max number of records thing: then you may have started with what was or would become 1.5 million records, but in the course of operations you actually wound up with truncated data, and probably a corrupt MDB file

If I was wrong... and I've been known to be wrong.... then your problem with changing data types is probably that one of your records contains more information, or invalid data: for the datatype that you are trying to specify. Happy hunting!

 

[Ken Snell]

There is no limit to the number of records that a table can have. The 65,536
limit is the number of rows that an EXCEL 2000 or higher spreadsheet can
have. An ACCESS database is limited in its overall size, not in the number
of records.

--

Ken Snell
<MS ACCESS MVP>

To be honest I am not sure about table specs for Access 2003. At first

glance they do not appear to be much different than xp2002. Going with that
idea, it would be impossible for you to have an Access table that contains
over 65536 records.

< snip >

 

[Ken Snell]

ACCESS copies the data from the old field to the new field, and then deletes
the old field, when you change properties on the field.

You may need to create a new table with exactly the right
formats/properties, and then append the data from the old table into the new
table.

 

[Guest]

Thank you Ken, for your response. Thanks to you also
Jason.

As it happened, I came to the same solution that you
recommended (and fortunately before I had pulled all of my
hair out.) It worked as you suggested and completely
resolved this issue.

I appreciate your response and especially your explanation
of the workings of Access that cause the problem. It will
help me figure out future issues myself and hopefully
reduce my frustration levels.

Thanks again,
Joe