cant save database

[Guest]

I am importing a refreshed table that I import every month. The next thing I
do is change a few of the fieds from 255 to 2. when I attempt to save it I
get a message that I don't have enough memory or disk space and that is not
the case. The help message is like below....I did not change any index.
what can I do? thank you


This error can appear if:
You have exceeded the maximum number of columns allowed in a table or the
maximum number of locks for a single file.
The indexed property of a field was changed from Yes (Duplicates OK) to Yes
(No Duplicates) when duplicate data is already present in the table.
If a table exceeded the maximum number of columns, close the database,
select Compact and Repair from the Tools - Database Utilities menu option.
This will remove column references from a table definition that were marked
as deleted (deletions do not actually occur until the database is compacted
and therefore they contribute to the column count).
If the maximum number of locks per file was exceeded, you can increase the
number by editing a registry entry for the local computer. However, this is
not a recommended option.
Find the MaxLocksPerFile registry value using the Windows registry editor
(regedit.exe) and increase the value.
The MaxLocksPerFile value is stored as part of the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
If you edit a value in the Windows registry, there is no guarantee that the
change will not corrupt the registry and render Windows unusable. Edit the
registry at your own risk. If you do, it is highly recommended you make a
backup of the registry before performing any edits.
If you have changed the Indexed property of a field and duplicate data
already exists in the table, you can either reset the Indexed property back
to the previous setting, or remove the duplicate records from the table.

 

[John Nurick]

How many columns (fields) are in the table? Does the problem go away
when you compact and repair the database?

 

[Guest]

there is 33. it does not go away. I added some text to one field...is it
possible it does not like brackets or dashes in that field?

 

[Tony Vrolyk]

This is a long shot but is the mdb stored on an old Novell 4.xx server? I
know there aren't many left out there but I had one a while ago and got the
same error trouble running update queries on large numbers of record because
of some kind of file lock limit that was too low. Although 33 records seems
pretty low I thought I would throw that out there as a possibility, however
remote. If this is the issue sorry I don't remember the exact name of the
setting on the server to change.

Tony

 

[Guest]

It is one my hard drive

This is a long shot but is the mdb stored on an old Novell 4.xx server? I
know there aren't many left out there but I had one a while ago and got the
same error trouble running update queries on large numbers of record because
of some kind of file lock limit that was too low. Although 33 records seems
pretty low I thought I would throw that out there as a possibility, however
remote. If this is the issue sorry I don't remember the exact name of the
setting on the server to change.

Tony

 

[John Nurick]

1) Adding some text to a field shouldn't make any difference.

2) Instead of importing the table afresh every month from some external
source, I'd keep the existing table. The monthly drill would be:
- run a delete query to get rid of the existing data
- compact and repair the database
- import the new data to the existing table.

3) If you do need to import the table afresh and then change the field
types, try it this way after importing:

-rename the fields you want to resize
-create new fields with the sizes and names you want
-create and run an update query to move data from the renamed fields to
the new ones
-delete the renamed fields
-compact and repair.

AIUI this is less likely to hit the MaxLocksPerFile problem.

4) Finally, increasing the MaxLocksPerFile registry setting may be a
solution. This doesn't seem to a risk in itself, though I'd research in
the newsgroups and the Microsoft website before doing it myself. The
risk that the error message warns about is that an unwary user will
screw up other registry settings. In the worst case this can indeed
"render Windows unusable".