Updating a Record in Access with Excel VBA

[mp1115]

Hello All,

I'm currently using a macro that pushes data from individual cells in Excel to the appropriate column in an Access table (referred to throughout as 'Table') at the click of a button. However, the code I'm using will currently only add a new record to the database whereas I want it to be able to update a record where the data in certain fields (the primary keys of the table) already exist. Here is the code I am using (the template for which was given to me by forum user CalcSux78 in this thread -> http://www.mrexcel.com/forum/excel-questions/910496-macro-export-individual-cells-table-access.html):

Public Const Conn As String = "Data Source=Path\Database.accdb;"
Private Sub Export_Click()

    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.connection
    Dim tbl As String

    tbl = "Table"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn
    rs.Open tbl, cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    With rs
        .AddNew
        .Fields("FieldName1") = [A1]
        .Fields("FieldName2") = [C3]
        .Fields("FieldName3") = [C4]
        .Update
        .Close
    End With

    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

So let's say that FieldName1 is the primary key in Table. A record exists that already contains the value of cell A1 in the FieldName1 field within Table, however I have changed the contents of cells C3 and C4 and need the same button to reflect that update in Table (in FieldName2 and FieldName3, respectively). I imagine I can use some logic like:

"If FieldName1 Exists, then Update FieldName2 and FieldName3.
Else, Add FieldName1, FieldName2, and FieldName3."

The issue is that I am very new to VBA/macros/coding in general and have no idea how to execute this, so I am looking for any help, assitance, or pointer fingers in the right direction that someone could provide.

Thanks in advance for any and all insight!

 

[CalcSux78]

This is an example of what I've used in the past when a record could be either new or existing. The SQL statement (string) is only needed when the record ID is found and updating data. If the ID does not exist, I would need to add a new record.

How it works:

ssql = "SELECT * FROM " & tbl & " WHERE " & tbl & ".ID=" & ID

The above statement will pull all records (hopefully just 1 record) from my table of the given access database where the id# matches. Since this is a unique datafield, the string should only find one match.

Public Const Conn As String = "Data Source=Path\Database.accdb;"
Private Sub Export_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim ssql As String, tbl As String
Dim ID As Integer

tbl = "Table"
ID = IDLoan
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn

If IDLoan = "" Then     'IF NEW
    rs.Open tbl, cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.AddNew
Else                    'If Existing
    ssql = "SELECT * FROM " & tbl & " WHERE " & tbl & ".ID=" & ID
    rs.Open source:=ssql, ActiveConnection:=cn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdTableDirect
End If
With rs
    .Fields("FieldName1") = [A1]
    .Fields("FieldName2") = [C3]
    .Fields("FieldName3") = [C4]
    'repeat for all required fields
    .Update
    .Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[LockeGarmin]

Another option you have would be to use an Update Query and then an Append Query if no rows were affected.

http://www.mrexcel.com/forum/excel-questions/911978-updating-record-access-excel-visual-basic-applications.html

Option Explicit

Public Const Conn As String = "Data Source=Path\Database.accdb;"



Private Sub Export_Click()

Dim UpdatedRecordCount As Long
Dim AppendedRecordCount As Long

  Update_Or_Append_Record_In_Table [A1].Value2, [C3].Value2, [C4].Value2, UpdatedRecordCount, AppendedRecordCount

  Debug.Print "Updated Records: " & UpdatedRecordCount
  Debug.Print "Appended Records: " & AppendedRecordCount

End Sub




Private Sub Update_Or_Append_Record_In_Table( _
              FieldName1 As String, _
              FieldName2 As String, _
              FieldName3 As String _
              , Optional ByRef UpdatedRecordCount As Long _
              , Optional ByRef AppendedRecordCount As Long)

Dim CN As ADODB.Connection
Dim Sql As String
Dim RecordsAffected As Long

Const tbl As String = "Table"

    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn
    On Error GoTo CloseConnection
    
    Sql = "UPDATE [" & tbl & "] " & _
            "SET FieldName2 = '" & FieldName2 & "', FieldName3 = '" & FieldName3 & "' " & _
            "WHERE FieldName1 = '" & FieldName1 & "'"
    
    CN.Execute Sql, UpdatedRecordCount
    
    If UpdatedRecordCount = 0 Then
      Sql = "INSERT INTO [" & tbl & "] (FieldName1, FieldName2, FieldName3) " & _
              "VALUES ('" & FieldName1 & "', '" & FieldName2 & "', '" & FieldName3 & "')"
      
      CN.Execute Sql, AppendedRecordCount
    Else
      AppendedRecordCount = 0
    End If
    
CloseConnection:
    CN.Close

End Sub