Results 1 to 2 of 2

Thread: Use VBA excel to delete records in access table that match a named range in excel

  1. #1
    Neophyte Peer44's Avatar
    Join Date
    Oct 2012
    Someren, Netherlands

    Use VBA excel to delete records in access table that match a named range in excel

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

    I'm trying to delete records in access with Excel.
    I use the ADOSQL code to write data to access, but when I delete a record in Excel it's not automaticly deleted in access.

    To solve this I want to delete all the records in access which match the "DeleteDate" (named range in excel)

    after the records are deleted, I re-write them into access with the code below.
    How can I delete the records first using the "DeleteDate"?

    Option Explicit
    Sub DB_Insert_via_ADOSQL()
    'Author       : Ken Puls 
    '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
        '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\productieplanning DB.accdb"
        tblName = "productieplanning"
        Set rngColHeads = ActiveSheet.Range("tblHeadings")
        Set rngTblRcds = ActiveSheet.Range("tblRecords")
        '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
        '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
        'Check if error was encounted
        If Err.Number <> 0 Then
            'Error encountered.  Rollback transaction and inform user
            On Error Resume Next
            MsgBox "Er is iets misgegaan  Database niet bijgewerkt! controleer of er nergens geen ### staan, dit eerst oplossen", vbCritical, "Error!"
            On Error Resume Next
        End If
        'Close the ADO objects
        Set rst = Nothing
        Set cnt = Nothing
        On Error GoTo 0
            'database wordt geopend, zit autorun macro in die de dubbele waarde verwijderd.
            With GetObject("Y:\labels\labels gekoppeld aan database voor etiketeermachine\Databestanden\Database bestand Access\Backend\productieplanning DB.accdb")
             End With
    End Sub

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    This is written in the browser, so you'll need to test this out, but...

    Right after this section:
        'Begin transaction processing
        On Error GoTo EndUpdate
    Is where you'll want to delete your records. The syntax to do that is:
    cnt.Execute sSQL
    Wheree sSQL is the SQL statement. I'm assuming that you're going to want something along the lines of:
    cnt.Execute "DELETE * FROM " & tblName & " WHERE DateColumn = #" & worksheets("x").range("DeleteDate") & "#"
    Keep in mind that:
    • DateColumn should be replaced with the name of your date column
    • Worksheets("x").range("DeleteDate") needs to be updated with the name of the appropiate worksheet
    • I haven't debugged the SQL

    The way I test things is to step through my code and do a debug.print to work out the SQL, then test it in Access. I did, however, put this after the transaction processing was turned on. This way, even if there is an error in the delete or addition, the entire transaction will be rolled back so you don't lose anything.

    Hope it helps and makes sense.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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