Trying to delete two linked tables from front-end
I am trying to delete two linked tables via VBA code from the front-end of a database. The will always include the string "archive" in the linked table names. I've tried this which isn't working:
Dim index As Integer
Dim dbs As Database
Dim tdf As TableDef
For index = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(index)
If InStr(tdf.Connect, "Archive") Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next indexASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Anders
<I wonder if that wouldn't skip the table if the 2 tables are right after other. > why would it skip?
<I wonder if that wouldn't skip the table if the 2 tables are right after other. > why would it skip?
ASKER
Worked perfectly!
@Rey
I remember cases where deleting items from a collection while looping it will cause every second item to be skipped.
Think of it like this: Deleting item 3 causes item 4 to become the item 3 in the collection, but the loop now finished with item 3 moves on to item 4 (which was previously item 5). Does that make sense?
I remember cases where deleting items from a collection while looping it will cause every second item to be skipped.
Think of it like this: Deleting item 3 causes item 4 to become the item 3 in the collection, but the loop now finished with item 3 moves on to item 4 (which was previously item 5). Does that make sense?
did you look at the code I posted?
I just ran a test. Created 10 tables in a blank database and ran your code (without the "archive" restriction), and all tables were successfully deleted.
If I replaced the DeleteObject line with
I've just adopted the approach, that when deleting items from a collection, I always iterate backwards through them to avoid such issues.
Regardless, the code you posted does work. :)
If I replaced the DeleteObject line with
dbs.TableDefs.Delete tdf.NameI've just adopted the approach, that when deleting items from a collection, I always iterate backwards through them to avoid such issues.
Regardless, the code you posted does work. :)
I wonder if that wouldn't skip the table if the 2 tables are right after other.
@Steve
Instead of tdf.Connect try tdf.SourceTable