delete user-selected table(s)

[jguscs]

I would like to include a way for the user to be able to delete multiple tables simultaneously from the database.

I've got a (scrolling) list box where the user could select an available table from the database, along with a command button that would take the selected table from the list box and delete it when clicked.

The code for deleting a table is as follows:
DoCmd.DeleteObject acTable, ListBox.Value

What I want to know is if anybody has a way to select multiple values from the List Box simultaneously (like with a CTRL-click) so the user can have multiple tables deleted simultaneously when the Delete command button is clicked.

Thanks!

 

[dcx693]

You can use a multiselect list box. Change the multiselect property from None to Simple (or Extended). Then use a loop to go through all the items in the listboxes ItemsSelected collection. Those will contain the selected table names.

 

[jguscs]

Very nice. Thanks.
One more question regarding the list box.
Once an option has been selected in the list box, it stays selected even if you click somewhere other than the list box.
Is it possible to get rid of the highlighted selections in the list box with some code?

 

[dcx693]

You want to be able to clear all the highlighted selections so that nothing is selected?

 

[jguscs]

yeah and I think I just realized that if you hold down Ctrl while clicking on the (last) highlighted selection, it will no longer be selected.

But how about a Command button that performs a "deselect all" operation? Is that possible?

 

[dcx693]

Try this code:

Private Sub cmdClearList_Click()
Dim intX As Integer
 For intX = 0 To Me.lstbox.ListCount - 1
  If Me.lstbox.Selected(intX) Then
   Me.lstbox.Selected(intX) = False
  End If
 Next intX
End Sub


If it goes fast enough, then cool. I was trying to use the ItemsSelected property to loop through the collection faster, but I wasn't getting the syntax right.

 

[dcx693]

Finally got the syntax right, though thought I had tried this before:

Private Sub cmdClearList2_Click()
Dim varItem As Variant
 For Each varItem In Me.lstbox.ItemsSelected
  Me.lstbox.Selected(varItem) = False
 Next varItem
End Sub

This version should run faster than the previous one, though only marginally in small listboxes.

 

[jguscs]

Yes, I can tell it is slightly faster by the refresh of the list box.

You know what else works? (And probably the fastest of all...)
Me!lstbox.Requery

(I just discovered that, but I appreciate the technique of your code.)

 

[dcx693]

Yes, but Me!lstbox.Requery only works for extended multi-select listboxes, and I don't think it's even supposed to work that way, according to Microsoft here I make it a rule not to depend on "undocumented" behavior.

I tried to make the technique as general as possible (so I could use it when I needed it! )

 

[jguscs]

AH!
microsoft actually considers this a problem/bug!
i think i'll be using the code you suggested instead.
thanks for the tip...

are you ready for my next question?

 

[dcx693]

Bring it on!

 

[jguscs]

I'm trying to allow the option for the user to rename a selected table in the list box.
1) can I utilize a built-in pop-up box (like MsgBox) to ask for the new name, or do I have to create a new modal form?
2) i was trying to use the following code, but the last parameter of the DoCmd.Rename (Me.LstTables.ItemsSelected.Item) probably isn't correct.
Dim newName As String
newName = "renamed"
DoCmd.Rename newName, acTable, Me.LstTables.ItemsSelected.Item

 

[dcx693]

You can use a Msgbox to prompt for a new name. What version of Access do you use?

 

[jguscs]

Access 2000

 

[dcx693]

And do you use DAO or ADO? If you have no idea what I'm talking about, you probably use ADO.

 

[jguscs]

ADO, i think.

 

[dcx693]

You can use the following ADO code to delete a specified table:

Sub TableDelete(strTablename As String)

Dim cat As ADOX.Catalog

 Set cat = New ADOX.Catalog
 cat.ActiveConnection = CurrentProject.Connection
 On Error Resume Next
 cat.Tables.Delete strTablename
 Set cat = Nothing

End Sub

Use the sub like this:

Call TableDelete "mytablename"

 

[jguscs]

thank you.

in the mean time, i'm still having trouble with the code to rename a table in the list.

(where TxtRename is a text box that the user types a name into for renaming purposes):
DoCmd.Rename Me.TxtRename, acTable, Me.LstTables.Selected(ItemsSelected)

Basically, I don't know how to properly refer to the value of the selected row in the list.

 

[jguscs]

got it:

For Each varItem In Me.LstTables.ItemsSelected
DoCmd.Rename Me.TxtRename, acTable, Me.LstTables.ItemData(varItem)
Next varItem

that is more efficient than

For Each varItem In Me!LstTables.ItemsSelected
For count = 0 To Me!LstTables.ColumnCount - 1
DoCmd.DeleteObject acTable, Me!LstTables.Column(count, varItem)
Next count
Next varItem

...for a single-columned list, that is.

 

[dcx693]

Ooops, by bad. I was thinking about your first problem an deleting tables. My bad.

As for renaming the table, change the last argument in your statement. This expression:
Me.LstTables.Selected(ItemsSelected)
returns True or False telling you if the item in question is selected. Try this:

Me.lstTest.ItemData(varItem)
where varItem is a number corresponding to the position within the listbox.