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

[skydivetom]

John:



Sorry, yes, you're absolutely right... I noticed it after I posted the sample file. I had already edited the response but guess it was too late for the edit.

Anyhow, the "LK_" prefix works great now.

At the same time, I'm trying to figure as to how I may want to slightly modify the process.

Allow me to recap the existing procedure.
1. I have 10 LK_ tables in the DB.
2. My table [00_DeleteTables] includes 10 records.
3. Let's say, I check 3 records in the table: Age, Education, Email.
4. Next, upon running the code, the 3 tables get deleted AND the Boolean value is set to FALSE.
5. Also, in the event I'd return to [00_DeleteTables] and, e.g., re-check "Age" followed by re-running the code, the msgbox indicates that "0" tables were deleted (given "Age" doesn't exist any longer).

At first glance, steps 1-5 make sense and will NOT result in any unexpected errors. Great!

Here's the thing though...
a. Boolean value "TableName" should NOT really be re-set each time (i.e., setting all records to FALSE) after executing the function.
b. Ultimately, the conditions (TRUE or FALSE) for the LK_ tables do NOT change.
c. So, for now, I added a 2nd Boolean with keeps the TRUE/FALSE indicators. And after each run, I certainly could run an UPDATE query to overwrite 1st Boolean ("Delete") with 2nd Boolean ("Status").
d. Naturally, the latter is a bit cumbersome.
e. That said, I un-commented the 2 strSQL commands (see below). Hence, [TableName] does NOT get updated upon a run.

                'Uncomment next 2 lines if [TableName] needs to be set to FALSE upon deleting the LK table
                'strSQL = "UPDATE delete_NonLOVs SET [Delete] = False"
                'strSQL = strSQL & " WHERE TableName = '" & rsFields.Fields(0) & "';"

f. Step e., however, results in getting the error when attempting to DELETE a table which was previously already deleted.

So, I'm not sure if the above falls in the "chicken vs. egg" scenario (i.e, which actions comes first... setting Boolean to TRUE or FALSE once tables were deleted).

So, ultimately, I'm trying to figure out the following:
- I'd like the Boolean flags (TRUE) remain unchanged in [Delete] once the procedure was executed.
- Once a table has been deleted (while the Boolean flag is set to TRUE), I merely want to skip to the next table(s) that still exist and have a flag = TRUE.

I hope this makes sense. Any feedback on the modification procedure is greatly welcome.

Cheers,
Tom

[accesstos]

Tom,

I was ready to post an alternative way/code, just for fun and demo, before see your last post. Maybe is doing what you are asking.

Function DelTables() As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strIN As String

    Set db = CurrentDb
    'Query the tables selected to drop.
    Set rs = db.OpenRecordset("SELECT 'LK_' & [TableName] AS tName FROM 00_DeleteNonLOVs " _
                              & "WHERE [00_DeleteNonLOVs].Delete = True;")
    On Error Resume Next
    With rs
        While Not .EOF
            'Build a "list" with the existing tables to drop.
            strIN = strIN & ", " & db.TableDefs(!tName).Name
            .MoveNext
        Wend
    End With
    rs.Close
    Set rs = Nothing
    On Error GoTo 0
    
    strIN = Mid(strIN, 3)
    If Len(strIN) Then
        If MsgBox("Are you sure that you want to delete the selected tables?" _
                  , vbQuestion + vbYesNo + vbDefaultButton2, "Delete tables") = vbYes Then
            'Drop all tables at once.
            db.Execute "DROP TABLE " & strIN
            'Prepare the "list" of table names for WHERE clause.
            '(Remove the 'LK_' and add single quotes on names.
            strIN = Replace(Replace(strIN, "LK_", ""), ", ", "', '")
            'Update the Delete field of droped tables at once
            db.Execute "UPDATE [00_DeleteNonLOVs] SET [Delete]=0 WHERE TableName IN('" & strIN & "')"
            'Return the count of droped tables via the RecordsAffected of db.
            DelTables = db.RecordsAffected
        End If
    Else
        MsgBox "No tables selected to drop or does not exists. Review table [00_DeleteNonLOVs].", , "Delete tables"
    End If
    Set db = Nothing
End Function

Select some tables via 00_DeleteNonLOVs and run the function from Immediate window as below:

?DelTables

Will return the actualy count of droped tables.

[Micron]

Now try that routine when a user has a lock on one of those tables. Or can that not happen?

[accesstos]

Now try that routine when a user has a lock on one of those tables. Or can that not happen?

Of course, will fail, so, the flow of the program it's up to the caller decisions.

[skydivetom]

John -- as you indicated, your code is "just for fun and demo" (and if I understood you correctly, you developed it before seeing my post #16).

At any rate, my goal is to NOT having to update the records to TRUE after each run.

So, is there a way to accomplish both a) leaving [DELETE] unchanged but at the same time b) not throwing errors when attempting to delete a table which no longer exist (due to previous deletions) ? Let's say the following scenario occurs:

Run #1:
- [Age] = TRUE; [Education] = TRUE
- Both tables "Age" and "Education" will be deleted

Run #2:
- Analyst updates the status of the tables and puts a check into [Gender] as well as [Age] again.
- So, even though 2 tables have been checked, only "Gender" can/will be deleted.
- It would be great to include some warning indicating that either "1 out of 2 selected tables" (or a msg indicating "some tables were previously deleted"). However, this custom message may be going overboard.
- If I merely prevent getting an error due to "Age" no longer being available, that would be ok.

Tom

[Micron]

Of course, will fail, so, the flow of the program it's up to the caller decisions.

Just to be clear, by "can that not happen" I was speculating that perhaps a lock would never be placed on those tables, not that you can't delete them if there is a lock. The reason for suggesting it be tried is that IIRC that was one of my points in the first post reply.

[skydivetom]

John -- I took a short break here.. I'm back now.

Ok, I commented out the following line: 'db.Execute "UPDATE [00_DeleteNonLOVs] SET [Delete]=0 WHERE TableName IN('" & strIN & "')"

It's a bit trickier to do some testing as it appears that the tables don't get deleted until I close the form. And even if I close the form, it seems like it takes a few seconds before they actually get deleted.

I have a new computer should performance shouldn't be an issue. What am I missing?

[accesstos]

John -- I took a short break here.. I'm back now.

Ok, I commented out the following line: 'db.Execute "UPDATE [00_DeleteNonLOVs] SET [Delete]=0 WHERE TableName IN('" & strIN & "')"

It's a bit trickier to do some testing as it appears that the tables don't get deleted until I close the form. And even if I close the form, it seems like it takes a few seconds before they actually get deleted.

I have a new computer should performance shouldn't be an issue. What am I missing?

It's naturally Tom, nothing has to do with your machine. The navigation pane just have to refresh.
But, I don't understand... Do you prefer to remain checked the [Delete] of dropped tables?

Edit: (Hmm... The daemon of the forum dropped my edit.)
So,...
I think that the table 00_DeleteNonLOVs needs a boolean field as drop indicator. Then, the user have to work with a query which hides the records (tablenames) that has been dropped already. Doesn't make sense to see records of tables that doesn't exists. My thought.

[skydivetom]

John:

Ok, allow me to respond to both:

1) Performance:
I don't want to disgress, but in another post, a DELETE function is run. In that scenario, 150 tables are dropped/deleted within a split second after calling the delete routine.
Please refer to file (ForTomLKwithMsg.ZIP ) provided by Orange (Jack) in Post #41 @ https://www.accessforums.net/showthr...t=83240&page=3

Thus, my question: What's the difference between the delete routine in that sample file vs. the delete routine in this thread?

---------------

2. Boolean Flag -- allow me to summarize in 2 or 3 words:
- Again, this goes back to https://www.accessforums.net/showthr...t=83240&page=3
- Specifically, a lookup (LK) table generation routine scans through multiple tables (imported from XLS) and automatically creates LK tables for each distinct fieldname.
- In my actual data set, this currently generates nearly 150 LK tables. It's easier to have the entirety of all XLS columns processed vs. having to define which ones a) I want to have vs. b) which ones are NOT needed.
- Based on the latter, I know that I only need to keep, e.g., 70 LK tables out of the 150 generated LK tables. n = 70 may change based on additional analysis, but for the sake of arguments, let's call it the "final #" that will NOT change.
- Thus, inherintly, I also know which 80 LK tables I do NOT need to keep. This # also does not change. Thus, I always want the [DELETE] = TRUE for these 80 fields/tables.
- Based on the present routine though, once I deleted the 80 tables, the flag is set to FALSE.
- Then, when I re-generate the 150 LK tables ** with new data values ** and I want to get rid of the 80 tables again, I would have to update my [DELETE] from FALSE to TRUE for those 80 tables.

So, in summary, I merely want to be able to delete all tables where the flag = TRUE. If a table where flag = TRUE was previously deleted, I merely want to skip it and move on to the next available table to be deleted.

Like I mentioned... 2 or 3 words to explain this activity.

[accesstos]

Tom,
I apologies, I didn't follow your threads from the start. I just jump in to answer about the "LK_" in code.
The difference of this function is the deletion of all tables in only one SQL execution.
"DROP TABLE Table1, Table2, Table3..."
Specifically, in our case:

'Drop all tables at once.
db.Execute "DROP TABLE " & strIN

I leave my last version and jump out, while the thread is indicated as solved.

Function DelTables(Optional ByVal fShowMessage As Boolean) As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strIN As String

    Set db = CurrentDb
    'Query the tables selected to drop.
    Set rs = db.OpenRecordset("SELECT 'LK_' & [TableName] AS tName FROM 00_DeleteNonLOVs " _
                              & "WHERE [00_DeleteNonLOVs].Delete = True;")
    On Error Resume Next
    With rs
        While Not .EOF
            'Build a "list" with the existing tables to drop.
            strIN = strIN & ", " & db.TableDefs(!tName).Name
            .MoveNext
        Wend
    End With
    rs.Close
    Set rs = Nothing
    On Error GoTo 0

    strIN = Mid(strIN, 3)
    If Len(strIN) Then

        If MsgBox("Are you sure that you want to delete the selected tables?" _
                  , vbQuestion + vbYesNo + vbDefaultButton2, "Delete tables") = vbYes Then
            'Drop all tables at once.
            db.Execute "DROP TABLE " & strIN
            'Return the count of droped tables via the RecordsAffected of db.
            DelTables = db.RecordsAffected
            db.TableDefs.Refresh

            If fShowMessage Then
                'Prepare the "list" of table names for WHERE clause.
                '(Remove the 'LK_' and add single quotes on names.
                strIN = Replace(Replace(strIN, "LK_", ""), ", ", "', '")
                'Get the names of the tables that not found.
                Set rs = db.OpenRecordset("SELECT 'LK_' & [TableName] AS tName FROM 00_DeleteNonLOVs " _
                                          & "WHERE ([Delete]=True) AND (TableName NOT IN('" & strIN & "'))")
                If rs.RecordCount Then
                    strIN = vbNullString
                    With rs
                        While Not .EOF
                            'Build a "list" with tables that not found.
                            strIN = strIN & vbCrLf & !tName
                            .MoveNext
                        Wend
                        MsgBox "The " & .RecordCount & " tables below not found:" & strIN, , "Delete tables"
                    End With
                End If
                rs.Close
                Set rs = Nothing
            End If
        End If
    Else
        MsgBox "No tables selected to drop or does not exists. Review table [00_DeleteNonLOVs].", , "Delete tables"
    End If
    Set db = Nothing
End Function

You can choose to display the message about the skipped tables by the relevant optional argument. For example:

?DelTables(True)

Cheers,
John

[skydivetom]

John -- copy, I totally understand. Thank you for posting your most recent code.

I noticed that " RefreshDatabaseWindow" will update the navigation pane.

As always, thank you!

[skydivetom]

Steve, John:

I posted a follow-up question at: https://www.accessforums.net/showthr...746#post473746

I apologize but I probably should not have closed the previously thread so soon. Anyhow, I'd welcome any ideas how to even better the existing solution.