myString = "UPDATE " results in an empty myString

Question

In MS Access assigning a string literal will sometimes result in an empty String

The following code

Public Sub test()
    Dim myString As String
    myString = "UPDATE "
    Debug.Print "'" & myString & "'"
End Sub

results in

''

this is freaking me out. It only happens sometimes. Other times the "UPDATE " will work, but myString = "tblCategorie" won't. It needs to be exactly that String. If "UPDATE " fails, then "update " will still be okay.

I'm using MS Access 2003 11.8204.8221 SP3 Does anyone have the same problem?

(before you say: dump access! we're already doing that, but still in a transitional phase. I'm not really expecting anyone to come up with a decent answer, but a guy can hope)

[UPDATE]: Thanks for all the comments! let me just put really clear though that

1. it's not a typo. The same code sometimes works, and sometimes doesn't.
2. It's run in isolation so it's no global variable problem.
3. I have updated the sample to be the exact code that fails/doesn't fail. It's a literal copy. I test it by pasting it in a module and typing 'test' in the direct screen.
4. It first popped up in code that had worked flawlessly the past half year,
5. It is really the string assignment that fails (I can check that by putting a break on the assignment statement)
6. I generate my database from text exports, so it can't really be a corruption problem. (It could be, but it's not one that I can fix by compressing etc.)

Answer 1

Is the code you posted a copy of the code that is failing, or a reasonable facimile? I'm wondering if someting was lost in paraphrasing, as I don't see anything at all wrong with the code you posted.

Answer 2

Just a blind guess... are you sure you are typing the second "myString" correctly?

Beacuse il you don't (ex.

Debug.print "'" & mySting & "'"

) Access won't complain but it will create an empty variable...

Answer 3

Are you using On Error Resume Next i.e. is the assignment failing silently? That said, I can't think why an assignment of a String literal to a String variable would fail, which begs the question: is mySting really typed as String?

UPDATE: I see from your UPDATE (pun intended?) that my guesses are off. I simply cannot see how your code could fail to print anything other than 'UPDATE '. Perhaps you should now view this as an opportunity to abandon dynamic SQL in favour of prepared statements or, preferably, PROCEDURES (sure, in ACE/Jet's stored procs are limited to a single SQL statement but at least they keep the SQL code in the correct place i.e. the db).

Answer 4

Dump access! :-)

Something is FUBAR.Have you tried a Compact and Repair on the database?

The other thing I would try is to run a copile on the VBA code (Debug->Compile Access[X]).