Solved VBA Code to Copy to New Access Table before Deletion in Access Database Using Excel ListBox

[meilkew]

Hello Guys,

I have a List Box in UserForm and Command Button to Delete existing records in Access Database. The below code works for deleting multiple records selected in the list box. I would like to grab a copy of these records into a new Table "f_SD_DeletedRecords" before executing the command. Additionally, I would like to add new fields for "Date_Deleted", "Deleted_By" and "Reason" who execute and why the records are being deleted. The table where the records are being deleted have the same number of columns of f_SD_DeletedRecords + Date Deleted + Deleted By + Reason.

I hope someone could help me modify this code. If required, I can share the template, that I am currently working on.

Private Sub cmbDelete_Click()

Dim sFilePath As String


    sFilePath = Worksheets("Home").Range("P4").Value

If Selected_List = 0 Then

        MsgBox "Please select record(s) to delete.", vbOKOnly + vbInformation, "Delete"
       
        Exit Sub

    End If


Dim confirmation As Integer

confirmation = MsgBox("Do you want to delete selected record(s)?", vbQuestion + vbYesNo)

If confirmation = vbNo Then Exit Sub

Dim i As Long

    Dim cnn As New ADODB.Connection

    Dim rst As New ADODB.Recordset

    Dim qry As String


    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilePath

For i = 0 To Me.ListBoxSD.ListCount - 1

     If Me.ListBoxSD.Selected(i) = True Then

        qry = "Delete FROM f_SD WHERE ID = '" & Me.ListBoxSD.List(i, 0) & "'"
 
        rst.Open qry, cnn, adOpenKeyset, adLockOptimistic  

     End If

Next i

cnn.Close

Set rst = Nothing

Set cnn = Nothing

Call Me.List_box_Data

MsgBox "Deleted Successfully", vbInformation


End Sub

Files that I am currently working.

 

[meilkew]

Guys, anyone can jump in! hehehe...

 

[moke123]

I'm not an excel or Ado guy so cant help a lot with that.

It looks to me like you need to add an append qry to your new table as indicated below. Execute it before your delete.

For i = 0 To Me.ListBoxSD.ListCount - 1

     If Me.ListBoxSD.Selected(i) = True Then

'*** Add append query here ***

        qry = "Delete FROM f_SD WHERE ID = '" & Me.ListBoxSD.List(i, 0) & "'"
 
        rst.Open qry, cnn, adOpenKeyset, adLockOptimistic 

     End If

Next i

 

[meilkew]

Hi @moke123 this is exactly what I need, but no idea how to write that code. I hope someone could help me out. I need to get all the data which matched the selected ID's in the listbox then loop if the selected row is more than 1.

How I will do that, let's hope someone could help. I think this is too much to ask as this will require opening another form with textbox which user have to filled then add deleted by and date deleted fields before the values deleted from the main DB table.

I really hope someone from here could help me out.

 

[moke123]

Must you delete the records? Very often it's better to just mark the record as archived or deleted with out actually deleting it. When selecting records you would then exclude these records from the recordset with a where clause like "where DateDeleted is not null".

 

[moke123]

to test this add a field DateDeleted in your table (date datatype)
replace your "qry = delete . . ." line with the one below.

your update query would look somethink like
qry = "UPDATE f_SD SET f_SD.DateDeleted = #" & Date & "# WHERE f_SD.ID = '" & Me.ListBoxSD.List(i, 0) & "'"

I would also add an autonumber as a PK rather than a long string like C01-HBC-S-STA-T-AR-AL-AZ-1003_C0

 

[Minty]

I'm with Moke on this one - you are creating a separate table for excluded information, which unless you have massive amounts of records (millions) shouldn't be required

 

[moke123]

What is your workflow on this? I see all your code and forms are within excel. Again, i'm not an excel person but when I have imported from
excel I always did everything from within access by just linking to the excel file like a table.
I've always found creating objects in access to be much easier than in excel.

 

[meilkew]

Must you delete the records? Very often it's better to just mark the record as archived or deleted with out actually deleting it. When selecting records you would then exclude these records from the recordset with a where clause like "where DateDeleted is not null".

I actually thought about this, but I read something that it is much better to move it to new table instead of excluding it with filter as it needs to run every time causing for time to return the value back to excel. I have nearly 100K records in the database.

 

[meilkew]

to test this add a field DateDeleted in your table (date datatype)
replace your "qry = delete . . ." line with the one below.

your update query would look somethink like
qry = "UPDATE f_SD SET f_SD.DateDeleted = #" & Date & "# WHERE f_SD.ID = '" & Me.ListBoxSD.List(i, 0) & "'"

I would also add an autonumber as a PK rather than a long string like C01-HBC-S-STA-T-AR-AL-AZ-1003_C0

If I don't find any solution to move the records to a new table, I will try this approach. I use these string as it more convenient to have the exact reference number and revision to be my primary key.

 

[meilkew]

What is your workflow on this? I see all your code and forms are within excel. Again, i'm not an excel person but when I have imported from
excel I always did everything from within access by just linking to the excel file like a table.
I've always found creating objects in access to be much easier than in excel.

I wish I could do the same, I don't know much about access and I am not a programmer either. I found codes then modify it to best I could.

 

[arnelgp]

firstly, try learning Access. it is more easier to program than excel.
see the code behind "Delete" button.

 

[meilkew]

firstly, try learning Access. it is more easier to program than excel.
see the code behind "Delete" button.

Holy Macro.... Thank you sooooo much Sir. I will try to learn Access in the future... Kudos.. Keep Safe Sir and God bless you. Do you have a website or video tutorial Sir? would like to enroll if you have one?

 

[arnelgp]

I don't. If you want I can send you some Access pdf for your study.

 

[meilkew]

I don't. If you want I can send you some Access pdf for your study.

You should have one.. I will definitely enroll myself, if you can share any reading materials for me to begin with, much appreciated. Also, I did add the code below in the reason form to avoid user from closing the form using the X button. As it continue to delete the records if X button is selected.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use Cancel Button to Close this Form"
End If

End Sub

You are a life saver. I have been searching for almost a week now, I couldn't imagine how and where to begin with the code (As I really don't know how to write one to begin with) hehehe... I search code in the web adjust it to the best possible I could then put it together. This kind of forum helped me a lot.

 

[arnelgp]

pm me and mention your email addr, i will email the pdf to you.