How to check a record using an identifier, and then if the record exists, if you do not add a new record

Question

I created a custom excel form for collecting data. I connected it to Access to reset the data. However, I want to update Access every time the user clicks the submit button.

Basically, I need a Select statement to determine if id exists, and then if it doesn't exist, I need to use INSERT to add a new line. I am very new to any SQL, so any help would be great.

Here is the code that I have now, I need to adapt it to 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 

Answer 1

I have reviewed your sample code enough to make it work on my system and tested this version in Excel 2007.

When I use a value for lngId that matches the id existing record, that record opens in the recordset and I can update the values ​​of my fields.

If lngId does not match the id existing record, the recordset is opened empty [ (.BOF And .EOF) = True ]. In this situation, I add a new record and add field values ​​to it.

 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 

Answer 2

Using VBA, here is an example of how to request a specific ID to check if it exists. If he does not add it using the 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