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.OpenR ecordset(strMyS ql)

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]

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

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.OpenR ecordset(strMyS ql)

HTH
Randy

"Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
news:fbzOb.9503 3$na.49992@attb i_s04...[color=blue]
>
> I posted that I was having trouble with a SQL statement that was working[/color]
in[color=blue]
> the SQL window, but not in VBA. I have since discovered that when I[/color]
create[color=blue]
> the string in VBA it is over 1023 characters long. When I copy this[/color]
string[color=blue]
> into the SQL window, it splits into two lines, one of 1023 and the[/color]
remainder[color=blue]
> 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[/color]
to[color=blue]
> 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[/color]
need[color=blue]
> anywhere near that much, but it seemed like a solution. But I have no[/color]
idea[color=blue]
> 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[/color]
and[color=blue]
> make sure it is less than 1023 and then the rest of the string I can[/color]
assign[color=blue]
> 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.OpenR ecordset(strMyS ql)
>
> 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
>
>[/color]

[Colleyville Alan]

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


"Randy Harris" <randy@SpamFree .com> wrote in message
news:8cAOb.3166 2$P%1.25291490@ newssvr28.news. prodigy.com...[color=blue]
> 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.OpenR ecordset(strMyS ql)[/color]

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



[color=blue]
> "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
> news:fbzOb.9503 3$na.49992@attb i_s04...[color=green]
> >
> > I posted that I was having trouble with a SQL statement that was working[/color]
> in[color=green]
> > the SQL window, but not in VBA. I have since discovered that when I[/color]
> create[color=green]
> > the string in VBA it is over 1023 characters long. When I copy this[/color]
> string[color=green]
> > into the SQL window, it splits into two lines, one of 1023 and the[/color]
> remainder[color=green]
> > 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[/color][/color]
seem[color=blue]
> to[color=green]
> > be a string capacity problem (plus the part of the string that went to[/color][/color]
the[color=blue][color=green]
> > next line was still there). I searched the archives and saw reference[/color][/color]
to[color=blue][color=green]
> > VBA limit of 1023 characters per line and that with underscores you[/color][/color]
could[color=blue][color=green]
> > create a logical line of something like 10 lines of 1023 each. I do not[/color]
> need[color=green]
> > anywhere near that much, but it seemed like a solution. But I have no[/color]
> idea[color=green]
> > 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").[/color][/color]
I[color=blue][color=green]
> > can now decide to use to variables to catch the first part of the string[/color]
> and[color=green]
> > make sure it is less than 1023 and then the rest of the string I can[/color]
> assign[color=green]
> > 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.OpenR ecordset(strMyS ql)
> >
> > 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
> >
> >[/color]
>
>[/color]

[Robert]

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

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 & _
"ShipAddres s, ShipCity, ShipRegion, ShipPostalCode,
ShipCountry "

'And again.
strSQL = strSQL & _
"FROM Customers INNER JOIN Orders ON Customers.Custo merID =
Orders.Customer ID " & _
"WHERE CompanyName= '" & strMyCompanyNam e & "' AND
ShipPostalCode= " & strMyShipPostal Code

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

---End Code---





"Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
news:CiBOb.9403 5$I06.415161@at tbi_s01...[color=blue]
>
> "Randy Harris" <randy@SpamFree .com> wrote in message
> news:8cAOb.3166 2$P%1.25291490@ newssvr28.news. prodigy.com...[color=green]
> > 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.OpenR ecordset(strMyS ql)[/color]
>
> 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[/color]
as[color=blue]
> before, but once I got around 900 chars or so, I used the continuation[/color]
char[color=blue]
> for the last several lines. When I stepped thru the code in debug mode,[/color]
the[color=blue]
> continuation lines form a contiguous block. But they concatenate to the
> previous string and the result is a string variable holding about 1200[/color]
chars[color=blue]
> and then being unrecognized by Access. Once again, I copy from Immediate[/color]
to[color=blue]
> 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).
>
>
>
>[color=green]
> > "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
> > news:fbzOb.9503 3$na.49992@attb i_s04...[color=darkred]
> > >
> > > I posted that I was having trouble with a SQL statement that was[/color][/color][/color]
working[color=blue][color=green]
> > in[color=darkred]
> > > the SQL window, but not in VBA. I have since discovered that when I[/color]
> > create[color=darkred]
> > > the string in VBA it is over 1023 characters long. When I copy this[/color]
> > string[color=darkred]
> > > into the SQL window, it splits into two lines, one of 1023 and the[/color]
> > remainder[color=darkred]
> > > 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[/color][/color]
> seem[color=green]
> > to[color=darkred]
> > > be a string capacity problem (plus the part of the string that went to[/color][/color]
> the[color=green][color=darkred]
> > > next line was still there). I searched the archives and saw reference[/color][/color]
> to[color=green][color=darkred]
> > > VBA limit of 1023 characters per line and that with underscores you[/color][/color]
> could[color=green][color=darkred]
> > > create a logical line of something like 10 lines of 1023 each. I do[/color][/color][/color]
not[color=blue][color=green]
> > need[color=darkred]
> > > anywhere near that much, but it seemed like a solution. But I have no[/color]
> > idea[color=darkred]
> > > 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[/color][/color][/color]
statement").[color=blue]
> I[color=green][color=darkred]
> > > can now decide to use to variables to catch the first part of the[/color][/color][/color]
string[color=blue][color=green]
> > and[color=darkred]
> > > make sure it is less than 1023 and then the rest of the string I can[/color]
> > assign[color=darkred]
> > > 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.OpenR ecordset(strMyS ql)
> > >
> > > 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"[/color][/color][/color]
here?[color=blue][color=green][color=darkred]
> > >
> > > Thanks
> > > Alan
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Wayne Gillespie]

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

On Sun, 18 Jan 2004 19:57:14 GMT, "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote:
[color=blue]
>
>"Randy Harris" <randy@SpamFree .com> wrote in message
>news:8cAOb.316 62$P%1.25291490 @newssvr28.news .prodigy.com...[color=green]
>> 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.OpenR ecordset(strMyS ql)[/color]
>
>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).
>[/color]

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]

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


"Randy Harris" <randy@SpamFree .com> wrote in message
news:8cAOb.3166 2$P%1.25291490@ newssvr28.news. prodigy.com...[color=blue]
> 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.OpenR ecordset(strMyS ql)[/color]

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



[color=blue]
> "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
> news:fbzOb.9503 3$na.49992@attb i_s04...[color=green]
> >
> > I posted that I was having trouble with a SQL statement that was working[/color]
> in[color=green]
> > the SQL window, but not in VBA. I have since discovered that when I[/color]
> create[color=green]
> > the string in VBA it is over 1023 characters long. When I copy this[/color]
> string[color=green]
> > into the SQL window, it splits into two lines, one of 1023 and the[/color]
> remainder[color=green]
> > 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[/color][/color]
seem[color=blue]
> to[color=green]
> > be a string capacity problem (plus the part of the string that went to[/color][/color]
the[color=blue][color=green]
> > next line was still there). I searched the archives and saw reference[/color][/color]
to[color=blue][color=green]
> > VBA limit of 1023 characters per line and that with underscores you[/color][/color]
could[color=blue][color=green]
> > create a logical line of something like 10 lines of 1023 each. I do not[/color]
> need[color=green]
> > anywhere near that much, but it seemed like a solution. But I have no[/color]
> idea[color=green]
> > 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").[/color][/color]
I[color=blue][color=green]
> > can now decide to use to variables to catch the first part of the string[/color]
> and[color=green]
> > make sure it is less than 1023 and then the rest of the string I can[/color]
> assign[color=green]
> > 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.OpenR ecordset(strMyS ql)
> >
> > 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
> >
> >[/color]
>
>[/color]

[Colleyville Alan]

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

"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in message
news:a31m009t1k sob3hi0ss5bqnl2 puruegvak@4ax.c om...[color=blue][color=green]
> >[/color]
>
> Instead of using line continuations break your string into distinct lines[/color]
using vbCrLf and then concatenate the lines together.[color=blue]
>
> 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 "[/color]

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

[Larry Linson]

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

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



"Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
news:CiBOb.9403 5$I06.415161@at tbi_s01...[color=blue]
>
> "Randy Harris" <randy@SpamFree .com> wrote in message
> news:8cAOb.3166 2$P%1.25291490@ newssvr28.news. prodigy.com...[color=green]
> > 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.OpenR ecordset(strMyS ql)[/color]
>
> 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[/color]
as[color=blue]
> before, but once I got around 900 chars or so, I used the continuation[/color]
char[color=blue]
> for the last several lines. When I stepped thru the code in debug mode,[/color]
the[color=blue]
> continuation lines form a contiguous block. But they concatenate to the
> previous string and the result is a string variable holding about 1200[/color]
chars[color=blue]
> and then being unrecognized by Access. Once again, I copy from Immediate[/color]
to[color=blue]
> 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).
>
>
>
>[color=green]
> > "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote in message
> > news:fbzOb.9503 3$na.49992@attb i_s04...[color=darkred]
> > >
> > > I posted that I was having trouble with a SQL statement that was[/color][/color][/color]
working[color=blue][color=green]
> > in[color=darkred]
> > > the SQL window, but not in VBA. I have since discovered that when I[/color]
> > create[color=darkred]
> > > the string in VBA it is over 1023 characters long. When I copy this[/color]
> > string[color=darkred]
> > > into the SQL window, it splits into two lines, one of 1023 and the[/color]
> > remainder[color=darkred]
> > > 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[/color][/color]
> seem[color=green]
> > to[color=darkred]
> > > be a string capacity problem (plus the part of the string that went to[/color][/color]
> the[color=green][color=darkred]
> > > next line was still there). I searched the archives and saw reference[/color][/color]
> to[color=green][color=darkred]
> > > VBA limit of 1023 characters per line and that with underscores you[/color][/color]
> could[color=green][color=darkred]
> > > create a logical line of something like 10 lines of 1023 each. I do[/color][/color][/color]
not[color=blue][color=green]
> > need[color=darkred]
> > > anywhere near that much, but it seemed like a solution. But I have no[/color]
> > idea[color=darkred]
> > > 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[/color][/color][/color]
statement").[color=blue]
> I[color=green][color=darkred]
> > > can now decide to use to variables to catch the first part of the[/color][/color][/color]
string[color=blue][color=green]
> > and[color=darkred]
> > > make sure it is less than 1023 and then the rest of the string I can[/color]
> > assign[color=darkred]
> > > 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.OpenR ecordset(strMyS ql)
> > >
> > > 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"[/color][/color][/color]
here?[color=blue][color=green][color=darkred]
> > >
> > > Thanks
> > > Alan
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[David W. Fenton]

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

aecharbonneau@n ospam.comcast.n et (Colleyville Alan) wrote in
<fhEOb.83086$nt 4.125731@attbi_ s51>:
[color=blue]
>"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in
>message news:a31m009t1k sob3hi0ss5bqnl2 puruegvak@4ax.c om...[color=green][color=darkred]
>> >[/color]
>>
>> Instead of using line continuations break your string into
>> distinct lines[/color]
>using vbCrLf and then concatenate the lines together.[color=green]
>>
>> 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 "[/color]
>
>Thanks - that worked. Though now I have a 3219 runtime error
>which I've discovered means I cannot use "OpenRecord set" with an
>action query, but that is a new problem to solve; the too-long
>line problem is fixed.[/color]

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

Instead of:

set rst = db.OpenRecordse t(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]

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

"Larry Linson" <bouncer@localh ost.not> wrote in message
news:8UEOb.1270 4$9U6.3378@nwrd dc02.gnilink.ne t...[color=blue]
> 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[/color]
not[color=blue]
> 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[/color]
not[color=blue]
> 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[/color]
65,xxx[color=blue]
> characters. I'd use your orginally-described approach of separate[/color]
statements[color=blue]
> rather than continuation lines. I believe the VBA limit is per statement,
> rather than per line.
>
> Larry Linson
> Microsoft Access MVP[/color]


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]

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

On Sun, 18 Jan 2004 23:03:40 GMT, "Colleyvill e Alan" <aecharbonneau@ nospam.comcast. net> wrote:
[color=blue]
>"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in message
>news:a31m009t1 ksob3hi0ss5bqnl 2puruegvak@4ax. com...[color=green][color=darkred]
>> >[/color]
>>
>> Instead of using line continuations break your string into distinct lines[/color]
>using vbCrLf and then concatenate the lines together.[color=green]
>>
>> 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 "[/color]
>
>Thanks - that worked. Though now I have a 3219 runtime error which I've
>discovered means I cannot use "OpenRecord set" with an action query, but that
>is a new problem to solve; the too-long line problem is fixed. Thanks
>again.
>Alan
>[/color]

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

CurrentDB().Exe cute strMySQL, dbFailOnError

or

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


Wayne Gillespie
Gosford NSW Australia

[Colleyville Alan]

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


"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in message
news:sb7m00dhqa rerkvpv9q7ee7d5 ctajpee4f@4ax.c om...[color=blue]
> On Sun, 18 Jan 2004 23:03:40 GMT, "Colleyvill e Alan"[/color]
<aecharbonneau@ nospam.comcast. net> wrote:[color=blue]
>[color=green]
> >"Wayne Gillespie" <bestfit@NObest fitsoftwareSPAM .com.au> wrote in message
> >news:a31m009t1 ksob3hi0ss5bqnl 2puruegvak@4ax. com...[color=darkred]
> >> >
> >>
> >> Instead of using line continuations break your string into distinct[/color][/color][/color]
lines[color=blue][color=green]
> >using vbCrLf and then concatenate the lines together.[color=darkred]
> >>
> >> 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 "[/color]
> >
> >Thanks - that worked. Though now I have a 3219 runtime error which I've
> >discovered means I cannot use "OpenRecord set" with an action query, but[/color][/color]
that[color=blue][color=green]
> >is a new problem to solve; the too-long line problem is fixed. Thanks
> >again.
> >Alan
> >[/color]
>
> If your SQL string equates to an action query you need to use either[/color]
Execute (preferred) or RunSQL to run the SQL.[color=blue]
>
> CurrentDB().Exe cute strMySQL, dbFailOnError
>
> or
>
> On Error Resume Next
> DoCmd.SetWarnin gs False
> DoCmd.RunSQL strMySQL
> DoCmd.SetWarnin gs True
> On Error GoTo 0
>[/color]

Thanks