Too many fields? Or what?

[Compressor]

I'm trying to make 1 table from 2. Copied the fields from one to the other tried to save... No luck. Error: Property value is too large. The help accompanying the messagebox is:

Property value is too large. (Error 3309)
You are trying to set a property value of an object to a size greater than the largest permissible size of 2K. Shorten the length of the property value.

So I click ok. Then another message pops up: Errors were encountered during the save operation. Properties were not updated.
Then I click show help:

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.

Just to see if I did have too many colums (fields) in the table I selected them all, copied them to another instance of access with a new DB and table and tried to save again. Same thing happened. I started to delete some fields and after deleting some 12 fields the table could be saved again. The same is true for the table in the original database.

I think I have read somewhere that access allows for 255 different columns (fields) per table (or is it per Database?) but I do not come even close (well, at least not in one table, maybe across tables... yes), I have around 140 fields in that one table. So that shouldn't be the problem although it does appear that way....

In the help part they are talking about the MaxLocksPerFile value. I don't know what a "LockPerFile" is, but the value they mention is 9500 in my registry. I'm guessing its next to impossible that I have reached that limit in any way.

So I'm stumped.... That leaves little flexibility in the table for the future, or I will have to stay with my two tables I had before.

Erhmm.... Help?

 

[Compressor]

Searching through google did deliver a lot of hits, but none that I can find up to now which says... hey, access bug, or JET bug (or limitation). However... google did come up with a post from the archive here of someone who has/had the same problem. Also no real solution in that thread, but..... the last post in that thread reads:

Are you using alot of text in the field description area in design mode? I am having the same problem, and found I could add a word to a description for one field at a time up to a certain number of fields, then get that error. We put alot of detail into those descriptions, so I wonder if there is a certain limit to the size of information you can use - either for a specific field or for that area in general? Any thoughts from the experts?

(complete link: http://www.access-programmers.co.uk/forums/archive/index.php/t-89885.html )
And after that, no replies anymore.

So I started to delete some text which I had entered in the description field of the table in design view. And guess what? I could start adding fields again. So what is up with this one? Is it me? Is it Access? Is it Jet? Is it a known issue? Are there any Microsofters out-there aware of this? Anyone here with a solution?

Maybe my google-ology isn't good enough, gonna try the MSKB... But am curious about your advice/findings...

 

[ColinEssex]

I have to say that 140+ fields in one table is suspect. Most tables shouldn't need more than around 20. What are these 140 fields?
Have you designed the table like a spreadsheet?

Also, I remember reading somewhere (but can't find it now) that Access remembers how many fields have been added and deleted to a form to a max of (I think) 566. So if you keep copying a form and then changing it, it remembers all the previous changes and counts them then you can no longer add any more or save.

That may not be your problem, but I thought I'd mention it.

Col

 

[Compressor]

well, I had split up "case- date- time- appointment- cancellation of appointment- case closed- etc information, and technical information at first. Now these two are combined. I think I will be better of this way in the long run. They all are about one specific case for one customer anyway.

And as the number of fields... well... A collection of 10 fields of one-liner problem descriptions, hardware that is in the system, for example, memory. Well, what type of memory? What interface? What type of speed? ECC? etc etc etc. And that for most general parts that can be in a PC. Ehrm... I say general, because going specific spread across a forest of different tables would make absolutely no sense to me anymore. Up to a certain point, yes. But I',m a newbie who needs to learn a lot still... But I don't want to make it any harder than necessary. I started to come across problems, simple problems for experienced users maybe, but for me.... nah, too difficult for now. Time is an issue: two more months: no more income (well, almost no more). Unless I can start-up my company. And I need a working database for that. So, trying to keep it simple. As my skills progress, I can always redo the database at one point in time. Simple... doesn't mean I am not willing to put a lot of effort in it btw.

 

[gemma-the-husky]

hiya compressor - are you using a SQL backend by any chance?

 

[Compressor]

@Gemma: Ehrrrrmm..... Huh?

 

[pdx_man]

Do some searches on Decompiling an Access Database. This may free up some of the issues that Colin was referring to.