VBA 将新记录添加到 Access 中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25549917/
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 add new record to table in Access
提问by Xavier
I have a (linked) table [Traffic] with one field named [Log]
I have a variable IO which can be "I" or "O".
我有一个（链接）表 [Traffic]，其中一个字段名为 [Log]
我有一个变量 IO，可以是“I”或“O”。
The only purpose of this function is to add a new record/row to table [Traffic] in column [Log] that is containing one string: a date stamp combined with an "I" or an "O" every time a form is loaded/unloaded.
此函数的唯一目的是向 [Log] 列中的表 [Traffic] 添加一个新记录/行，该列包含一个字符串：每次加载表单时与“I”或“O”组合的日期戳/卸载。
I try to create a Function in Ms Access 2010 without success (Error: "Object Required").
我尝试在 Ms Access 2010 中创建一个函数但没有成功（错误：“需要对象”）。
Any help is highly appreciated.
任何帮助都受到高度赞赏。
Public Function AppendTxt(IO As String)
Dim sText As String
Dim sTableName As String
Dim col As Integer
Dim lLastRow As Long
Dim iHeader As Integer
sTableName = "Traffic"
sText = Format$(Now, "yyyy\-mm\-dd hhnn") & IO
col = 0
With ActiveSheet.ListObjects(sTableName)
'find the last row of the list
lLastRow = ActiveSheet.ListObjects(sTableName).ListRows.Count
'shift from an extra row if list has header
If .Sort.Header = xlYes Then
iHeader = 1
Else
iHeader = 0
End If
End With
'add the data a row after the end of the list
ActiveSheet.Cells(lLastRow + 1 + iHeader, col).Value = sText
End Function
回答by Jens
Public Function Appendtxt(IO As String)
Dim sql As String
sql = "INSERT INTO tbl ( [timestamp], var ) " & _
"SELECT #" & Time() & "#, """ & IO & """ AS Expr2"
DoCmd.RunSQL sql
End Function
Assuming you were making a mistake when posting a piece of excel code here, this should do the trick.
假设您在此处发布一段 excel 代码时犯了一个错误，这应该可以解决问题。
EDIT:to get rid of any warning message, call the following function at the startup of the database.
编辑：要消除任何警告消息，请在数据库启动时调用以下函数。
Function fncSetOptions()
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Record Changes", False
DoCmd.SetWarnings False
End Function
