Access object name limits

by Michael S. Kaplan, published on 2006/06/25 16:11 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/06/25/646769.aspx


A few days ago, Eric Rucker posted about the various limits in Access 2007 in this post, including my favorites:

Number of characters in a table name 64
Number of characters in a field name 64
Number of characters in a text field 255
Number of characters in a validation rule 2048
Number of characters in a validation message 2048
Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property is set to Yes. 4000
Number of characters in a field property setting 255
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of characters in a cell in the query design grid 1024
Number of characters in a parameter in a parameterized query 255
Number of characters in a SQL statement Approx 64,000
Number of characters in a label 2048
Number of characters in a text box 65535
Number of characters in a SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control. 32750
Number of characters in a condition 255
Number of characters in a comment 255
Number of characters in an Action Argument 255

As to why these are my favorites?

Well, because each use of "Number of characters" actually ought to read "Number of UTF-16 code units" since e.g. the string

ểểểểểểểểểểểểểểểểểểểểểể

is actually 66 of those types of "characters" long even though any user would reasonably count it up as being of length 22.

Unfiortunately, Access and Jet don't consider that object and one with this name:

ểểểểểểểểểểểểểểểểểểểểểể

(the first is in normalization form D, the second in form C).

Anyone want to calculate the number of possible unique object names one can create with just this one character? :-)

 

This post brought to you by (U+1ec3, a.k.a. LATIN SMALL LETTER E WITH CIRCUMFLEX AND HOOK ABOVE)


# Anthony Mills on 25 Jun 2006 9:26 PM:

Well, there are two forms in Form C and one in Form D (I think) for each one, so that's 3^22 = 31,381,059,609 names with 22 characters. With 1-22 characters, 47,071,589,412 names.

For the room available in the 64 characters you can name a table with, you could create 5.15 x 10^30 tables, each one named uniquely.

# Michael S. Kaplan on 25 Jun 2006 9:36 PM:

Well, two things --

1) there are those intermediate forms I have talked about (one in this case)

2) You can't use more than 22 or else you will overrun the 64-"char" limit for the form D strings.

:-)

# Anthony Mills on 28 Jun 2006 10:36 AM:

Well then I guess you can't do 22 characters for the pure Form D ones either. You could use a maximum of 21 Form D characters and one Form C character.

Interestingly, Firefox screws up the Form D rendering; IE gets both right.

# Michael S. Kaplan on 28 Jun 2006 12:27 PM:

Yep, the technical limitations if the condition is that all strings must look the same is 21 of these "characters", which translates to 63 code points in Form D, 21 of them in Form C, and all of the in betweens for the rest of them.

Plus you could tack on that one Form C character at the end like you were talking about. :-)

go to newer or older post, or back to index or month or day