Delete # (n) tables based on Boolean value in another table

[skydivetom]

Experts:

I'd like some assistance with DELETING/DROPPING TABLES based on a Boolean value = TRUE in another table.



Attached DB includes 11 tables:
00_DeleteTables -- this table includes the Boolean flag
LK_Age
LK_Education
LK_Email
LK_FirstName
LK_Gender
LK_LastName
LK_MaritalStatus
LK_Nickname
LK_NumberOfChildren
LK_Occupation

As depicted in the attached JPG, field [00_DeleteTables].[Delete] = TRUE for 4 tables: [LK_Age]; [LK_Email]; [LK_Nickname]; [LK_NumberOfChildren]

What I'd like to achieve:
- Via a form, execute a function that will DELETE these four (4) tables.
- Thus, upon successful execution, I will have only 7 tables remaining (i.e., the 6 LK tables + table 00_DeleteTables itself).

Naturally, once I re-created the 10 LK tables -- and let's say uncheck the Boolean value for, e.g., table LK_Age AND then re-run the delete function, only 3 tables should be deleted then.

How could this be accomplished via VBA?

Thank you,
Tom

[Micron]

This is something I'd never, ever code for - especially not for someone else. Things can go real bad in a split second and I sure wouldn't want to know that I wrote code and you lost things you didn't intend to. See
https://docs.microsoft.com/en-us/off...oft-access-sql

You would have to loop through the Tables collection. Note: if anyone has a lock on a table, I believe it will fail, so checking for locks is just another complication.
Surely your db is not split into tables with related data as in your uploaded file?

[skydivetom]

Micron -- thank you, I can appreciate your position. I am aware of the potential "dangers" but there's a strategy for this particular approach.

I'll look at the provided link and hopefully I can come up w/ a solution.

Cheers,
Tom


P.S. If you care, please see https://www.accessforums.net/showthr...t=83240&page=3 post #37 (explanation at the bottom).

[ssanfu]

@ Tom,
I am in agreement with Micron re: "Things can go real bad in a split second". You have been warned...but it seems you are going to go ahead anyway.

I'll look at the provided link and hopefully I can come up w/ a solution.

I read the link post #37 (explanation at the bottom). I'm not sure about your use of DELETE or DROP. DROP removes TABLES. DELETE removes records in a table.


Try this on a COPY of your dB...... again a COPY of the dB.

Option Compare Database
Option Explicit

Public Sub DropTables()
    Dim d      As DAO.Database
    Dim r      As DAO.Recordset
    Dim sSQL   As String
    Dim k      As Integer
    Dim RC     As Integer
    Dim Msg, Style, Title, Response, MyString
    
    Set d = CurrentDb
    
    sSQL = "SELECT [00_DeleteTables].TableName"
    sSQL = sSQL & " FROM 00_DeleteTables"
    sSQL = sSQL & " WHERE [00_DeleteTables].Delete = True;"
    
    Set r = d.OpenRecordset(sSQL)
    If r.BOF And r.EOF Then
        MsgBox "No tables selected to DROP"
    Else
        r.MoveLast
        r.MoveFirst
        RC = r.RecordCount
        
        Msg = "Clicking YES will DROP " & RC & " tables."
        Msg = Msg & vbCrLf
        Msg = Msg & " Are sure you want to continue?" ' Define message.
        Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
        Title = "DROP Tables"                     ' Define title.
        ' Display message.
        Response = MsgBox(Msg, Style, Title)
        
        If Response = vbYes Then
            Msg = "Last Chance - Are you SURE??"
            Msg = Msg & vbCrLf
            Msg = Msg & " Are sure you want to continue?" ' Define message.
            Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
            Title = "DROP Tables - Last chance"   ' Define title.
            ' Display message.
            Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then
                Do While Not r.EOF
                    k = k + 1
                    'drop tables
                    sSQL = "DROP TABLE " & r.Fields(0) & ";"
                    Debug.Print sSQL
                    d.Execute sSQL, dbFailOnError
                    
                    '--- delete record from [00_DeleteTables] ---
'                    sSQL = "Delete * FROM [00_DeleteTables]"
'                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "'"
                    
                    '--- OR set the DELETED check box to FALSE in [00_DeleteTables] ---
                    sSQL = "UPDATE 00_DeleteTables SET [Delete] = False"
                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "';"
                    
                    d.Execute sSQL, dbFailOnError
                    r.MoveNext
                Loop
                MsgBox "Done!" & vbCrLf & "Dropped " & k & " tables"
            End If
        End If
    End If
    
    'clean up
    On Error Resume Next
    r.Close
    Set r = Nothing
    Set d = Nothing
    
End Sub

You have been warned!!!!



Edit: Forgot to mention you have a field "Deleted" in table 00_DeleteTables.
I am hoping you do not have a production (real) table with a field named "DELETED" as "DELETED" is a reserved word in Access.

[Minty]

Actually, I do this frequently (at DB start up), but only to update certain temp local tables from a remote linked backend that are used for lookups, thus reducing form loading time significantly when there are lots of combos etc.

I have a load of code that does this but it is quite involved as it also calls routines to check if the source table definitions have changed, and alerts the developer (because he's forgotten he's changed something...)
So sorry, I can't easily share it with you.

[skydivetom]

Steve (ssanfu):

Oh no!!! My tables are gone!!!

... just kidding!

Your solution is perfect and does exactly what I was hoping to achieve. Btw, I like the few reminders... "Are you sure?"; "Are you really sure?!" LOL

Although no big deal, I came across a run-time error (purely by accident so to speak).

Process:
- I selected 4 tables incl, "LK_Email" and executed the function.
- The 4 tables were deleted. Great!
- I then re-opened the [00_DeleteTables] a 2nd time... I was pleased to see all records were unchecked.
- I arbitrarily re-checked 2 tables (and without thinking) re-checked "LK_Email".
- Naturally, given the table didn't exist any longer, the run-time error 3376 popped up when executing the function again.

My question: Is there a simple way to by-pass this error (e.g., throw an custom msgbox to indicate that one or more of the tables were previously deleted)? ... or something like it.

Again, both you and Micron provided ample warning... but I DO LIKE YOUR SOLUTION A LOT.

Thank you,
Tom

[ssanfu]

There you go..... first thing you do is break my code!!!

But, yes, can check if a table actually exists or have error handling code ....


Working on it......

[ssanfu]

How about this?

Option Compare Database
Option Explicit

Public Sub DropTables()
    On Error GoTo HandleError     ' New line
    
    Dim d      As DAO.Database
    Dim r      As DAO.Recordset
    Dim sSQL   As String
    Dim k      As Integer
    Dim RC     As Integer
    Dim Msg, Style, Title, Response, MyString
    
    Set d = CurrentDb
    
    sSQL = "SELECT [00_DeleteTables].TableName"
    sSQL = sSQL & " FROM 00_DeleteTables"
    sSQL = sSQL & " WHERE [00_DeleteTables].Delete = True;"
    
    Set r = d.OpenRecordset(sSQL)
    If r.BOF And r.EOF Then
        MsgBox "No tables selected to DROP"
    Else
        r.MoveLast
        r.MoveFirst
        RC = r.RecordCount
        
        Msg = "Clicking YES will DROP " & RC & " tables."
        Msg = Msg & vbCrLf
        Msg = Msg & " Are sure you want to continue?" ' Define message.
        Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
        Title = "DROP Tables"                     ' Define title.
        ' Display message.
        Response = MsgBox(Msg, Style, Title)
        
        If Response = vbYes Then
            Msg = "Last Chance - Are you SURE??"
            Msg = Msg & vbCrLf
            Msg = Msg & " Are sure you want to continue?" ' Define message.
            Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
            Title = "DROP Tables - Last chance"   ' Define title.
            ' Display message.
            Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then
                Do While Not r.EOF
                    k = k + 1
                    'drop tables
                    sSQL = "DROP TABLE " & r.Fields(0) & ";"
    '                Debug.Print sSQL
                    d.Execute sSQL, dbFailOnError
                    
                    '--- delete record from [00_DeleteTables] ---
                    '                    sSQL = "Delete * FROM [00_DeleteTables]"
                    '                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "'"
                    
                    '--- OR set the DELETED check box to FALSE in [00_DeleteTables] ---
                    sSQL = "UPDATE 00_DeleteTables SET [Delete] = False"
                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "';"
                    
                    d.Execute sSQL, dbFailOnError
                    r.MoveNext
                Loop
                MsgBox "Done!" & vbCrLf & "Dropped " & k & " tables"
            End If
        End If
    End If
    
    'clean up
    On Error Resume Next
    r.Close
    Set r = Nothing
    Set d = Nothing
    
 ' --- new lines added from here down ---
Exit_HandleError:  
    Exit Sub
    
HandleError:
    Select Case Err.Number
        Case 3376
            k = k - 1
            Resume Next                           ' Use this to just ignore the line.
        Case Else                                 ' Any unexpected error.
            MsgBox Err.Number & Err.Description
            Resume Exit_HandleError
    End Select
    
    Resume Exit_HandleError
End Sub

[moke123]

Another option is testing for the tables existance

Public Function IsTableExists(ByVal strTableName As String) As Boolean
On Error Resume Next
 IsTableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

[Welshgasman]

[QUOTE=ssanfu;473669]@ Tom,

Option Compare Database
Option Explicit

Public Sub DropTables()
    Dim d      As DAO.Database
    Dim r      As DAO.Recordset
    Dim sSQL   As String
    Dim k      As Integer
    Dim RC     As Integer
    Dim Msg, Style, Title, Response, MyString
    
    Set d = CurrentDb
    
    sSQL = "SELECT [00_DeleteTables].TableName"
    sSQL = sSQL & " FROM 00_DeleteTables"
    sSQL = sSQL & " WHERE [00_DeleteTables].Delete = True;"
    
    Set r = d.OpenRecordset(sSQL)
    If r.BOF And r.EOF Then
        MsgBox "No tables selected to DROP"
    Else
        r.MoveLast
        r.MoveFirst
        RC = r.RecordCount
        
        Msg = "Clicking YES will DROP " & RC & " tables."
        Msg = Msg & vbCrLf
        Msg = Msg & " Are sure you want to continue?" ' Define message.
        Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
        Title = "DROP Tables"                     ' Define title.
        ' Display message.
        Response = MsgBox(Msg, Style, Title)
        
        If Response = vbYes Then
            Msg = "Last Chance - Are you SURE??"
            Msg = Msg & vbCrLf
            Msg = Msg & " Are sure you want to continue?" ' Define message.
            Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
            Title = "DROP Tables - Last chance"   ' Define title.
            ' Display message.
            Response = MsgBox(Msg, Style, Title)
            If Response = vbYes Then
                Do While Not r.EOF
                    k = k + 1
                    'drop tables
                    sSQL = "DROP TABLE " & r.Fields(0) & ";"
                    Debug.Print sSQL
                    d.Execute sSQL, dbFailOnError
                    
                    '--- delete record from [00_DeleteTables] ---
'                    sSQL = "Delete * FROM [00_DeleteTables]"
'                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "'"
                    
                    '--- OR set the DELETED check box to FALSE in [00_DeleteTables] ---
                    sSQL = "UPDATE 00_DeleteTables SET [Delete] = False"
                    sSQL = sSQL & " WHERE TableName = '" & r.Fields(0) & "';"
                    
                    d.Execute sSQL, dbFailOnError
                    r.MoveNext
                Loop
                MsgBox "Done!" & vbCrLf & "Dropped " & k & " tables"
            End If
        End If
    End If
    
    'clean up
    On Error Resume Next
    r.Close
    Set r = Nothing
    Set d = Nothing
    
End Sub

I'm curious now?
Why use an Update query to update the flag, when you are processing the record already in a recordset.?
I've read that queries are much faster than recordset processing, but surely that does not apply when you are actually in the recordset.?

Wouldn't bringing in the delete flag and updating the recordset as it is processed be a better option?

Still trying to learn here, despite not using Access that much anymore.

[Welshgasman]

On another note, on another post the O/P wanted to delete records from various tables.
I suggested a table to hold the names and the O/P stated they did not want to work that way, and now appears to be doing exactly that?

[skydivetom]

Steve:

I like "breaking" (testing) things... it's just part of the process I guess. Your code works like a charm. From my perspective, it's absolutely solid!

Quick edit...

I now have "translated" the testing version (posted in the thread) into my actual DB. And I realized there's one piece missing. Allow me to recap.

- I have a table where I mark fields to be deleted.
- This text fields holds values, e.g., to "ACTIVITY_NAME", "ACTIVITY_UIC_CODE", or "AGE".
- Now, as part of my lookup table creation, all of my tables will automatically receive an "LK_" prefix.
- For DB maintenance purposes, it will be much easier to continue adding records/field values into [00_DeleteTables] **without** the "LK_" prefix.
- Obviously, table "LK_Age" does not equal value "Age" (in [00_DeleteTables]).


My question: Can the VBA routine modified so that it will always add the "LK_" prefix to the values stored in field [00_DeleteTables].["TableName]?

Have a great weekend,
Tom

[accesstos]

Hi all!

[Edited] (Deleted my note about k=k+1)

My question: Can the VBA routine modified so that it will always add the "LK_" prefix to the values stored in field [00_DeleteTables].["TableName]?

Yes Tom, it can. Just tweak this line to:

'drop tables
           sSQL = "DROP TABLE LK_" & r.Fields(0) & ";"

Cheers,
John

[skydivetom]

John - wow... what a simple fix! Much obliged!!!

[accesstos]

[Edited]
Well, so far so good.