使用 Access 的 VBA 追加查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27987736/
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
VBA Append Query Using Access
提问by SarahSedaii
I am attempting to add a button to a form to add the contents of the form as a new line in my table. I have written this line of code before, but this time it isn't working.
我正在尝试向表单添加一个按钮，以将表单的内容添加为表格中的新行。我以前写过这行代码，但这次不起作用。
My form contains text and integers. 3 boxes on the form are auto-populated when the form loads. (Will the auto populated boxes change anything?)
我的表单包含文本和整数。当表单加载时，表单上的 3 个框会自动填充。（自动填充的框会改变什么吗？）
Instead of trying to make the whole string work, I have reduced my code to just add one box (one of the auto-populated boxes). I get a Rune-time error '438': Object doesn't support this property or method.
我没有尝试使整个字符串工作，而是将代码简化为仅添加一个框（自动填充框之一）。我收到符文时错误“438”：对象不支持此属性或方法。
I've tried googling the error, but I cannot find anything that applies.
我试过在谷歌上搜索错误，但找不到任何适用的内容。
The code is as follows:
代码如下：
Dim strInsert As String
strInsert = "INSERT INTO NLog(IDKEY) " & _
" Values(" & Me.TxtIdKey & ")"
Debug.Print strInsert
CurrentProject.Execute strInsert, dbFailOnError
MsgBox ("Entry Added")
When I look at the debug screen it shows the following: INSERT INTO NLog(IDKEY) Values(OH08801405)
当我查看调试屏幕时，它显示以下内容：INSERT INTO NLog(IDKEY) Values(OH08801405)
I am so frustrated! I am still very new to this, and I feel totally out of my element.
我很沮丧！我对此仍然很陌生，我觉得完全不符合我的要求。
采纳答案by HansUp
You could execute your INSERTstatement with ADO like this ...
你可以INSERT像这样用 ADO执行你的语句......
CurrentProject.Connection.Execute strInsert
Note CurrentProject.Connection.Execute, not CurrentProject.Execute, and don't include the DAO constant dbFailOnErrorwhen executing from ADO.
请注意CurrentProject.Connection.Execute，从 ADO 执行时CurrentProject.Execute，不包括 DAO 常量dbFailOnError，不包括在内。
Or with DAO like this ...
或者像这样的 DAO ......
CurrentDb.Execute strInsert, dbFailOnError
Also, you were attempting to insert a text value into the IDKEYfield. Assuming text is the correct datatype for that field, add quotes around the value you're inserting ...
此外，您试图在IDKEY字段中插入文本值。假设 text 是该字段的正确数据类型，请在您插入的值周围添加引号...
strInsert = "INSERT INTO NLog(IDKEY) " & _
" Values('" & Me.TxtIdKey & "')"
Then you should see Debug.Printoutput similar to this ...
然后你应该看到Debug.Print类似于这个的输出......
INSERT INTO NLog(IDKEY) Values('OH08801405')
Without quotes surrounding OH08801405, the db engine will not understand it is supposed to be a string value it should insert, and instead will assume it is a parameter for which you haven't supplied a value.
如果没有围绕OH08801405 的引号，数据库引擎将无法理解它应该是应该插入的字符串值，而是会假设它是您尚未为其提供值的参数。
回答by Newd
I have to assume that the error message is happening at CurrentProject.Execute strInsert, dbFailOnErrorsince your debug actually shows something.
我必须假设错误消息正在发生，CurrentProject.Execute strInsert, dbFailOnError因为您的调试实际上显示了一些东西。
You will need to set up a connection to your database and use db.Execute strInsert, dbFailOnError.
您将需要建立与数据库的连接并使用db.Execute strInsert, dbFailOnError.
You declare a connection like this:
你声明一个这样的连接：
Dim db As DAO.Database
Set db = CurrentDb
So you should end up with something like:
所以你应该得到类似的结果：
Dim strInsert As String
Dim db As DAO.Database
strInsert = "INSERT INTO NLog(IDKEY) " & _
" Values(" & Me.TxtIdKey & ")"
Debug.Print strInsert
Set db = CurrentDb
db.Execute strInsert, dbFailOnError
MsgBox ("Entry Added")
