针对 ODBC 连接表 VBA 运行查询
我有一个已通过 Access 中的 ODBC 连接的表（读数），当我单击它时，它会很快打开。
但是，当我尝试在 VBA 中运行它时，它会锁定并且从不显示任何内容：
Dim strSql As String
strSql = "SELECT readings.ids " & _
"INTO ids_temp " & _
"FROM readings " & _
"WHERE readings.ids > 1234;" //This is id in middle of list
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
由于某种原因，这会导致整个系统崩溃。 有任何想法吗？
I have a table (readings) already connected by ODBC in Access that opens very quickly when I click on it.
However, when I try to run this in VBA I it locks up and never displays anything:
Dim strSql As String
strSql = "SELECT readings.ids " & _
"INTO ids_temp " & _
"FROM readings " & _
"WHERE readings.ids > 1234;" //This is id in middle of list
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
For some reason this crashes the whole system. Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
通常不是使用 DoCmd，而是由现有连接处理以创建 Command 对象，该对象接受与 Command.Execute 方法一起使用的 SQL 语句。
阅读 DoCmd 的文档，它似乎主要用于从 Access UI 菜单执行宏。
Rather than using DoCmd, t's usually handled by your existing connection to create a Command object, which accepts SQL statements to use with the Command.Execute method.
Reading the documentation for DoCmd, it appears to primarily be intended for eexecuting Macros from the Access UI menus.
您的数据库本地有 ids_temp 表吗？ 如果 ids_temp 表是链接表，它将删除该表，因为 select into CREATES NEW TABLE。 如果你想添加到表中，请尝试 INSERT INTO 命令。 您可以在插入数据之前清理表。
Does you Database have ids_temp table locally? If ids_temp table is Linked table it will delete the table, because select into CREATES NEW TABLE. If you want to add to table try INSERT INTO command. You can clean table before inserting the data.
所以这个错误实际上是我的错，我使用的 id 导致查询返回大约 600 万个结果。 但是，这种方法实际上效果很好，我只需创建表格并将不同表单上的列表框链接到该表格，然后只显示该表单。 我在中间做了一些关闭和更新，但总的来说，它似乎运作良好。 谢谢您的帮助
So the error was actually my fault the id I was using was causing the Query to return about 6 million results. But, this method actually works great, I just create the table and link a list box on a different form to the table, then I just show the form. I do some closes and updates in between but overall it seems to work well. Thanks for the help
让我说，DoCmd.RunSQL 永远都是不可取的，特别是在 SetWarnings 关闭的情况下，因为您不知道结果是否是您所期望的。 您已经告诉 VBA 不要报告错误，因此您永远不知道是否所有记录都已插入。
用我的 SQLRun() 函数替换 DoCmd.RunSQL 非常容易，发布在这里：
Let me say that DoCmd.RunSQL is never advisable, particularly with SetWarnings turned OFF, as you have no idea whether the result is what you expect or not. You've told VBA not to report errors, so you never know if all the records were inserted or not.
It's very easy to replace DoCmd.RunSQL with my SQLRun() function, posted here: