Data corruption when using UCase() function in DB.execute "UPDATE SQL" in Access 97 Runtime
Mark Cubitt
field to upper case using the following code. The code works fine in
my Development full Access environment, but not on a user's PC where
the field is emptied for all records in the table being updated (I am
not sure whether to null or a zero length string). There is no error
reported in the VB code.
The function is listed below.
The output from the msgbox statements (added for debugging purposes)
is:
First msgbox "Before place: PWL After"
Second msgbox "Test upper case convert from 'rt' to 'RT': RT"
The function returns "OK"
Function UcaseCodes()
On Error GoTo Exit_UcaseCodes
v_before = DFirst("[PLACE]", "Captures", "[SPEC]= 'Robin'")
Dim dbWs As Object, dbCurrent As Object, tmptable As Object,
tmpField As Object, tmpProperty As Property
Set dbWs = DBEngine.Workspaces(0)
Set dbCurrent = dbWs.Databases(0)
dbCurrent.Execute "UPDATE [Places] SET [PLACE] = ucase([PLACE])"
UcaseCodes = "OK"
MsgBox "Before place: " & v_before & " After: " &
DFirst("[PLACE]", "Captures", "[SPEC]= 'Robin'")
MsgBox "Test upper case convert from 'rt' to 'RT' : " &
UCase("rt")
Exit_UcaseCodes:
Exit Function
Err_UcaseCodes:
MsgBox "UcaseCodes: " & Err.Number & " " & Err.Description
UcaseCodes = "NOTOK"
Resume Exit_UcaseCodes
End Function
Albert Kallal
will show up in your "test upper" code. However, this does not always
ensure you will not have problems with the expression builder. In other
words, I seen code function such as left$, right$ etc. work, but when
functctions used in the expression "services" (which queries use), then
they don't work. The "expresson" services is used when your code is NOT
compiled...such as in Queries, and in text boxes in a form.
Thus, you either want to check any references in code, or when possible
use late binding (this has fixed many problem for me).
The other culprit when I saw this problem (ie: functions still worked, but
I had broken references) was the target machine had *different* version of
jet. The solution here is to update *your* pc to the latest jet fix (this
is NOT the latest sr2b patch). This jet fix should be installed on your
pc, and also the target. While it was not bugs in jet that fixed my
reference problem, it was the fact that the target pc had a difference
version of jet (and thus the expression service was crapping out).
Thus, all my office install are
Office97 (calendar control selected, as it does not install by default)
Then sr1 (if required)
Then sr2b
Then jet35sp3
The above results in a real smooth and reliable set-up. In fact, if the
above compounds are not installed, I will NOT permit my software to be run
on that machine. I had cases where access reported NO broken references,
but queries were giving *different* results on different pc's. Anyone who
tries to run access, and not ensure the *same* set-up on the target is
asking for big trouble.
In addition, change any code to late binding if you are using Word, or
Outlook references in code. This is especially important as so many new
machines come with a small office, or even just Word 2000 installed, but
are still using older office components (such as a97).
As mentioned, in 95% of these "broken" references case, you will find that
left$, right$ etc do not work. However, the nasty one is where left$,
right$ etc work in code, but in Queries (and in forms control expressions)
they do not work.
The following ms knowledge document explains this exact problem and
solutions..
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q194374
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
Bob Barrows
wrote:
>My Runtime Access 97 application runs a pice of SQL to convert a given
>field to upper case using the following code. The code works fine in
>my Development full Access environment, but not on a user's PC where
>the field is emptied for all records in the table being updated (I am
>not sure whether to null or a zero length string). There is no error
>reported in the VB code.
>
Sounds like a mismatched references problem. On the machine where it
doesn't work, try opening a code module and "refreshing" the database
references (Select a new reference, click ok, then go back and
deselect the unnecessary reference). There's a KB article : Q194374,
about this problem, one of the main causes of which is:
----begin quote from KB-------
You referenced a type library on your development computer, and then
moved the database to a computer that has a newer version of the type
library installed.
-----end quote
If there are any References marked MISSING, uncheck them and scroll
down the list until you find the equivalent reference and check that
one. If you can't find it in the list, and you know the dll is
installed on the PC, you can use the Browse button to locate it on the
hard drive. Access wil register it once you show it where the dll is.
You may have to install the missing dll onto the computer if you can't
find it in the list or on the PC's hard drive.
HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.
Mark Cubitt
> My Runtime Access 97 application runs a pice of SQL to convert a given
> field to upper case using the following code.
>
As Albert has observed we are in the "the nasty one is where left$,
right$ etc work in code, but in Queries they do not work".
In which reference are the "expression services" that he refers to
held or are they part of Jet? If it is the latter then the JET35SP3
may well sort the problem (I will test this with my 'problem' user.)
The only references that I have in my database are the basic ones:
Visual Basic for Applications, MS Access 8.0 Object Library and MS DAO
3.5 Object Library plus a library database. It is in this library
database that the problem procedure resides. Its References are the
three basic ones just listed. (I have already been through the Compile
error problem when this library database couldn't be found.)
I have seen that comctl and comctl32 are mentioned frequently in the
usegroup in similar contexts. The target runtime machine does have a
much later (v4 rather than v3 edition of this ocx), but I didn't think
that this would be involved in "expression services". The target
runtime machine also has Access 2000 installed, but to a different
directory structure and different registry entries (using Wise Install
Builder v8 and SageKey scripts).
I hadn't heard of late binding, but from browsing through te usegroup
this just invloves using Object as a type in the dim expression. By
chance I think I am already doing this, unless I have misunderstood?
I don't think that I can use the Refresh references technique from KB
article : Q194374 on the target computer as this would require a
compile and save all modules and as my database is secured to the
extent that Admin users have no Design permissions? (This has been
done to protect the code from bootlegging.) Is there any way around
this should a refresh prove to be the problem?
Bob Barrows
wrote:
>I don't think that I can use the Refresh references technique from KB
>article : Q194374 on the target computer as this would require a
>compile and save all modules and as my database is secured to the
>extent that Admin users have no Design permissions? (This has been
>done to protect the code from bootlegging.) Is there any way around
>this should a refresh prove to be the problem?
Use the code in the KB article to do it.
The point is - the database must be recompiled to solve this issue, so
you're going to have to bite the bullet on this one.
You're mistaken if you think the security you've implemented will
prevent the code from being bootlegged. Anyone who bootlegs code is
certainly goinig to be knowledgeable enough to get past Access
security in a few minutes. The only sure way to protect your code is
to distribute mde files.
Tony Toews
>The other culprit when I saw this problem (ie: functions still worked, but
>I had broken references) was the target machine had *different* version of
>jet.
Interesting. I'll keep this in mind. Thanks
>The solution here is to update *your* pc to the latest jet fix (this
>is NOT the latest sr2b patch). This jet fix should be installed on your
>pc, and also the target.
I've now put in a DLL version check on the DAO DLL just to double check.
What is quite interesting on this one is that the situation at my one clients. They
have very good, concientious sysadmins. The network is quite stable. And things are
up to date. Thus I was quite surprised to find that three out of the twenty users
didn't have Jet35SP3 installed.
Tony
----
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm