vba 如何使用ID检查记录，如果记录存在则更新如果不添加新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21706069/
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
How to check for record by using ID, then if record exists update if not add new record
提问by Crabara
I've create an excel userform to collect data. I have connected it to Access to dump the data. However I want to update Access every time a user presses the submit button.
我创建了一个 excel 用户表单来收集数据。我已将其连接到 Access 以转储数据。但是，每次用户按下提交按钮时，我都想更新 Access。
Basically I need the Select statement to determine an id existence, then if it doesn't exists I need to use the INSERT to add the new row. I'm very new with any SQL so any help would be great.
基本上我需要 Select 语句来确定 id 存在，然后如果它不存在，我需要使用 INSERT 添加新行。我对任何 SQL 都很陌生，所以任何帮助都会很棒。
Here is the code I have now, I need to adapt it to ADO.
这是我现在拥有的代码，我需要将其调整为 ADO。
Sub Update()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim StrSql As String
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="Foam", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
StrSql = "SELECT * FROM Foam WHERE FoamID = " & txtMyID
Set rst = CurrentDb.OpenRecordset(StrSql, dbOpenDynaset)
If (rst.RecordCount = 0) Then
DoCmd.RunSQL "INSERT INTO Foam (ID, Part, Job, Emp, Weight, Oven) VALUES " & _
"(" & txtID & ", '" & txtField1 & "', '" & txtField2 & "', '" & txtField3 & "', '" & txtField4 & "', '" & txtField5 & "' );"
End If
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End With
End Sub
采纳答案by HansUp
I revised your code sample just enough to get it working on my system and tested this version in Excel 2007.
我修改了您的代码示例以使其在我的系统上运行，并在 Excel 2007 中测试了此版本。
When I use a value for lngIdwhich matches the idof an existing record, that record is opened in the recordset and I can update the values of its fields.
当我使用lngId与id现有记录匹配的值时，该记录将在记录集中打开，我可以更新其字段的值。
When lngIddoes not match the idof an existing record, the recordset opens empty [(.BOF And .EOF) = True]. In that situation, I add a new record and add the field values to it.
当lngId与id现有记录的不匹配时，记录集打开为空 [ (.BOF And .EOF) = True]。在这种情况下，我添加了一条新记录并向其中添加了字段值。
Sub Update()
Const TARGET_DB As String = "database1.mdb"
Dim cnn As ADODB.Connection
Dim MyConn As String
Dim rst As ADODB.Recordset
Dim StrSql As String
Dim lngId As Long
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
lngId = 4
StrSql = "SELECT * FROM tblFoo WHERE id = " & lngId
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseServer
.Open Source:=StrSql, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
If (.BOF And .EOF) Then
' no match found; add new record
.AddNew
!ID = lngId
!some_text = "Hello World"
Else
' matching record found; update it
!some_text = "Hello World"
End If
.Update
.Close
End With
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub
回答by Linger
Using VBA, here is a sample of how to query for a certain IDto check if it exists. If it does not then add it via an INSERTstatement:
使用 VBA，这里是如何查询某个对象ID以检查它是否存在的示例。如果没有，则通过INSERT语句添加它：
Dim rs As DAO.Recordset
Dim StrSql As String
StrSql = "SELECT * FROM MyTable WHERE ID = " & txtMyID
Set rs = CurrentDb.OpenRecordset(StrSql, dbOpenDynaset)
If (rs.RecordCount = 0) Then
DoCmd.RunSQL "INSERT INTO MyTable (ID, Field1, Field2) VALUES " & _
"(" & txtID & ", '" & txtField1 & "', '" & txtField2 & "');"
End If
