Nutty error

[Johnny C]

Hi



I've got a db in Access97 running on XP. It's 90Mb in size, with 100K records. Most of the data is in one table, with a few ancilliary lookup tables.

When I try and change the field size on any text fields, I get an odd error message on saving. It doesn't matter whether I'm increasing or decreasing the size.

"Microsoft Access can't change the data type.

There isn't enough Disk Space or Memory"

Disk space isn't a problem, there's 15gb free on a 27Gb network partition. And my PC is fine, it's a dual core with 2Gb.

If I reduce the size of the text field I get the message that data may be truncated as you would expect, and then the message. There's no way that reducing the size of a field should give that message.

The field isn't linked via a relationship to anything, it's a free text comment field.

I've tried backing up the Db and compacting, as that usually sorts weird errors out, but not this time.

Can anyone help shed light on this please?

[SoftwareMatters]

I don't think it's talking about actual hard disk space but an allocated amount of memory that Access stores data.

You could add a new field with the required text size and run an update query to populate it and then delete the old field.

[Johnny C]

Hi

I knew it wasn't actual storage as a problem, just a nutty error of the sort that older versions of Office chucks out

I found the problem, when you get more than about 45K rows in Access97, when you make certain design changes you hit a pagelocks limit that's set in the registry for the Jet driver when the records try and update the new field setting.

it can be fixed with RegEdit or in a macro though doing it in a macro (sorry with VBA) limits the scope to the VBA procedure, it can't be set at User Interface level.

Since what I was trying to do was best done via the user interface I just made a copy of the main table, deleted all the data, made the design changes (which worked with no data in) then copied the data back in from the backup table. Simples.