Results 1 to 2 of 2

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

  1. #1
    Neophyte NicoV.'s Avatar
    Join Date
    Dec 2020
    Posts
    1
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    
    
    Code:
    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


    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
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    342
    Articles
    0
    Excel Version
    O365
    Why not just link your Excel Table to Access and it will automatically update any changes in real time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •