vba Access 中的简单 SQL 查询因缺少分号错误而失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1523922/
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
Simple SQL query in Access fails with a missing semicolon error
提问by James
So, I'm learning Access 2007, Visual Basic, and SQL at the same time. Not ideal.
所以，我正在同时学习 Access 2007、Visual Basic 和 SQL。不理想。
I have this code attached to a button in a standard wizard-generated interface. I'm trying to copy a line from tblA to tblB. Every time the code is executed I get the message, "Run-time error '3137' Missing semicolon (;) at end of SQL statement."
我将此代码附加到标准向导生成的界面中的按钮上。我正在尝试将一行从 tblA 复制到 tblB。每次执行代码时，我都会收到消息“运行时错误‘3137’在 SQL 语句末尾缺少分号 (;)。”
I'm guessing that it's expecting the SQL statement to terminate earlier, before the WHERE? But without the WHERE, how would I attach the add to a particular line ID?
我猜它希望 SQL 语句在 WHERE? 之前提前终止？但是如果没有 WHERE，我如何将添加附加到特定的行 ID？
Private Sub buttonAdd_Click()
Dim strSQL As String
strSQL = "INSERT INTO [tblB]" & _
"VALUES (ID, [Name], [Some value], [Some other value])" & _
"SELECT * FROM tblA" & _
"WHERE ID = '" & Me.ID & "' " & _
";"
DoCmd.RunSQL strSQL
End Sub
回答by mjv
Syntax is wrong, you need to remove the "VALUES" keyword.
This is assuming that ID, [Name], [Some value] and [Some other value] are column names of tblB (some hesitation on my part with the last two names having "value").
The VALUES() SQL syntax is used to provide immediate values, but since you're getting the values from tblA, the query should look like:
语法错误，需要删除“VALUES”关键字。
这是假设 ID、[Name]、[Some value] 和 [Some other value] 是 tblB 的列名（我有些犹豫，最后两个名称具有“值”）。
VALUES() SQL 语法用于提供即时值，但由于您是从 tblA 获取值，因此查询应如下所示：
strSQL = "INSERT INTO [tblB] " & _
"(ID, [Name], [Some value], [Some other value]) " & _
"SELECT * FROM tblA " & _
"WHERE ID = '" & Me.ID & "' " & _
";"
Edit: I also added spaces between tokens. Good catch Nick D, thank for noticing this !
编辑：我还在令牌之间添加了空格。好赶上尼克 D，感谢您注意到这一点！
回答by Guffa
You have put the field names in the values clause instead of after the table name, and there is a space missing between tbla and where. Both the value clause followed by select and the missing space could cause the error message by themselves.
您已将字段名称放在 values 子句中而不是放在表名称之后，并且在 tbla 和 where 之间缺少一个空格。后跟 select 的 value 子句和缺少的空间都可能导致错误消息。
strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
"SELECT * FROM tblA " & _
"WHERE ID = '" & Me.ID & "'"
The semicolon at the end is not requiered nowadays. You only need it for separating queries if you run more than one query at a time.
现在不需要末尾的分号。如果您一次运行多个查询，您只需要它来分离查询。
Also, if the ID field is numerical, you should not have apostrophes around the value:
此外，如果 ID 字段是数字，则值周围不应有撇号：
strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
"SELECT * FROM tblA " & _
"WHERE ID = " & Me.ID
回答by Nick Dandoulakis
mjv is correct. You must remove the VALUESkeyword and also you should put spaces between keywords, ie:
mjv 是正确的。您必须删除VALUES关键字，并且您应该在关键字之间放置空格，即：
"SELECT * FROM tblA" & _
"WHERE ID = '" & Me.ID & "' " & _
the above will be joined as "...FROM tblAWHERE ID..."
以上将被加入为 "...FROM tblAWHERE ID..."
