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

Peer44

New member
Joined
Oct 24, 2012
Messages
1
Reaction score
0
Points
0
Location
Someren, Netherlands
Hello,
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"?

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

    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\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
    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
    
        '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
  
Blad11.Activate

End Sub
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
This is written in the browser, so you'll need to test this out, but...

Right after this section:
Code:
    'Begin transaction processing
    On Error GoTo EndUpdate
    cnt.BeginTrans

Is where you'll want to delete your records. The syntax to do that is:
Code:
cnt.Execute sSQL

Wheree sSQL is the SQL statement. I'm assuming that you're going to want something along the lines of:
Code:
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.
 
Top