Solved VBA Code to Copy to New Access Table before Deletion in Access Database Using Excel ListBox (1 Viewer)

meilkew

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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.

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

Attachments

  • Help.zip
    398 KB · Views: 576
Last edited:

moke123

AWF VIP
Local time
Today, 14:46
Joined
Jan 11, 2013
Messages
3,852
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.

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

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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

AWF VIP
Local time
Today, 14:46
Joined
Jan 11, 2013
Messages
3,852
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

AWF VIP
Local time
Today, 14:46
Joined
Jan 11, 2013
Messages
3,852
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

AWF VIP
Local time
Today, 18:46
Joined
Jul 26, 2013
Messages
10,355
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

AWF VIP
Local time
Today, 14:46
Joined
Jan 11, 2013
Messages
3,852
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

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:46
Joined
May 7, 2009
Messages
19,169
firstly, try learning Access. it is more easier to program than excel.
see the code behind "Delete" button.
 

Attachments

  • Help.zip
    343.6 KB · Views: 584

meilkew

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
firstly, try learning Access. it is more easier to program than excel.
see the code behind "Delete" button.

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:46
Joined
May 7, 2009
Messages
19,169
I don't. If you want I can send you some Access pdf for your study.
 

meilkew

New member
Local time
Today, 21:46
Joined
Apr 14, 2020
Messages
27
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.

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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:46
Joined
May 7, 2009
Messages
19,169
pm me and mention your email addr, i will email the pdf to you.
 

Users who are viewing this thread

Top Bottom