waarde uit excel in access wegschrijven telkens overschrijven

[Peer44]

Hallo,

weet iemand hoe ik onderstaande code kan aanpassen:
ik wil de waarde in access telkens overschrijven en niet als nieuw record wegschrijven?

Option Explicit

Sub verwijderenRecords()
'Author       : Ken Puls (www.excelguru.ca)
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required

    Dim cnt As New ADODB.Connection, _
            rst As New ADODB.Recordset, _
            dbPath As String, _
            tblName As String, _
            rngColHeads As Range, _
            rngTblRcds As Range, _
            colHead As String, _
            rcdDetail As String, _
            ch As Integer, _
            cl As Integer, _
            notNull As Boolean

    Blad12.Activate
    'Set the string to the path of your database as defined on the worksheet
    dbPath = "Y:\labels\labels gekoppeld aan database voor etiketeermachine\Databestanden\Database bestand Access\Backend\test db\productieplanning DB.accdb"
    tblName = "DeleteDate"
    Set rngColHeads = Sheets("Invulblad").Range("ProductieDatum")
    Set rngTblRcds = Sheets("Invulblad").Range("DeleteDate")

    'Concatenate a string with the names of the column headings
    colHead = " ("
    For ch = 1 To rngColHeads.Count
        colHead = colHead & rngColHeads.Columns(ch).Value
        Select Case ch
            Case Is = rngColHeads.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next ch

    'Open connection to the database
    cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & dbPath & ";"

    'Begin transaction processing
    On Error GoTo EndUpdate
    cnt.BeginTrans

    'Insert records into database from worksheet table
    For cl = 1 To rngTblRcds.Rows.Count

        'Assume record is completely Null, and open record string for concatenation
        notNull = False
        rcdDetail = "('"

        'Evaluate field in the record
        For ch = 1 To rngColHeads.Count
            Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                    End Select

                    'if not empty, set notNull to true, and append value to string
                Case Else
                    notNull = True
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                    End Select
            End Select
        Next ch

        'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case notNull
            Case Is = True
                rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
            Case Is = False
                'do not insert record
        End Select
    Next cl

EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        cnt.RollbackTrans
        MsgBox "Er is iets misgegaan  Database niet bijgewerkt! controleer of er nergens geen ### staan, dit eerst oplossen", vbCritical, "Error!"
    Else
        On Error Resume Next
        cnt.CommitTrans
    End If

    'Close the ADO objects
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
    
       
  
Blad11.Activate

End Sub

 

[Alcocraro]

Techniques Products Just what Options Are Available to Individuals

However, you do have the option of permanent and total back hair removal for males. .

Moreover to hereditary concerns, 1 a lot more frequent explanation for female hair loss is due to hormone imbalances which could be skilled on account of being pregnant, menopause, specific birth manage pills, and when ovarian cysts exist. . Peptides are break-down goods of proteins. read this hair removal cream . Be certain to read the accompanying directions of arm waxing kits that use a non-cloth connected removal method. .

The ****h is, in a lot of an old movie you can see the characters utilizing a classic wet shave, with either a double edged safety razor (a single bladed razor with two sides plus a cover) or straight razor. .