SQL statement too long in VBA - how can I fix it?

[Colleyville Alan]

I posted that I was having trouble with a SQL statement that was working in
the SQL window, but not in VBA. I have since discovered that when I create
the string in VBA it is over 1023 characters long. When I copy this string
into the SQL window, it splits into two lines, one of 1023 and the remainder
in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem to
be a string capacity problem (plus the part of the string that went to the
next line was still there). I searched the archives and saw reference to
VBA limit of 1023 characters per line and that with underscores you could
create a logical line of something like 10 lines of 1023 each. I do not need
anywhere near that much, but it seemed like a solution. But I have no idea
how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new statement"). I
can now decide to use to variables to catch the first part of the string and
make sure it is less than 1023 and then the rest of the string I can assign
to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line" here?

Thanks
Alan

[Randy Harris]

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

HTH
Randy

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:fbzOb.95033$na.49992@attbi_s04...

I posted that I was having trouble with a SQL statement that was working in the SQL window, but not in VBA. I have since discovered that when I create the string in VBA it is over 1023 characters long. When I copy this string into the SQL window, it splits into two lines, one of 1023 and the remainder in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem to be a string capacity problem (plus the part of the string that went to the
next line was still there). I searched the archives and saw reference to
VBA limit of 1023 characters per line and that with underscores you could
create a logical line of something like 10 lines of 1023 each. I do not need anywhere near that much, but it seemed like a solution. But I have no idea how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new statement"). I
can now decide to use to variables to catch the first part of the string and make sure it is less than 1023 and then the rest of the string I can assign to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line" here?

Thanks
Alan

[Colleyville Alan]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:8c**********************@newssvr28.news.prodi gy.com...

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)
I tried that, but it merely seems to do what it did before, put too many
characters into the string and then Access splits them when they get over
1023. I cannot put a line continuation character after each line since
there are more than 10 lines and the limit is 10. So I built the string as
before, but once I got around 900 chars or so, I used the continuation char
for the last several lines. When I stepped thru the code in debug mode, the
continuation lines form a contiguous block. But they concatenate to the
previous string and the result is a string variable holding about 1200 chars
and then being unrecognized by Access. Once again, I copy from Immediate to
SQL window and the code splits into two segments at 1023 chars.

This string building takes place in a function with the result of the
function being the sql stmt (if that matters).

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:fbzOb.95033$na.49992@attbi_s04...

I posted that I was having trouble with a SQL statement that was working in

the SQL window, but not in VBA. I have since discovered that when I

create

the string in VBA it is over 1023 characters long. When I copy this

string

into the SQL window, it splits into two lines, one of 1023 and the

remainder

in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem to

be a string capacity problem (plus the part of the string that went to

the next line was still there). I searched the archives and saw reference to VBA limit of 1023 characters per line and that with underscores you could create a logical line of something like 10 lines of 1023 each. I do not

need

anywhere near that much, but it seemed like a solution. But I have no

idea

how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new statement"). I can now decide to use to variables to catch the first part of the string

and

make sure it is less than 1023 and then the rest of the string I can

assign

to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line" here?

Thanks
Alan

[Robert]

Alan,

The solution to your problem is probably a combination of both techniques:
Line continuation characters, and String Concatenation.

Here is an example of the correct technique. Notice ampersands and line
continuation characters at the end of each line, and then when the thing
gets too long, we start over again, and add more to the string. This
example was written in the example Northwind database that comes with
Access. It is UNTESTED.


---Begin Code---

Dim strSQL as String

'Begin building the string. Notice that the end of the next two lines both
have spaces before the closing quote.
strSQL = "SELECT CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, " & _
"Country, Phone, Fax, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, "

'Here we begin again. This overcomes the ten-line continuation limiatation
of VBA.
strSQL = strSQL & _
"ShipAddress, ShipCity, ShipRegion, ShipPostalCode,
ShipCountry "

'And again.
strSQL = strSQL & _
"FROM Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID " & _
"WHERE CompanyName= '" & strMyCompanyName & "' AND
ShipPostalCode= " & strMyShipPostalCode

strSQL = strSQL & _
" ORDER BY CompanyName, ContactName;"

---End Code---

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:CiBOb.94035$I06.415161@attbi_s01...

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:8c**********************@newssvr28.news.prodi gy.com...

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)
I tried that, but it merely seems to do what it did before, put too many
characters into the string and then Access splits them when they get over
1023. I cannot put a line continuation character after each line since
there are more than 10 lines and the limit is 10. So I built the string

as before, but once I got around 900 chars or so, I used the continuation char for the last several lines. When I stepped thru the code in debug mode, the continuation lines form a contiguous block. But they concatenate to the
previous string and the result is a string variable holding about 1200 chars and then being unrecognized by Access. Once again, I copy from Immediate to SQL window and the code splits into two segments at 1023 chars.

This string building takes place in a function with the result of the
function being the sql stmt (if that matters).

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:fbzOb.95033$na.49992@attbi_s04...

I posted that I was having trouble with a SQL statement that was working

in

the SQL window, but not in VBA. I have since discovered that when I create

the string in VBA it is over 1023 characters long. When I copy this

string

into the SQL window, it splits into two lines, one of 1023 and the

remainder

in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem

to

be a string capacity problem (plus the part of the string that went to the next line was still there). I searched the archives and saw reference to VBA limit of 1023 characters per line and that with underscores you could create a logical line of something like 10 lines of 1023 each. I do

not need

anywhere near that much, but it seemed like a solution. But I have no

idea

how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new

statement"). I can now decide to use to variables to catch the first part of the

string and

make sure it is less than 1023 and then the rest of the string I can

assign

to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line"

here?
Thanks
Alan

[Wayne Gillespie]

On Sun, 18 Jan 2004 19:57:14 GMT, "Colleyville Alan" <ae***********@nospam.comcast.net> wrote:

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:8c**********************@newssvr28.news.prod igy.com...

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

I tried that, but it merely seems to do what it did before, put too many
characters into the string and then Access splits them when they get over
1023. I cannot put a line continuation character after each line since
there are more than 10 lines and the limit is 10. So I built the string as
before, but once I got around 900 chars or so, I used the continuation char
for the last several lines. When I stepped thru the code in debug mode, the
continuation lines form a contiguous block. But they concatenate to the
previous string and the result is a string variable holding about 1200 chars
and then being unrecognized by Access. Once again, I copy from Immediate to
SQL window and the code splits into two segments at 1023 chars.

This string building takes place in a function with the result of the
function being the sql stmt (if that matters).

Instead of using line continuations break your string into distinct lines using vbCrLf and then concatenate the lines together.

strMySQL = "This is line one " & vbCrLf
strMySQL = strMySQL & "This is line two " & vbCrLf
strMySQL = strMySQL & "This is line three " & vbCrLf
strMySQL = strMySQL & "This is line four "
Wayne Gillespie
Gosford NSW Australia

[Colleyville Alan]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:8c**********************@newssvr28.news.prodi gy.com...

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)
I tried that, but it merely seems to do what it did before, put too many
characters into the string and then Access splits them when they get over
1023. I cannot put a line continuation character after each line since
there are more than 10 lines and the limit is 10. So I built the string as
before, but once I got around 900 chars or so, I used the continuation char
for the last several lines. When I stepped thru the code in debug mode, the
continuation lines form a contiguous block. But they concatenate to the
previous string and the result is a string variable holding about 1200 chars
and then being unrecognized by Access. Once again, I copy from Immediate to
SQL window and the code splits into two segments at 1023 chars.

This string building takes place in a function with the result of the
function being the sql stmt (if that matters).

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:fbzOb.95033$na.49992@attbi_s04...

I posted that I was having trouble with a SQL statement that was working in

the SQL window, but not in VBA. I have since discovered that when I

create

the string in VBA it is over 1023 characters long. When I copy this

string

into the SQL window, it splits into two lines, one of 1023 and the

remainder

in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem to

be a string capacity problem (plus the part of the string that went to

the next line was still there). I searched the archives and saw reference to VBA limit of 1023 characters per line and that with underscores you could create a logical line of something like 10 lines of 1023 each. I do not

need

anywhere near that much, but it seemed like a solution. But I have no

idea

how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new statement"). I can now decide to use to variables to catch the first part of the string

and

make sure it is less than 1023 and then the rest of the string I can

assign

to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line" here?

Thanks
Alan

[Colleyville Alan]

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:a3********************************@4ax.com...

Instead of using line continuations break your string into distinct lines

using vbCrLf and then concatenate the lines together.
strMySQL = "This is line one " & vbCrLf
strMySQL = strMySQL & "This is line two " & vbCrLf
strMySQL = strMySQL & "This is line three " & vbCrLf
strMySQL = strMySQL & "This is line four "

Thanks - that worked. Though now I have a 3219 runtime error which I've
discovered means I cannot use "OpenRecordset" with an action query, but that
is a new problem to solve; the too-long line problem is fixed. Thanks
again.
Alan

[Larry Linson]

The problem seems to be the copying to the SQL view of Query design, Alan.
That is, you appear to be doing the right thing in code, but Access limits
what it handles properly in the SQL window. So perhaps you could simply not
view that SQL in the SQL window.

Is there some way that you could simplify your naming convention and
database structure so that the SQL string would not be so long? You may not
be able to get it under the number that causes this problem, but perhaps
still make it a bit easier to deal with.

As far as I know, the limit on a string variable is either 32,xxx or 65,xxx
characters. I'd use your orginally-described approach of separate statements
rather than continuation lines. I believe the VBA limit is per statement,
rather than per line.

Larry Linson
Microsoft Access MVP

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:CiBOb.94035$I06.415161@attbi_s01...

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:8c**********************@newssvr28.news.prodi gy.com...

strMySql = "some sql text goes here " & _
"and more sql text goes here " & _
"and so on "

strMySql = strMySql & "you can continue to add " & _
"to the string in this manner also " & _
"when you get it all built the way " & _
"you want it, then simply use:"

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)
I tried that, but it merely seems to do what it did before, put too many
characters into the string and then Access splits them when they get over
1023. I cannot put a line continuation character after each line since
there are more than 10 lines and the limit is 10. So I built the string

as before, but once I got around 900 chars or so, I used the continuation char for the last several lines. When I stepped thru the code in debug mode, the continuation lines form a contiguous block. But they concatenate to the
previous string and the result is a string variable holding about 1200 chars and then being unrecognized by Access. Once again, I copy from Immediate to SQL window and the code splits into two segments at 1023 chars.

This string building takes place in a function with the result of the
function being the sql stmt (if that matters).

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:fbzOb.95033$na.49992@attbi_s04...

I posted that I was having trouble with a SQL statement that was working

in

the SQL window, but not in VBA. I have since discovered that when I create

the string in VBA it is over 1023 characters long. When I copy this

string

into the SQL window, it splits into two lines, one of 1023 and the

remainder

in the next. When I remove that break, the query runs just fine.

Since Access tells me that a string can hold 10^16 (64k), it did not seem

to

be a string capacity problem (plus the part of the string that went to the next line was still there). I searched the archives and saw reference to VBA limit of 1023 characters per line and that with underscores you could create a logical line of something like 10 lines of 1023 each. I do

not need

anywhere near that much, but it seemed like a solution. But I have no

idea

how to implement it; the syntax is unclear to me.

Currently, I set up a string and add to it (i.e. strMySql = "some Sql
Statement" and then strMySql = strMySql +"some other new

statement"). I can now decide to use to variables to catch the first part of the

string and

make sure it is less than 1023 and then the rest of the string I can

assign

to a second variable. But I have no idea what to do with that. The
underscore character and a new line might be the answer, but how to
implement? Currently, when I have the sql statement built into one
variable, I then use the following:

Set rstMyRecs = CurrentDb.OpenRecordset(strMySql)

When the sql statement is shorter, this works just fine and I get my
recordset and go on from there. How could I use a "logical line"

here?
Thanks
Alan

[David W. Fenton]

ae***********@nospam.comcast.net (Colleyville Alan) wrote in
<fhEOb.83086$nt4.125731@attbi_s51>:

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in
message news:a3********************************@4ax.com...

>

Instead of using line continuations break your string into
distinct lines

using vbCrLf and then concatenate the lines together.

strMySQL = "This is line one " & vbCrLf
strMySQL = strMySQL & "This is line two " & vbCrLf
strMySQL = strMySQL & "This is line three " & vbCrLf
strMySQL = strMySQL & "This is line four "

Thanks - that worked. Though now I have a 3219 runtime error
which I've discovered means I cannot use "OpenRecordset" with an
action query, but that is a new problem to solve; the too-long
line problem is fixed.

Action queries can be executed, but you can't open a recordset.

Instead of:

set rst = db.OpenRecordset(strMySQL)

use:

db.Execute strMySQL, dbFailOnError

You don't need a recordset at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Colleyville Alan]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:8U******************@nwrddc02.gnilink.net...

The problem seems to be the copying to the SQL view of Query design, Alan.
That is, you appear to be doing the right thing in code, but Access limits
what it handles properly in the SQL window. So perhaps you could simply not view that SQL in the SQL window.

Is there some way that you could simplify your naming convention and
database structure so that the SQL string would not be so long? You may not be able to get it under the number that causes this problem, but perhaps
still make it a bit easier to deal with.

As far as I know, the limit on a string variable is either 32,xxx or 65,xxx characters. I'd use your orginally-described approach of separate statements rather than continuation lines. I believe the VBA limit is per statement,
rather than per line.

Larry Linson
Microsoft Access MVP

The solution that Wayne provided is working. It was an odd sort of thing,
because string variables are limited to 64k, but VBA lines are limited to
1023 char. Yet it appeared to me that there was difficulty in the way
Access treated the lines as assigned to a string. The original SQL view
(which I got from building these as QBE) certainly had no problem with the
length. VBA simply would not hold all that on one line. But with the
vbCrLf , it now copies into the SQL window as one group with each SQL
command on a separate line.

Now my code does not work for a different reason - OpenRecordset cannot be
used with action queries. But that's ok, I am fixing it now. A few more
hours of typing, and I'll be ready to rock-n-roll! (or at least move on to
the next phase).

[Wayne Gillespie]

On Sun, 18 Jan 2004 23:03:40 GMT, "Colleyville Alan" <ae***********@nospam.comcast.net> wrote:

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:a3********************************@4ax.com.. .

>

Instead of using line continuations break your string into distinct lines

using vbCrLf and then concatenate the lines together.

strMySQL = "This is line one " & vbCrLf
strMySQL = strMySQL & "This is line two " & vbCrLf
strMySQL = strMySQL & "This is line three " & vbCrLf
strMySQL = strMySQL & "This is line four "

Thanks - that worked. Though now I have a 3219 runtime error which I've
discovered means I cannot use "OpenRecordset" with an action query, but that
is a new problem to solve; the too-long line problem is fixed. Thanks
again.
Alan

If your SQL string equates to an action query you need to use either Execute (preferred) or RunSQL to run the SQL.

CurrentDB().Execute strMySQL, dbFailOnError

or

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL strMySQL
DoCmd.SetWarnings True
On Error GoTo 0
Wayne Gillespie
Gosford NSW Australia

[Colleyville Alan]

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:sb********************************@4ax.com...

On Sun, 18 Jan 2004 23:03:40 GMT, "Colleyville Alan" <ae***********@nospam.comcast.net> wrote:

"Wayne Gillespie" <be*****@NObestfitsoftwareSPAM.com.au> wrote in message
news:a3********************************@4ax.com.. .

>

Instead of using line continuations break your string into distinct

linesusing vbCrLf and then concatenate the lines together.

strMySQL = "This is line one " & vbCrLf
strMySQL = strMySQL & "This is line two " & vbCrLf
strMySQL = strMySQL & "This is line three " & vbCrLf
strMySQL = strMySQL & "This is line four "
Thanks - that worked. Though now I have a 3219 runtime error which I've
discovered means I cannot use "OpenRecordset" with an action query, but thatis a new problem to solve; the too-long line problem is fixed. Thanks
again.
Alan

If your SQL string equates to an action query you need to use either

Execute (preferred) or RunSQL to run the SQL.
CurrentDB().Execute strMySQL, dbFailOnError

or

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL strMySQL
DoCmd.SetWarnings True
On Error GoTo 0

Thanks