ValidationRule DAO extra character problem

[Christine]

I am trying to set the ValidationRule for a table field using DAO but
get an extra character at the end, that I cannot get rid of. For
example:
fld.Properties("ValidationRule") = ">0" results in 3 characters being
in the property. The first two are correct (">0") but the extra
character is an ascii 48, which shows up as a box when I hover over
the value in debug mode.

In searching the newsgroups I have seen complaints of an extra chr(0)
at the end of the ValidationRule. I don't believe that is the same as
the ascii 48, but maybe it is. In any case, there does not seem to be
any way for me to correct this. But it is causing programmatic
problems, and driving me absolutely crazy. It seems like it must be an
Access bug.

Does anyone know of a solution for this? None of the chr(0) posts I
saw had a solution. I can't believe how ridiculous this problem is and
would really appreciate help. Please help me if you can.

I am using Access 2000. Is there any other way to set the
ValidationRule programatically? And/Or, if this is a bug, is it fixed
in Access 2002? or 2003?
Thanks in advance.
Christine

 

[Allen Browne]

Hi Christine.

I am unable to reproduce this problem in Access 2000 (nor in Access 2003).

I used:
fld.Properties.Append fld.CreateProperty("ValidationRule", dbText,
">0")
and received a 2-character result.

 

[Christine]

Hi Allen,
Thank you so much for taking the time to address my problem.
For some reason, we are not getting the same results.

When I try to do as you suggest, I get the Error:
Error: 3367 Cannot append. An object with that name already exists
in the collection.

However, if I try to delete the property so that I can add it, I get:
Error: 3384 Cannot delete a built in property.

I wonder if there is a setting someplace that can control whether
ValidationRule is a built in property. In browsing the news groups
about this problem, I did see others having my same problem (Cannot
append) also.

Once again, thanks so much and I am hoping you will stick with me on
this and perhaps have some more ideas.

G'day, Christine

 

[Allen Browne]

Hmm. Okay, here is the full code I used, which actually creates the property
only if it does not already exist:

Function SetPropertyDAO(obj As Object, _
strPropertyName As String, _
intType As Integer, _
varValue As Variant, _
Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
" not set to " & varValue & ". Error " & Err.Number & " - " &
Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

 

[Christine]

Dear Allen,
Thanks so very much for sticking with me on this.

Your SetProperty function is actually very similar to the one I used
and since the ValidationRule property always does exist, since it is
builtin, I think it would always go through the first branch of your
If statement, so it seems as if we are doing the same thing. The
question is, are we really getting different results?....

It has occured to me that my initial post was not very clear and
precise and that I may have ommitted the most important piece of
information which is that the extra character I get is actually
invisible. If I open the table in design view and look at the property
I do not see a 3rd character. However, I know it is there. Perhaps the
following Debug Window items will help clarify more exactly:

1. Set the property directly, through code:
currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
= ">0"
-----------------------------------------------------------------------------
2. Print out the contents of the property surrounded by single quotes
to show that there is an extra character:
?"'" & currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
& "'"
'>0 ' 'Note what looks like a space before the terminating
quote
----------------------------------------------------------------------------
3. Get the length of the property value, confirming that it is 3
characters and not two:
?len(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"))
3
--------------------------------------------------------------------------
4. This is the thing that boggles my mind. I am asking if the property
value is equal to what I wanted to set it to and it is saying that it
is, and yet, as we have just seen, it is not really because one is a 2
character string and the other is a 3 character string:
?currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule")
= ">0"
True 'This response really shocks me!!!!!!!!
'---------------------------------------------------------------------
5. Looking at the ascii values of each of the characters and trying to
understand. It seems as if those other posts that talked about Chr(0)
at the end were the same exact issue.
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),1,1))
62 'First character (>)
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),2,1))
48 'Second character (0)
?asc(mid(currentdb.TableDefs("tblATest").Fields("SumID").Properties("ValidationRule"),3,1))
0 'Third character - my invisible character
?asc(chr(0)) .See how chr(0) gets the same asc result as my
invisible char
0
?asc(0)
48
?asc("0")
48
-------------------------------------------------------------------
If I then go to another field and set the property manually through
the user interface of the design view of the table and then go back to
the Debug window and run through the same steps, I see that this time
I do only have a 2 character string. In other words I cannot reproduce
via code the same result as setting the property manually.

Sorry for the inordinate amount of detail. But since you were kind
enough to take the time and interest to respond I thought it was only
fair to give you all the clues and maybe together we can get to the
bottom of it. If you could try to reproduce my Debug Window steps, I'd
be most interested to see if you get the same results or not.

Once again, thanks so very much for your help.
Regards,
Christine

 

[Allen Browne]

Christine, what is the version of msaccess.exe (under the MS Office folder)
and msjet40.dll (in Windows\System32)?

I tested using A2003 with JET 4 SP8, and Len() returns 2 characters, so I am
not getting the same result.

The 3rd character is the null character (vbNullChar). Access and Windows
were probably written in C, and C often terminates strings with a null char,
so it's possible that this issue relates to the null char being returned
instead of truncated. That is consistent with the rest of your findings,
e.g.:
? Len("A" & vbNullChar)
2
? ("Jones" & vbNullChar = "Jones")
True

So, the question now is, Why does your version of Access wrongly assign or
return (we don't know which yet) the null char, when mine does not. Let me
know your versions of the 2 files above.

 

[Christine]

msjet40.dll version: 4.0.6218.0
msaccess.exe version: 9.0.0.6620

Christine

 

[Allen Browne]

Your Access 2000 looks like it is up to date, but you need the JET 4 Service
Pack 8.

Go to http://support.microsoft.com, the Downloads section, and get the JET 4
service pack. That should take you up to 4.0.8xxx.0

I have just tested this again with Access 2000, and it does work correctly
on my system, so hopefully the JET 4 service pack will solve this for you.

 

[Christine]

Hi Allen,
I have updated to Jet 4 SP 8 as you suggested and it has not fixed the
problem. I still get the null at the end of the string for the
ValidationRule when I set it programmatically, unless I set it to "",
in which case it does not add the extra character.

In any case, at this point in time, I am willing to resign myself to
this and build into my program handling of the extra character. As far
as I can see, it does not hurt functionally to have the extra chr(0)
at the end. The validationrule seems to behave the same way as it
should without it.

The application I am working on that revealed this inconsistency
between a manually set ValidationRule and a programatically set one is
to be able to automate the update of a back end structure (tables,
relationships etc). I am developing an application with a split FE/BE
architecture. Before I release my first version of the Product and
sell it to multiple customers (I hope) I wanted to have a technique
developed that would enable the automated schema update for the
customer's BE, when I release patches or upgrades in the future.
Because the customer's BE would already contain their data it thus
could not be replaced the way the FE could. The concept is similar to
being able to run an SQL DDL script, but not quite so simple because
some things cannot be done by DDL (such as changing the ValidationRule
etc), but I'll refer to it as a script, anyway, to update the BE
schema. In developing this AutoSchemaUpdate, I have developed a tool
to iterate through the DB objects of a 'Master BE' that has all the
tables, properties, relationships that the latest version of the FE
requires and would compare each of these to the Master BE of the
previously released FE and generate a 'Script' that would be executed
at the customer site to make their BE compatible with the new FE. The
intention is that a BE that had been modified manually (schematically)
would be identical in structure to one that had been processed by the
script execution. What I plan to do now, is simply consider a
ValidationRule that has the extra null result in the same Checksum as
a ValidationRule that does not have the null. Checksum is my own way
of coming up with a unique number to represent each DB object, and
ultimately the DB itself, so that I can see at a glance that 2 DB's
have the same identical structure. As part of the AutoSchemaUpdate at
customer site, there will be a final check on completion of the
process to make sure the DB checksum matches what the FE thinks the BE
checksum should be. As I start testing on various computers and beta
testing with different customers, no doubt some will act the way my
system does and some will behave the way yours does, so my program
must be able to reduce both to the same checksum, regardless.

Well, now that I've probably given you way more information than you
ever wanted to know. I want to thank you once again for all your time
& patience. Being able to dialog with you in this way, has been very
helpful in helping me to understand what is going on. So unless you
happen to think of some other reason why we get different results, I
am willing to end this thread. It is always nice to know WHY something
happens, even if one cannot control it.

Thanks again, and best regards,
Christine