vba vba中的查询字符串长度限制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2545951/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query string length limit in vba?
提问by datatoo
I am trying to combine multiple audit tables, and then filter the results into an excel sheet. The Union All and parameters make the query in excess of 1200 characters. It appears the string is truncated when running this. What recommendations can anyone make. I have no control over the database structure and am only reading foxpro free tables.
我正在尝试组合多个审计表，然后将结果过滤到一个 excel 表中。Union All 和参数使查询超过 1200 个字符。运行它时，字符串似乎被截断了。任何人都可以提出什么建议。我无法控制数据库结构，只能读取 foxpro 免费表。
I am permitted table creation but can write into the excel sheet connecting to the datasource
我被允许创建表，但可以写入连接到数据源的 Excel 表
An update to this is that I was able to extend the query string beyond 1800 characters and get data back. So I conclude I had syntax errors and the apparent truncation I mentioned was a failure in my string development in the scripting.
对此的更新是我能够将查询字符串扩展到 1800 个字符以上并取回数据。所以我得出结论我有语法错误，我提到的明显截断是我在脚本中的字符串开发失败。
I posted an example of my connection code and that answer has disappeared, so I am not sure how to designate this a closed issue. There is NOT an apparent string length limit and that was my initial concern. Thanks for the contributions.
我发布了我的连接代码示例并且该答案消失了，所以我不确定如何将其指定为已关闭的问题。没有明显的字符串长度限制，这是我最初的担忧。感谢您的贡献。
回答by Loopo
If it's a Union Query, as your question suggests, then you could break it up into each sub-query and run them one after the other.
如果它是一个联合查询，正如您的问题所暗示的那样，那么您可以将其分解为每个子查询并一个接一个地运行它们。
I can hardly believe that the limit on your query string would be so small though. Where is your query being truncated? By VBA? by FoxPro? Can you copy/paste your generated query directly into a database client and see if it runs correctly?
我简直不敢相信您的查询字符串的限制会如此之小。您的查询在哪里被截断？通过 VBA？通过 FoxPro？您能否将生成的查询直接复制/粘贴到数据库客户端中并查看它是否正常运行？
回答by datatoo
After rewriting the query a number of ways, I have gotten the string length for the query beyond 1800 characters successfully. I have to conclude that my previous error was syntax although the vba error didn't give many clues.
在通过多种方式重写查询后，我成功地获得了超过 1800 个字符的查询字符串长度。我必须得出结论，我之前的错误是语法，尽管 vba 错误没有提供很多线索。
The query string is built using controls on an excel sheet and accumulate in SQLstr. Apparent truncation was an error in my scripting the string creation. Once that was resolved then this:
查询字符串是使用 Excel 工作表上的控件构建的，并在 SQLstr 中累积。明显的截断是我编写字符串创建脚本时的错误。一旦解决了这个问题：
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=myDB;Description=myDB;DATABASE=myDB;Trusted_Connection=YES"), Destination:=Range("A2"))
.CommandText = SQLstr
.Name = "Query from myDB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Thanks for the help because the responses indicating that it seemed unlikely to be a string length limit made me look at syntax instead. I am still curious if there are query string size limits, if anyone knows.
感谢您的帮助，因为表明它似乎不太可能是字符串长度限制的响应让我改为查看语法。如果有人知道，我仍然很好奇是否有查询字符串大小限制。
回答by Eddie Kumar
Check for syntaxtical errors, I was getting similar errors, but it transpired to be syntax-error, there were some white-spaces missing where I concatenated my (2000+ chars long) query-string. Ensure that you have space before/after each concatenation-string, e.g.
检查语法错误，我遇到了类似的错误，但结果证明是语法错误，在我连接（2000+ 个字符长）查询字符串的地方缺少一些空格。确保在每个连接字符串之前/之后都有空格，例如
qryStr = "SELECT name, tel, email" & _
" FROM MyTable;"
Note the space preceding the FROM clause (within double quotes). HTH. Eddie
请注意 FROM 子句之前的空格（在双引号内）。哈。埃迪
回答by Lunatik
Can you not create a recordset with a superset of the desired data, then run a second query against that to do the final filtering?
你不能用所需数据的超集创建一个记录集，然后针对它运行第二个查询来做最后的过滤吗？
Alternatively, and I'm not in any way familiar with FoxPro or your database privileges, could you create a stored procedure in the database and then just pass parameters to it?
或者，我对 FoxPro 或您的数据库权限一点也不熟悉，您能否在数据库中创建一个存储过程，然后将参数传递给它？
Edit to add: I presume you're already giving your tables short names? (e.g. ...FROM Extremely_Long_Table_Name a WHERE...) If not, this could probably save you a pile of characters.
编辑添加：我想你已经给你的表短名称了吗？（例如...FROM Extremely_Long_Table_Name a WHERE...）如果没有，这可能会为您节省一堆字符。
