SQL does not work in VBA, but does with tweaking in QBE

[Colleyville Alan]

I have an application that uses SQL. I am writing a new piece to it, so I
used the same approach that worked before. I set up a string and add to it,
i.e. strMySql = "some Sql Statement" and then strMySql = strMySql + "some
other new statement".

The program keeps crashing saying I am running an invalid operation. I go
to debug.print and copy the contents of the strMySql to the sql window.
Sure enough, where the sql statement wrapped in the Immdiates window, the
statement got split to a new line in the SQL window. I take out the space
at the end of the line and the next line snaps back into place to form a
coninuous statement once again. At this point, I can change back to the QBE
window and the statement is set up properly and will execute. But in VBA it
will not and I think that part that wraps in the Immdediate window has
nothing to do with my code.

Can anyone venture a guess as to what I am doing wrong?
Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you putting spaces in the SQL statements in your VBA strings.
IOW:

strMySQL = "Select * From myTable"
strMySQL = strMySQL & "Where X = Y"

This will concatenate to

Select * From myTableWhere X = Y
^^

See the error where I didn't put a space after the myTable in the
first strMySQL assignment. This will cause an SQL syntax error when
the SQL command is attempted.

Therefore, put extra spaces at the end of your strings to ensure the
concatenation properly separates all elements of the SQL statement.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAo6Q4echKqOuFEgEQJ1QACg4hEgrk5utCbp/dcRlJANjyNRUxkAn0iI
ofVMmEVfaiVXyyhVbcwK4fG+
=2310
-----END PGP SIGNATURE-----
Colleyville Alan wrote:

I have an application that uses SQL. I am writing a new piece to it, so I
used the same approach that worked before. I set up a string and add to it,
i.e. strMySql = "some Sql Statement" and then strMySql = strMySql + "some
other new statement".

The program keeps crashing saying I am running an invalid operation. I go
to debug.print and copy the contents of the strMySql to the sql window.
Sure enough, where the sql statement wrapped in the Immdiates window, the
statement got split to a new line in the SQL window. I take out the space
at the end of the line and the next line snaps back into place to form a
coninuous statement once again. At this point, I can change back to the QBE
window and the statement is set up properly and will execute. But in VBA it
will not and I think that part that wraps in the Immdediate window has
nothing to do with my code.

Can anyone venture a guess as to what I am doing wrong?
Thanks

[Colleyville Alan]

"MGFoster" <[e-mail]> wrote in message
news:aT******************@newsread1.news.pas.earth link.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you putting spaces in the SQL statements in your VBA strings.
IOW:

strMySQL = "Select * From myTable"
strMySQL = strMySQL & "Where X = Y"

This will concatenate to

Select * From myTableWhere X = Y
^^

See the error where I didn't put a space after the myTable in the
first strMySQL assignment. This will cause an SQL syntax error when
the SQL command is attempted.

Therefore, put extra spaces at the end of your strings to ensure the
concatenation properly separates all elements of the SQL statement.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAo6Q4echKqOuFEgEQJ1QACg4hEgrk5utCbp/dcRlJANjyNRUxkAn0iI
ofVMmEVfaiVXyyhVbcwK4fG+
=2310
-----END PGP SIGNATURE-----
\
Yes, I'm putting in the spaces. There were a couple of errors where I had
not put in the spaces, but I got those fixed before I posted this. The
thing is, the SQL statement breaks into two lines when I copy the string
from the Immediates window to the SQL window. When I eliminate that break,
I can run the SQL from that window. I have checked the length of the
statement and now think that this could be the problem. The length of the
line that gets split is 1299 characters and it gets split into two lines of
1023 characters on one line and the 276 characters on the next line; it
seems that I have reached the end of the capacity of a string. What do I do
now? Can I use two variables to split the SQL Statement in two and then
concatenate them? How might the coding for that look?

Thanks

Colleyville Alan wrote:

I have an application that uses SQL. I am writing a new piece to it, so I used the same approach that worked before. I set up a string and add to it, i.e. strMySql = "some Sql Statement" and then strMySql = strMySql + "some other new statement".

The program keeps crashing saying I am running an invalid operation. I go to debug.print and copy the contents of the strMySql to the sql window.
Sure enough, where the sql statement wrapped in the Immdiates window, the statement got split to a new line in the SQL window. I take out the space at the end of the line and the next line snaps back into place to form a
coninuous statement once again. At this point, I can change back to the QBE window and the statement is set up properly and will execute. But in VBA it will not and I think that part that wraps in the Immdediate window has
nothing to do with my code.

Can anyone venture a guess as to what I am doing wrong?
Thanks

[Mike Storr]

The length of your string should only be an issue if you are trying to apply
it to a form or report recordsource as I believe these have a lenght
limitation. More help could be provided if we could see the code section in
question.

Mike Storr
www.veraccess.com

Yes, I'm putting in the spaces. There were a couple of errors where I had
not put in the spaces, but I got those fixed before I posted this. The
thing is, the SQL statement breaks into two lines when I copy the string
from the Immediates window to the SQL window. When I eliminate that break, I can run the SQL from that window. I have checked the length of the
statement and now think that this could be the problem. The length of the
line that gets split is 1299 characters and it gets split into two lines of 1023 characters on one line and the 276 characters on the next line; it
seems that I have reached the end of the capacity of a string. What do I do now? Can I use two variables to split the SQL Statement in two and then
concatenate them? How might the coding for that look?

Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your SQL statement may have a vbCrLf in it. This would cause the
break. You also must recognize that the Immediate window will break
strings, that are longer than the Immediate window width, into
separate lines. These breaks may be inside a word or phrase, thus
invalidating the SQL string.

Since you say the break is a space, that space shouldn't cause the SQL
statement to fail. Therefore, the problem is probably not in the SQL
statement, but in your VBA code. Can we see that code?

Rgds,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAwmp4echKqOuFEgEQIDAACg05FPcdwRDRwr7fHITB2YF3 RokXwAn0kL
v4CsonwtGlsAgwyrVh75hcMh
=hdEE
-----END PGP SIGNATURE-----
Colleyville Alan wrote:

"MGFoster" <[e-mail]> wrote in message
news:aT******************@newsread1.news.pas.earth link.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you putting spaces in the SQL statements in your VBA strings.
IOW:

strMySQL = "Select * From myTable"
strMySQL = strMySQL & "Where X = Y"

This will concatenate to

Select * From myTableWhere X = Y
^^

See the error where I didn't put a space after the myTable in the
first strMySQL assignment. This will cause an SQL syntax error when
the SQL command is attempted.

Therefore, put extra spaces at the end of your strings to ensure the
concatenation properly separates all elements of the SQL statement.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAo6Q4echKqOuFEgEQJ1QACg4hEgrk5utCbp/dcRlJANjyNRUxkAn0iI
ofVMmEVfaiVXyyhVbcwK4fG+
=2310
-----END PGP SIGNATURE-----
\

Yes, I'm putting in the spaces. There were a couple of errors where I had
not put in the spaces, but I got those fixed before I posted this. The
thing is, the SQL statement breaks into two lines when I copy the string
from the Immediates window to the SQL window. When I eliminate that break,
I can run the SQL from that window. I have checked the length of the
statement and now think that this could be the problem. The length of the
line that gets split is 1299 characters and it gets split into two lines of
1023 characters on one line and the 276 characters on the next line; it
seems that I have reached the end of the capacity of a string. What do I do
now? Can I use two variables to split the SQL Statement in two and then
concatenate them? How might the coding for that look?

Thanks

Colleyville Alan wrote:

I have an application that uses SQL. I am writing a new piece to it, so
I
used the same approach that worked before. I set up a string and add to
it,
i.e. strMySql = "some Sql Statement" and then strMySql = strMySql +
"some
other new statement".

The program keeps crashing saying I am running an invalid operation. I
go
to debug.print and copy the contents of the strMySql to the sql window.
Sure enough, where the sql statement wrapped in the Immdiates window,
the
statement got split to a new line in the SQL window. I take out the
space
at the end of the line and the next line snaps back into place to form a
coninuous statement once again. At this point, I can change back to the
QBE
window and the statement is set up properly and will execute. But in
VBA it
will not and I think that part that wraps in the Immdediate window has
nothing to do with my code.

Can anyone venture a guess as to what I am doing wrong?
Thanks

[Colleyville Alan]

"MGFoster" <[e-mail]> wrote in message
news:wE******************@newsread1.news.pas.earth link.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your SQL statement may have a vbCrLf in it.
It does now since that was the *answer* to my problem. I added it to the
end of each line, thus:

strMySql = strMySql & "Fund_Holdings_By_Plan.Plan_Name " & vbCrLf

This would cause the break. When it breaks this way, it does not split at the wrong point. So I can
copy all of the code from the Immediates window and it runs in the SQL
window. More importantly, it works in VBA!
You also must recognize that the Immediate window will break
strings, that are longer than the Immediate window width, into
separate lines. These breaks may be inside a word or phrase, thus
invalidating the SQL string.

Since you say the break is a space, that space shouldn't cause the SQL
statement to fail. Therefore, the problem is probably not in the SQL
statement, but in your VBA code. Can we see that code?

I have it working now, but every sql stmt was breaking in Immediates at
exactly 1023 char. When I copied the contents to the sql window, got rid of
the "blank" (basically, rejoined the line), it ran fine. Before I rejoined
the line, I got the same error msg that I had received in VBA. So while
everyone tells me that I have the problem all wrong, all of the errors in
each of 5 sql blocks were the same. And the & vbCrLf after the end of each
line fixed the problem. I'm not 100% sure what it all means, but right now
it works and I am reasonably happy.