Property Value Too Large

[katefred]

I'd like to create a table with 240 fields. I know that the max is 255, however, I'm getting a message "property value too large" after I've created 114. Any ideas? All the number fields are byte size. Thanks!

 

[RuralGuy]

You might do a Compact and Repair and gain the ability to add more fields. It has been my experience that when people need that many fields, there is a normalization problem. With a relational database like Access you should be thinking tall and slim tables (lots of records and not many fields in each record). You can always create a 1:1 relationship with another table. If you tell us what is going in all of those fields, maybe someone will come up with a better idea.

 

[sbender333]

I am having the same problem and would really like some help. Here's my situation.

I'm trying to create a database from a large survey. The problem I'm having deals with some questions having the option to "select all that apply." For example, one question deals asks, "What conditions did this patient have prior to admission? Select all that apply" and below 30 or so different medical conditions. Then below that, another question might ask, "What procedures were performed after the patient was admitted. Select all that apply" followed by another large list of procedures. And below that a question asking about all possible drugs a patient was given.

So, I realized because of the "select all that apply" option, I had to list out every condition, procedure, drug, etc, as a separate field with a yes/no option. So instead of having a field that said "medical_conditions_prior" with hypotension, chf, diabetes, etc. as options, I had to create fields for each conditions, such that my fields were "medical_conditions_prior_hypotension", "medical_conditions_prior_chf", "medical_conditions_prior_diabetes", etc, and the options were "yes/no."

However, I recieved an error message saying I had two many fields. So I changed my approach and tried to create a primary table and then a table for each survey question that had "select all that apply" option. So, in my primary table, I would have a field that said "medical_conditions_prior" with a yes/no option. And I would create a table with all the medical conditions with a yes/no option. But, I have no idea how to correctly link these tables, as there are no two fields in the table that match, such as a primary table organized by novels and a secondary table organized by authors, but still having a novels field.

So any help would be appreciated.

 

[RuralGuy]

Have you looked at Duane Hookom's At Your Survey on his site?

 

[MsfStl]

Same Problem

I, too, am receiving the same error message. I have figured out that there is a limitation to the number/size of the Validation Rules and/or Text.

I was given a DB to finalize, I did not design nor build, and I noticed it had no error checking for data entry, so I have been working to add these. It seems that at some level Access limits the overall size of the Validation Rules and Text to 2k. Is it possible to enlarge that to say 3k? I believe for my purposes that should be sufficient. I thought about splitting the table as you mentioned RuralGuy, and may still have to do that, but if I can work with what they gave me it would definitely save me time.

Any thoughts would be appreciated.

Scot

 

[RuralGuy]

Scott,
I believe there is a 2K limitation to a validation rule in a *table* but not if the validation is done in the BeforeUpdate event of a control on a form that is bound to that field.

 

[DCrake]

Simple Software Solutions

To all interested listeners:

I also collect data from a patient survey into a database. This was a legacy system that I was asked to revamp. My solution was first to determine then number of options that the user could select from. Idealy a max of 26/36 is ideal. I gave each option a code A-Z & 0-9 then I created a field that was a text field 36 characters in size.

As I knew what order the questions were going to be answered i related this to a position in the string, for example Field fldQuestion1 = Text 26 and were all True/False responses.

After the user has ticked all the boxes I looped through them and built up a string similar to 0001110101011110 etc. this was then save into fldQuestion1.

Answers1 = iif(Me.ChkQ1a=True,1,0)
Answers1 = Answers1 & iif(Me.ChkQ1b=True,1,0)
etc

fldQuestion1 = Answers1

When the record was brought to screen a sub routine loops through the string and sets the value of the approriate control to the value in the string. Therefore ChkQ15.Value = Iff(Mid(fldQuestion1,5,1)=1,True,False)


By doing this you can store up to 36 answers in one field

Hope this is of use to you

Code Master

 

[DCrake]

Simple Software Solutions

To all interested listeners:

I also collect data from a patient survey into a database. This was a legacy system that I was asked to revamp. My solution was first to determine then number of options that the user could select from. Idealy a max of 26/36 is ideal. I gave each option a code A-Z & 0-9 then I created a field that was a text field 36 characters in size.

As I knew what order the questions were going to be answered i related this to a position in the string, for example Field fldQuestion1 = Text 26 and were all True/False responses.

After the user has ticked all the boxes I looped through them and built up a string similar to 0001110101011110 etc. this was then save into fldQuestion1.

Answers1 = iif(Me.ChkQ1a=True,1,0)
Answers1 = Answers1 & iif(Me.ChkQ1b=True,1,0)
etc

fldQuestion1 = Answers1

When the record was brought to screen a sub routine loops through the string and sets the value of the approriate control to the value in the string. Therefore ChkQ15.Value = Iff(Mid(fldQuestion1,5,1)=1,True,False)


By doing this you can store up to 36 answers in one field

Hope this is of use to you

Code Master

 

[KenHigg]

Forgive me if somebody else has already said this - 240 fields in a table screams normalize to me.


ken

 

[GaryPanic]

OK - there is either a normailisation problem or a set up problem


ok lets assume some simple solutions

what you are after are long tables not wide one

to get round this you need to look at your questions
lets take a medical survey you could break this down into chapters or questions types

lets simplify this even further
medical injury
a table for the head and table for the arms a table for the legs a table for the torso this gives 4 tables and questions that relate to them each has a FK being the PK of the underlying table

now you could sepearate your questions into say 10 chapters and each table would be a chapter - assume 25 questions = 250 questions in total
this allows you to add extra questions to each chapter should you need to .

so
custoemr statisfaction with chapter 1
service
chapter 2
treatment

there are occasions when you need to deviate from the norm - but try and steer away from this ...

it also depends on the questions asked
are they defined answer
yes/no
or ranged between 1 and 10


if they are free form then this is slightly different - and you might end up with a file bloat ..

 

[John_W]

I did a questionnaire database some years ago. The normalised table structure was something like this:

tblQuestionnaires
QRid
QRName

tblQuestions
Qid
QRid
Qtext

tblRespondents
Rid
RName

tblAnswers
Aid
Qid
Rid
AYes/No

I don't seem to be able to upload a picture unfortunately. But if you can follow the above, that should provide a good starting point.

 

[jrobinson]

The total characters allowed for all validation rules in an Access 2010 table in an mdb file is 34336 (=32*1073), close enough to 2^15 to make me suspicious that this is a hard limit. Determined this by creating a table with a field with a long rule, then replicating until Access barfed, then backing off to come up with the exact value.

My objective is to prevent non-printable characters in a table. There may be a more elegant way to do this, but right now the sure fire way seems to be

Not Like "*" & Chr(9) & "*" And Not Like "*" & Chr(10) & "*" And Not Like "*" & Chr(13) & "*" And Not Like "*" & Chr(8) & "*" And Not Like "*" & Chr(11) & "*" And Not Like "*" & Chr(12) & "*" And Not Like "*" & Chr(127) & "*"

Shortening this would be a big help (e.g. using (!a-z) type regexp syntaxes) if anyone has a suggestion.

 

[RuralGuy]

@jrobinson - are you aware you have tagged on to a ~9 year old thread?

 

[jrobinson]

Yep. Best fit I found to the issue I was facing.

 

[sonic8]

Your choice to revive this ancient thread is questionable at best.

Not Like "*" & Chr(9) & "*" And Not Like "*" & Chr(10) & "*" And Not Like "*" & Chr(13) & "*" And Not Like "*" & Chr(8) & "*" And Not Like "*" & Chr(11) & "*" And Not Like "*" & Chr(12) & "*" And Not Like "*" & Chr(127) & "*"

Shortening this would be a big help (e.g. using (!a-z) type regexp syntaxes) if anyone has a suggestion.

You can use that regexp-like syntax. The problem though is how to type those characters in a validation rule, as to my knowledge there are no regexp aliases like "\w" for whitespace available.
It is possible, if you use VBA to create the validation rule. Execute the following line in the Immediate Pane to add an validation rule that only uses 20 characters and has the same effect as your sample above.

CurrentDb.TableDefs("tblYourTable").Fields("yourFieldName").ValidationRule = "NOT LIKE ""*[" & Chr(8) & "-" & Chr(13) & " " & chr(127) & "]*"""

You should consider though, that this approach results in rules unreadable in the design view of the table.

 

[gemma-the-husky]

the maximum data size of a record is 4000 bytes. If you have large fixed length strings, that might be the problem.

 

[jdraw]

jrobinson,

Further to comments by others re: old post you've resurrected, and the approach offered by sonic8, here are a few things that may be useful.

I have mocked up a sample using sonic8's approach.

'---------------------------------------------------------------------------------------
' Procedure : testValidationRule
' Author    : mellon
' Date      : 30/03/2016
' Purpose   : related to
'  http://www.access-programmers.co.uk/forums/showthread.php?t=132076
'
' Applying a validation rule and related text info to all text fields in a table.
'Note:
' using the validation rule supplied by a poster sonic8
'---------------------------------------------------------------------------------------
'
Sub testValidationRule()

'CurrentDb.TableDefs("tblYourTable").Fields("yourFieldName").ValidationRule = "NOT LIKE ""*[" & Chr(8) & "-" & Chr(13) & " " & chr(127) & "]*"""
   
 Dim valRule As String
    Dim valText As String
10    On Error GoTo testValidationRule_Error

20    valRule = "NOT LIKE ""*[" & Chr(8) & "-" & Chr(13) & " " & Chr(127) & "]*"""
30    valText = " prevent non-printable characters in a table "

    Dim db As DAO.Database
40    Set db = CurrentDb
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
50    Set tdf = db.TableDefs("tblTestValRule_KILL")

60    For Each fld In tdf.Fields
70      If fld.Type = dbText Then
80          Debug.Print fld.name & " is text -applying validation rule and validation text"
90          fld.ValidationRule = valRule 'apply rule
100         fld.ValidationText = valText 'apply associated text info
110     End If
120   Next fld

130   On Error GoTo 0
140   Exit Sub

testValidationRule_Error:

150   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure testValidationRule"
End Sub

Tried a few queries,added a record using the table view, and this test routine

Sub tryUpdating()
    Dim db As DAO.Database
    Dim sql As String
10  On Error GoTo tryUpdating_Error

20  Set db = CurrentDb
30  Dim v1 As String: v1 = Chr(9)
    Dim v2 As String: v2 = "ABCabc"
40  sql = "UPDATE tblTestValRule_KILL SET tblTestValRule_KILL.fieldA1 = '" & v1 & "XYZ" & "';"
Debug.Print sql
50  db.Execute sql, dbFailOnError

60  On Error GoTo 0
70  Exit Sub

tryUpdating_Error:

80  MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure tryUpdating"
End Sub

Debug.print of SQL

UPDATE tblTestValRule_KILL SET tblTestValRule_KILL.fieldA1 = '  XYZ';

and received error

as per the attached jpg.

In future I'd recommend you start a new thread, mention the old thread as necessary, and describe your current issue, what you tried, what results etc.

Good luck.

 

[sonic8]

@Dave:
In what way does the record size limit affect the validation rules?
I did no research myself, but based on what @jrobinson wrote, I cannot see any connection.

 

[gemma-the-husky]

@Dave:
In what way does the record size limit affect the validation rules?
I did no research myself, but based on what @jrobinson wrote, I cannot see any connection.

I thought from the opening post, that the problem related to table design.
A record can be no larger than 4000 bytes, and I thought maybe the table design was being rejected for that reason.

The problem didn't seem to be worded as a runtime error.