Question VBA string bug? (1 Viewer)

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
I've got an Access application which is behaving rather strange. It has trouble handling strings correctly in VBA at random places.

For example:
Code:
strSQL = "SELECT " & strOmschrijving & " AS Omschrijving, CStr(V.Jaar) + ' ' + Format(V." & strPeriode
strSQL = strSQL & ",'00') AS Bereik1, " & CStr(intNiveau) & " AS Niveau, " & strID & [B][U]" AS ID, "[/U][/B]
strSQL = strSQL & CStr(GeefMaxSortering + 1) & " + (3000-V.Jaar)*10000 - V." & strPeriode & " AS Sortering, " & vbNewLine

When stepping through the code it leaves out the bold part " AS ID, ". But when I add an extra space somewhere in that line, than it behaves correctly.

Similar problems in other parts of the application too. I've already tried decompiling etc, to clean things up. I even made a new mdb and imported all objects and code, but no effect.

Has someone every seen anything like this too? I cannot imagine being the first to have this problem.

Another example would be:
Code:
strSQL = [B][U]"SELECT B.SorteerCentrumID, B.PeriodeID, PER.Jaar, PER.Week, PER.Tijdvak, B.Datum, B.ProcesCode, "[/U][/B]
strSQL = strSQL & "Sum(Nz(B.WaardeOchtend,0)) AS WaardeOchtend, Sum(Nz(B.WaardeMiddag,0)) AS WaardeMiddag, " & vbNewLine
strSQL = strSQL & "Sum(Nz(B.WaardeAvond,0)) AS WaardeAvond, Sum(Nz(B.WaardeNacht,0)) AS WaardeNacht, "
strSQL = strSQL & IIf(blnIsSorteerdag, "Sum(Nz(B.WaardeNachtVolgend,0)) AS WaardeNachtVolgend ", "NULL AS WaardeNachtVolgend ")

Here it leaved out the entire first line. When stepping through the code the string would still be empty after executing only the first line. But again, by adding a space somewhere inside the string, it would work again.

This is Access 2003 SP3, VBA6.3.
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
This is usually a sign from Access that your database is getting corrupted.

What i do is close the database and all other access applications i have opened, just to be on the safe side. Create a backup of the original application (FE) as well as the database (BE). Open the application with shift pressed and compact and repair. Do the same with the backend. Compact and repair is sometimes the trigger for the application to get corrupted, that's why i created a backup. When that happens, copy the backup to overwrite the corrupted original open a new database and copy all objects and data from the original database.

It sometimes helps if you simply reboot without doing all the above...

HTH:D
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
I've tried all the things you mentioned, but that doesnt really solve things permenantly. It might help fort a short time, but then it would do something stupid like this again, only probably somewhere else in code. I am talkin about doing compact & repair and rebooting like 10 times a day.

And even if this is the only solution, how certain can I be that when things seem to work and we bring the application to production these things will not arise again? Is it only because Im changing VBA all the time?
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
It is very odd behaviour and it happens to me every six weeks or so. And always when i am working on it, e.g. changing code and such.
It never happend on the production database. In this case it doesn't matter if it was compiled to an MDE database or not.

Is your database linked or changed in some exotic way to other databases?

In your case perhaps it is wise to reinstall Access again.

HTH:D
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
It uses linked tables from 2 other mdb's. Not really to exotic I think.
Reinstall Access? Really cant imagine that to help as things go wrong when I copy to other pc as well. Even different OS doenst solve things, it both goes wrong with XP and Vista business.
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
Compact and Repair is not the same as starting a new database and copying all objects to it. It is much more. Perhaps the "more" part is where you import your problems also.
Did you actually try to create a new database and copy all objects to it?

Is it possible to post a sample database?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:20
Joined
Aug 11, 2003
Messages
11,696
Just a general note, not a solution to your problem...

How about writing your code so it is more readable???
something like so:
Code:
strSQL = ""
strSQL = strSQL & " SELECT B.SorteerCentrumID, "
strSQL = strSQL & "        B.PeriodeID, "
strSQL = strSQL & "        PER.Jaar, "
strSQL = strSQL & "        PER.Week, "
strSQL = strSQL & "        PER.Tijdvak, "
strSQL = strSQL & "        B.Datum, "
strSQL = strSQL & "        B.ProcesCode, "

This will make your code much more maintainable.... Aside from this, very strange problem.. Never seen this behaviour before, a standard compact / reboot usually does the trick for me when ever something strange goes on.

Greets from Amsterdam :) (as you are dutch too )
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Sep 12, 2006
Messages
15,613
" AS ID, ".

You mention this seems not to happen when you put an extra space. SQL is unforgiving about punctuation, so i would set this out as

" AS ID , "

with extra spacing, before trying anything else.

------------
Having said this, I struggle to see how the string would not be assigned when stepping through code - that just seems nonsensical. Do you really have that behaviour, or do you just mean the SQL doesnt execute properly?
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
I did create a new database one time already and copy all objects, did not help. It immediately started again.

As it is an application for a large Dutch company we are working for, I cannot post the database.

As to mailman's idea: The SQL I'm generating is that large that placing every field on one line would make my modules too large.

There has to be a solution. Why does the problem only concern string-concatenating? Although Access crashes a lot too, not always but also at random. Doing a few decompile and compacts would solve that.

Grtz from Beverwijk.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Sep 12, 2006
Messages
15,613
" AS ID, ".

You mention this seems not to happen when you put an extra space. SQL is unforgiving about punctuation, so i would set this out as

" AS ID , "

with extra spacing, before trying anything else.

------------
Having said this, I struggle to see how the string would not be assigned when stepping through code - that just seems nonsensical. Do you really have that behaviour, or do you just mean the SQL doesnt execute properly?

try putting a msgbox after each line to see what the sqlstrg looks like
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
You mention this seems not to happen when you put an extra space. SQL is unforgiving about punctuation, so i would set this out as

" AS ID , "

with extra spacing, before trying anything else.

------------
Having said this, I struggle to see how the string would not be assigned when stepping through code - that just seems nonsensical. Do you really have that behaviour, or do you just mean the SQL doesnt execute properly?

It seems nonsensical to me too. ;) I'm a MSCD in VB6 for years now and havent seen anything like this in any VB or VBA-environment.

What I mean is if I change "[space]AS[space]ID,[space]" to "[space]AS[space]ID,[space][space]" it would work. Althugh changes would be that in another module a similar problem could arise.

"Its like putting plysters on wounds...."
 

Guus2005

AWF VIP
Local time
Today, 12:20
Joined
Jun 26, 2007
Messages
2,645
Okay, this is not a solution to your problem but a workaround: Use parameter queries; no string concatenation (which is slow to start from) and your code is easier to maintain.

Grtz from Utrecht.
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
It's an application that build reports dynamically. Based on options and filters more or less data is represented with different group by's. So dynamically creating a SQL statement gives me all the control i need, which would be very hard to achieve with stored query's.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Sep 12, 2006
Messages
15,613
steefie, did you look at this, from my previous post

Having said this, I struggle to see how the string would not be assigned when stepping through code - that just seems nonsensical. Do you really have that behaviour, or do you just mean the SQL doesnt execute properly?

try putting a msgbox after each line to see what the sqlstrg looks like
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
steefie, did you look at this, from my previous post

placing msgbox between the lines causes it to work fine but failing elsewhere. I dont see difference though in showing msgbox instead of stepping through code and look at values in immediate window.

Im really staring to lose my mind. Costing me so much time. Starting from scratch on other machine (XP instead of Vista business) does not help either.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Sep 12, 2006
Messages
15,613
because i dont think it IS failing to construct the SQLSTRG

I think the sqlstrg just isnt correct to do what you want.


How are you ACTUALLY executing the finished SQLSTRG


if you are doing

Code:
docmd.setwarnings false
docmd.runsql sqlstrg
docmd.setwarnings true

this will just fail, and not report any error

instead try this, which will report any invalid sql

Code:
on error goto fail
currentdb.execute sqlstrg, dbfailonerror

'''


exithere:
 exit sub

fail:
msgbox("Error: " & err & "  Desc: " & err.description)
resume exithere
 
Last edited:

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
I first just execute the code. It will stop at CurrentDB.Execute strSQL, because the SQL is incorrect. The reason why it's incorrect is because it fails to concatenate all strings correctly. When i then step through the code line by line at some piont it just ignores or steps over a certain part of the string.

For example when the code is :
strSQL = "SELECT acolumn, anothercolumn, " & vbnewline
And I execute this line, the immediate when tells me that strSQL is empty ("").

I can imagine that you just think it's some sort of newbee mistake, but its not.
 

steeffie

Registered User.
Local time
Today, 12:20
Joined
Mar 11, 2009
Messages
29
Maybe Access or VBA fails to handle multiple modules with numerous public procedures and functions correctly. However i find it strange that it only fails to handling strings correctly.
 

Rabbie

Super Moderator
Local time
Today, 11:20
Joined
Jul 10, 2007
Messages
5,906
I tested your second example on a similar configuration A2003 and Windows XP Professional patched to the latest level and everything seemed to work correctly.`

I agree with Guus that your error suggests either DB corruption or your version of Access is corrupt or is missing a patch.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Sep 12, 2006
Messages
15,613
see my last post - add the dbfailonerror bit, and it will show you what is wrong with the sql

with regard to your last post

strSQL = "SELECT acolumn, anothercolumn, " & vbnewline

what does the vbnewline achieve - this just CANNOT be correct
 

Users who are viewing this thread

Top Bottom