Question VBA string bug? (1 Viewer)

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
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


what does the vbnewline achieve - this just CANNOT be correct

vbnewline certainly has nothing to do with problem as it already did before adding newline-characters (which is what vbnewline does). At some point I thought maybe VBA cant handle long string without linebreaks so i added them later, but without any help. I know what is wrong with the SQL when it executes. If you build a query and it fails to include ALL STRINGS then certainly a query is generated that is not valid. The question is why does VBA not execute all code correctly, not whats wrong with the query.


Dont think re-installing Access will help, because it fails on both XP and Vista business machine. Both with newly created MDB's. So wouldnt it would be rather strange that my app destorys Access on 2 machines with different OS?
 

Guus2005

AWF VIP
Local time
Today, 16:20
Joined
Jun 26, 2007
Messages
2,645
vbnewline certainly has nothing to do with problem as it already did before adding newline-characters (which is what vbnewline does). At some point I thought maybe VBA cant handle long string without linebreaks so i added them later, but without any help. I know what is wrong with the SQL when it executes. If you build a query and it fails to include ALL STRINGS then certainly a query is generated that is not valid. The question is why does VBA not execute all code correctly, not whats wrong with the query.


Dont think re-installing Access will help, because it fails on both XP and Vista business machine. Both with newly created MDB's. So wouldnt it would be rather strange that my app destorys Access on 2 machines with different OS?
Which points to my earlier observation that your database is corrupt.

Is this the only module where it happens?

Copy all objects to a new database except this (or maybe all) modules.
Copy all modules to txt files, create modules in the new database and paste your code from the txt files into them.

the txt file part is important because it supports the WYSIWIG principle.

The options left are diminishing...
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:20
Joined
Sep 12, 2006
Messages
15,613
but why would you need a newline in a sqlstrg - if you want a continuation you use the underscore character, which is completely different from embedding a control character IN a string

sqlstrg = "a long string " & _
"a bit more"

I am as sure as I can be, that the problem is the way you are building your sqlstrg - these newline characters may well be the the problem.
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:20
Joined
Aug 11, 2003
Messages
11,696
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 is no need to space every field, this was just a sample. The point I am trying to make: Make it readable! and keep it that way!
If you keep your code more readable even if on one line it will make it easier to debug and/or find a problem, most times even preventing the problem to start with.


i.e.
Code:
strSQL = ""
strSQL = strSQL & " SELECT B.SorteerCentrumID, B.PeriodeID, "
strSQL = strSQL & "        PER.Jaar,           PER.Week, "
strSQL = strSQL & "        PER.Tijdvak,        B.Datum, "
strSQL = strSQL & "        B.ProcesCode, "
Yes it consumes more space, but space is not a concern. More even you can put all this SQL into a seperate module you hardly ever need to touch turning your sql into functions or whatever. But having code readable is priority #1!

these newline characters may well be the the problem.
The new line also goes towards readabilty of the output.
But since it is (to be) readable in code itself you need not put it in the actual SQL.

Question:
Is it possible you post the actual SQL that is failing along with the code that should be generating it?? Maybe that can lift a bit of the mistery?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:20
Joined
Sep 12, 2006
Messages
15,613
mailman

i actually thought embedding vbnewline (is that chr(13)) in a sqlstrng might stop it working completely - mayber the sql interpreter just takes the sql up to the newline, or after the newline

ie

sqlstrg = "a string " & vbnewline & " a bit more "

is completely different to

sqlstrg = "a string " & _
" a bit more "
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
Pls tell me what formatting of SQL has to do with VBA not executing code correctly.

If it says:
Code:
strSQL = strSQL & "bvlabl INNER JOIN " & TABELNAAM & " more blabla"
and the result is that strSQL only contains " more blabla", what difference does it make that the query is not easily readable or does or does not have newline-characters?

But, indeed I added the newlines also so that when I copy generated SQL to query window its more readable.

Its obvouis a bug, because it randomly takes places in random modules at random times. It seems to work fine at this moment. Ive converted the 2 mdb's with data to 2002-2003 format. Created a new mdb in 2002-2003 format and imported all the forms, modules, etc. Compact it, compiled it, reboot pc, another compact and now it generates all SQL as it should.

The question is how long... ;-)
the application is still far from complete.

Anyway thx for commenting. Hopefully I've convinced some of you that VBA is doing something wrong instead of generating invalid SQL.

It seems that some1 else had similar issues, but no1 came with a real solution either:
http://techreport.com/forums/viewtopic.php?f=20&t=62576
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:20
Joined
Sep 12, 2006
Messages
15,613
because a sql string has to END UP being properly formatted, so it looks like


select * from sometable where somecondition

if you insert a control character in the middle so it looks like

select * from sometable NEWLINECharacter where somecondition

then it will clearly fail to be processed as correct sql

now the SQL interpreter MAY fail on the whole thing, or it MAY just take the bit UPTO the newline or even MAY take the bit AFTER the newline.

bit i am sure it will not work correctly.

SQL objects to missing spaces, and some fields have to be surrounded with "", or # characters - it certainly wont like spurious characters within the string
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
because a sql string has to END UP being properly formatted, so it looks like


select * from sometable where somecondition

if you insert a control character in the middle so it looks like

select * from sometable NEWLINECharacter where somecondition

then it will clearly fail to be processed as correct sql

now the SQL interpreter MAY fail on the whole thing, or it MAY just take the bit UPTO the newline or even MAY take the bit AFTER the newline.

bit i am sure it will not work correctly.

SQL objects to missing spaces - it certainly wont like spurious characters within the string

No it wont. It ignores newline characters.

You can say:

Code:
SELECT Column FROM table WHERE column2 = 23;

or

Code:
SELECT 
 
Column FROM table WHERE 
 
column2 = 23;

OR

Code:
SELECT              Column
 FROM
 
 table WHERE column2 = 
 
23;

which will all do the same.

And yes: Chr(13) = vbNewLine
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:20
Joined
Sep 12, 2006
Messages
15,613
aah - ok, i didnt realise you could do that - i will try this and see.
 

gemma-the-husky

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

OK another thought

if this fails to add the AS ID in some cases, could it be that strID is null? - this might affect the string concatenation


Could you post the final concetentated SQLStrg that is failing?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 28, 2001
Messages
26,999
The only thing that comes to mind is the syntax of quotes in VBA.

Suppose you do this:

A = """" & [table1]![Address] & """"
B = """" & [table1]![Addr2] & """"

You double the double-quotes because you want a single double-quote to remain when quoting has been processed. But now concatenate A & B, which gives you

"whatever""whatever else" - and you have a remaining doubled double-quote in the middle of a quoted string. Which promptly becomes a SINGLE double-quote character in the middle of that string. Keep on adding strings and the quoting becomes nightmarish.

I'm not going to try to parse that out, but if you add spaces here and there, you prevent adjacency of quotes that can somehow get eliminated during further processing. It's not REALLY a bug in my view - just an unintended and not-always-obvious consenquence of the quoting rules.
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:20
Joined
Aug 11, 2003
Messages
11,696
Pls tell me what formatting of SQL has to do with VBA not executing code correctly.
Nothing offcourse... however it will
1) Prevent (m)any programming error
2) Make it easier to support (for yourself as well) towards the future
3) Make it easier to show to other people (online or IRL) and have the understand what is going on


Also in the case of: & strID & " as ID "
If strID contains a reserved word or 'illegal characters' you may run into inexplainable errors. If strID is i.e. "From" you have a big problem


Are you trying to insert a field name, a number value or a string value?
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
Unfortunately converting to Access 2002-2003 format did not solve the problem. It happens again, for example in this line:

Code:
strSQL = "SELECT scbID FROM tbl_SomeTable IN '" & strDBOud & "' WHERE ditSCB = True;"

Here it just ignores the "SELECT scbID FROM tbl_SomeTable IN '" part.

It's one line of code. SQL syntax is fine, code formatting is not an issue, it's clear what is meant here. What the hell is VBA thinking here?

I probably wont get an answer here anyway, but was hoping someone had a similar problem once.
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
Which points to my earlier observation that your database is corrupt.

Is this the only module where it happens?

Copy all objects to a new database except this (or maybe all) modules.
Copy all modules to txt files, create modules in the new database and paste your code from the txt files into them.

the txt file part is important because it supports the WYSIWIG principle.

The options left are diminishing...

Have tried making new databases twice. Once by importing all objects, including the modules, and once by copying the code module by module. No help.

I do seem to notice that it mainly happens in 'new' modules (modules I recently added). All code from before always works, although in there there's a lot of SQL-generation too.
 

Rabbie

Super Moderator
Local time
Today, 15:20
Joined
Jul 10, 2007
Messages
5,906
Unfortunately converting to Access 2002-2003 format did not solve the problem. It happens again, for example in this line:

Code:
strSQL = "SELECT scbID FROM tbl_SomeTable IN '" & strDBOud & "' WHERE ditSCB = True;"

Here it just ignores the "SELECT scbID FROM tbl_SomeTable IN '" part.

It's one line of code. SQL syntax is fine, code formatting is not an issue, it's clear what is meant here. What the hell is VBA thinking here?

I probably wont get an answer here anyway, but was hoping someone had a similar problem once.
IN a similar configuration to yours I have been unable to recreate this error. Your code works correctly in my computer. IMHO this points to either a corrupt DB, A corrupt Access installation or something else funny on your computer.

Have you trie re-installing Office on your PC as this seem the only option you haven't tried yet. Do you have all applicable updates including any hotfixes?
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
IN a similar configuration to yours I have been unable to recreate this error. Your code works correctly in my computer. IMHO this points to either a corrupt DB, A corrupt Access installation or something else funny on your computer.

Have you trie re-installing Office on your PC as this seem the only option you haven't tried yet. Do you have all applicable updates including any hotfixes?

I have the issues on 2 pc's, 1 running XP, 1 running Vista business. Both with Office 2003 SP3. As working for a software company Im sure all necessary updates are installed. I just cant imagine that a corrupt Access is the issue. Im not having problems with other Access applications.

That the code will work on your machine, does not surprise me. I dont know if you have read the entire post, but I mentioned several times that by simply adding a space somewhere inside the string, it works fine again. But thats not solving things, thats working around it.
 

Rabbie

Super Moderator
Local time
Today, 15:20
Joined
Jul 10, 2007
Messages
5,906
Since you are using A2003SP3 doublecheck that you have applied the hotfix. I have followed this thread and I am aware that there are workrounds and I understand why you want to fix the underlying issue.

The fact that older modules work correctly and the issue is confined to more recent additions suggests that the issue is update related and that some code is not being compiled correctly all the time. I am sure that adding the extra space in the string triggers a new compile which is fixing the problem.
 

steeffie

Registered User.
Local time
Today, 16:20
Joined
Mar 11, 2009
Messages
29
This is a nice one too:

Code:
strSQL = IIf(blnPreSelectie, "WHERE", "AND")

After this line strSQL is still empty ("" or vbNewline)!


My colleague is checking if we have that hotfix installed. Thx for the tip.
 

Users who are viewing this thread

Top Bottom