2

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.)
Community
  • 1
  • 1
Jauco
  • 1,320
  • 13
  • 23
  • Is it that the Debug.Print is a problem? Try seeing the value of variable in the watch window. – shahkalpesh May 11 '09 at 14:55
  • Why would you want to enclose a SQL string in single quotes? – David-W-Fenton May 11 '09 at 23:38
  • @David W. Fenton: one would do this when printing a string to the Immediate Window to make it easier to do a visual check of whitespace padding. Also, I think it is just an example; I mean, why read a sting literal into a string variable if the only point of the sub procedure is to print to the VBA Immediate Window which (we hope) will never be seen by users. – onedaywhen May 12 '09 at 09:03
  • Just to show that the string is empty, without the empty quotes you'd see an empty line, and wouldn't know whether something was indeed printed. (This is my testcase that identifies the problem without any distractions, not the production code. This is the shortest piece of code that I can write that actually fails, and clearly shows that it does fail) – Jauco May 12 '09 at 09:12
  • @ondedaywhen: Indeed, you answered while I was typing :) – Jauco May 12 '09 at 09:13

4 Answers4

1

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).

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • While that would solve the query problem, I use strings in other places too. I upvoted you since this is about as close to a solution as we'll probably get. (And anyway, the rewrite in a real programming language is coming along nicely :-) – Jauco May 13 '09 at 09:38
0

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.

Rich.Carpenter
  • 1,056
  • 1
  • 9
  • 21
  • While the code that originally gave the error is different, this is the test case that I used to narrow down the error. If I enter this code (or just ?"UPDATE " in the direct screen) the described behavior happens. Sometimes. – Jauco May 11 '09 at 13:56
0

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...

Manrico Corazzi
  • 11,299
  • 10
  • 48
  • 62
  • I have option explicit on so access would complain. Furthermore, if I put a breakpoint on the assignment statement I can see that the variable is empty. – Jauco May 11 '09 at 13:57
  • Curioser and curioser... have you considered dumping Access? ;-) Just kidding... sorry, at the moment I've got nothing else... – Manrico Corazzi May 11 '09 at 14:12
0

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]).

BIBD
  • 15,107
  • 25
  • 85
  • 137