在VBA中SQL语句太长 - 我该如何解决？ [英] SQL statement too long in VBA - how can I fix it?

问题描述

我发布了我在SQL窗口中运行的SQL语句有问题，而不是在VBA中。我后来发现，当我创建

时，VBA中的字符串长度超过1023个字符。当我将这个字符串

复制到SQL窗口时，它会分成两行，一行是1023，其余的是

。当我删除那个中断时，查询运行得很好。


由于Access告诉我一个字符串可以容纳10 ^ 16（64k），它似乎没有

是一个字符串容量问题（加上下一行的

的字符串部分仍然存在）。我搜索了档案，并看到了

每行1023个字符的VBA限制和带下划线的那个你可以创建一条10行1023的逻辑行。我不需要接近那么多的b $ b，但它似乎是一个解决方案。但我不知道

如何实现它;语法对我来说不清楚。


目前，我设置了一个字符串并添加到它（即strMySql =" some Sql

Statement"）然后strMySql = strMySql +其他一些新语句）。我现在可以决定使用变量来捕获字符串的第一部分和

确保它小于1023然后我可以分配的其余字符串

到第二个变量。但我不知道该怎么做。

下划线字符和新行可能就是答案，但如何实现
？目前，当我将sql语句内置到一个

变量中时，我使用以下内容：


设置rstMyRecs = CurrentDb.OpenRecordset（strMySql）


当sql语句较短时，这很好用，我得到了我的

记录集并从那里继续。我怎么能用逻辑线呢？在这里？


谢谢

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

推荐答案

strMySql =" some sql文本在这里 &安培; _

"以及更多sql文本到这里 &安培; _

""


strMySql = strMySql& 你可以继续添加 &安培; _

"以这种方式对字符串也是 &安培; _

当你把它全部建成的时候 &安培; _

你想要它，然后只需使用："


设置rstMyRecs = CurrentDb.OpenRecordset（strMySql）


HTH

兰迪


" Colleyville Alan" < AE *********** @ nospam.comcast.net>在消息中写道

news：fbzOb.95033

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

na.49992@attbi_s04...

我发布了我在SQL窗口中运行
的SQL语句遇到了问题，但在VBA中却没有。我发现当我在
中创建VBA中的字符串时，它的长度超过1023个字符。当我将这个
字符串复制到SQL窗口时，它会分成两行，一行是1023，另一行是
。当我删除那个中断时，查询运行得很好。

由于Access告诉我一个字符串可以容纳10 ^ 16（64k），因此字符串容量问题似乎不是
（加上到下一行的字符串部分仍然存在）。我搜索了档案并看到了每行1023个字符的VBA限制的引用，并且使用下划线你可以创建一条10行，每行1023行的逻辑行。我不需要接近那么多的b $ b，但这似乎是一个解决方案。但我没有b $ b的想法如何实现它;我的语法不清楚。

目前，我设置了一个字符串并添加到它（即strMySql =" some Sql
Statement"然后strMySql = strMySql +" some other新声明）。我现在可以决定使用变量来捕获字符串
的第一部分，并确保它小于1023然后字符串的其余部分我可以
分配给第二个变量。但我不知道该怎么做。
下划线字符和新行可能是答案，但如何实现？目前，当我将sql语句内置到一个
变量中时，我会使用以下内容：

设置rstMyRecs = CurrentDb.OpenRecordset（strMySql）

sql语句更短，这个工作得很好，我得到了我的
记录集并从那里继续。我怎么能用逻辑线呢？在这里？

感谢
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和QUOT; < RA *** @ SpamFree.com>在留言中写道

news：8c ********************** @ newssvr28.news.prodi gy.com ...

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

strMySql =" some sql text goes here &安培; _
和更多的sql文本在这里 &安培; _
"等等

strMySql = strMySql& 你可以继续添加 &安培; _________ /以这种方式对字符串也是 &安培; _
当你把它全部建成的时候 &安培; _
你想要它，然后简单地使用：

设置rstMyRecs = CurrentDb.OpenRecordset（strMySql）


我试过了，但它似乎只是做了它以前做过的事情，把太多的

字符放入字符串然后当它们结束时Access分裂它们

1023.我不能放一条线因为

超过10行并且限制为10，所以我将字符串构建为

之前，但是一旦我得到大约900个字符或者所以，我在最后几行使用了延续字符

。当我在调试模式下通过代码时，

延续行形成一个连续的块。但它们连接到

之前的字符串，结果是一个包含大约1200个字符的字符串变量

然后被Access无法识别。再次，我从Immediate复制到

SQL窗口，代码在1023个字符处分成两段。


这个字符串构建发生在一个函数中

函数的结果是sql stmt（如果重要的话）。


" Colleyville Alan" < AE *********** @ nospam.comcast.net>在消息中写道
新闻：fbzOb.95033

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

这篇关于在VBA中SQL语句太长 - 我该如何解决？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！