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

[Wayne Gillespie]

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

"Wayne Gillespie" <be*****@NObest fitsoftwareSPAM .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 "OpenRecord set" 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().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]

"Wayne Gillespie" <be*****@NObest fitsoftwareSPAM .com.au> wrote in message
news:sb******** *************** *********@4ax.c om...

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

"Wayne Gillespie" <be*****@NObest fitsoftwareSPAM .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 "OpenRecord set" 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().Exe cute strMySQL, dbFailOnError

or

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

Thanks