VBA Export from excel to access to add new record or update existing record

NicoV.

New member
Joined
Dec 2, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
I have a excel sheet I want to use to upload the data of the several cells into a access database.
The code is working fine.
But When the record in access already exist, I want the code to update the data, but it seems I'm not able to achieve this

If someone could help me with this, I would be very greatfull

Attached a zip file with the access-file and the Excel-file


Nico

This is the original working code:
Code:
[/COLOR]Sub ADOFromExcelToAccess1()

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:..\TestDatabase.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs
    
    .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Name") = Range("E8").Value
        .Fields("FirstName") = Range("E9").Value
        .Fields("InputDate") = Range("B1").Value
        .Fields("MeasuredValue") = Range("C15").Value
        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing
End Sub[COLOR=#333333]

and this is what I tried already If I want to update a existing record :
Code:
Sub ADOFromExcelToAccess2()

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long



' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:..\TestDatabase.accdb;"

Dim sql As String, pk1 As Variant, pk As Variant

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs
    
            pk1 = Range("B1").Value
        

        strSQL = "SELECT * " & _
                    "FROM table1 " & _
                    "WHERE [table1].[InputDate] = # " & pk1 & " ';"

        .Open Source:=strSQL, _
             ActiveConnection:=cn, _
             CursorType:=adOpenDynamic, _
             LockType:=adLockOptimistic, _
             Options:=adCmdText

        'if EOF add new record otherwise overwrite old record
        If .EOF = True Then
            .AddNew 'Create a new record
        End If
    
    
    
        '.AddNew ' create a new record
        ' add values to each field in the record
        .Fields("Name") = Range("E8").Value
        .Fields("FirstName") = Range("E9").Value
        .Fields("InputDate") = Range("B1").Value
        .Fields("MeasuredValue") = Range("C15").Value
        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing
End Sub
 

Attachments

  • TestDatabase.zip
    34.2 KB · Views: 10
Why not just link your Excel Table to Access and it will automatically update any changes in real time.
 
Back
Top