By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

Deleting multiple tables misses some each iteration

gnawoncents
100+
P: 214
Greetings.
I have a split Access 2013 database (back end with tables and front end with everything else) an am having trouble with deleting tables.

I run the code below to check the back end for only tables that meet my required criteria then delete them from the back end and delete the links from the front end.

When I test the code using a simple msgbox line naming the tables that will be deleted, it iterates through all the appropriate tables; however, when I run the actual code, only two or three tables are deleted at a time (with no popup errors). If I run the code again, a few more are deleted, and I can continue running it until all the correct tables are deleted.

Why won't it delete all the tables in one go?

I suspect it is some sort of a timing issue, but am not sure. Ideas anyone? Thanks for your help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDeleteTables_Click()
  2.  
  3. '---------------------------------------------------------------------------------------------
  4. 'Find all past and future-dated (not current month) schedule tables not in use then
  5. 'delete them from the back end and remove the links in the front end
  6. '---------------------------------------------------------------------------------------------
  7.  
  8. Dim dbDEL As Database
  9. Dim tdfDEL As TableDef
  10. Dim strTable As String
  11. strTable = ""
  12.  
  13. 'Get the location of the back end
  14. Dim strBackEndPath As String
  15. Dim lenPath As Integer
  16. Dim i As Integer
  17. Dim j As Integer
  18.  
  19. strBackEndPath = CurrentDb.TableDefs("tbScheduleALL").Connect
  20.  
  21. 'Remove the datebase & password prefix
  22. j = InStrRev(strBackEndPath, "=") + 1
  23. strBackEndPath = Mid(strBackEndPath, j)
  24.  
  25. Set dbDEL = OpenDatabase(strBackEndPath)
  26.  
  27. For Each tdfDEL In dbDEL.TableDefs
  28.     strTable = tdfDEL.Name
  29.     'Check to see if the table is a schedule table, linked, and not marked as in use (fldActive <> -1)
  30.     If (strTable Like "tb######*") And strTable <> "tb" & Format(Date, "yyyymm") Then
  31.         If Len(CurrentDb.TableDefs(strTable).Connect) > 0 Then
  32.             If (DLookup("[fldActive]", strTable) <> -1) Then
  33.                 dbDEL.TableDefs.Delete strTable 'delete table from the back end
  34.                 DoCmd.DeleteObject acTable, strTable 'Remove link to deleted tables from the front end
  35.             End If
  36.         End If
  37.     End If
  38. Next
  39.  
  40. dbDEL.Close
  41. Set dbDEL = Nothing
  42. Set tdfDEL = Nothing
  43.  
  44. End Sub
  45.  
Dec 28 '16 #1

✓ answered by jforbes

I have a feeling that your iterator (For Each Loop) is not aware that tableDefs are being Deleted. So when one is deleted, either the iterator gets lost, or it skips what would become the new current one (after the current one is deleted), effectively skipping the one right after the one that is deleted.

You might be able to re-write you loop to a Do While and reference the TableDefs by Index.

Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
I have a feeling that your iterator (For Each Loop) is not aware that tableDefs are being Deleted. So when one is deleted, either the iterator gets lost, or it skips what would become the new current one (after the current one is deleted), effectively skipping the one right after the one that is deleted.

You might be able to re-write you loop to a Do While and reference the TableDefs by Index.
Dec 29 '16 #2

gnawoncents
100+
P: 214
Thanks--I'll give that a go!
Dec 29 '16 #3

gnawoncents
100+
P: 214
Many thanks, jforbes, you were right on the mark. Instead of deleting the tables in my For Each Loop, I created and added them to a collection, then iterated through the collection once complete. It works perfectly. You are awesome!
Dec 29 '16 #4

NeoPa
Expert Mod 15k+
P: 31,489
That's absolutely correct.

Every time a TableDef is removed from the collection any previous pointer, or object set, to that collection remains pointing to the original collection and is not equivalent to a pointer/object set to the collection after one is deleted. The original keeps the same dimensions as before but simply sets the place for deleted entries to a marker indicating it no longer exists.

I added two new tables to my database and ran the following code (All on one line in the Debug Pane but I show it here separated out for ease of reading) :
Expand|Select|Wrap|Line Numbers
  1. Set TS=CurrentDb().TableDefs
  2. ?TS.Count;CurrentDb().TableDefs.Count;"|";
  3. Call DoCmd.DeleteObject(acTable,"tblUpdate1")
  4. ?TS.Count;CurrentDb().TableDefs.Count;"|";
  5. Call DoCmd.DeleteObject(acTable,"tblToDelete1")
  6. ?TS.Count;CurrentDb().TableDefs.Count;
The results I got were :
Expand|Select|Wrap|Line Numbers
  1.  93  93 | 93  92 | 93  91
You'll notice the TS object doesn't get reduced but continues to 'think' it's covering 93 tables even after two have been deleted.

In case you're wondering, adding tables invalidates the object entirely. Had you tried that your code would have crashed instead.

@Nonsense.
Your solution is a good one. You should also have been able to remove them in order from the original as long as you went backwards through the collection. That would involve accessing it numerically though, which is less intuitive than using a string reference.

Another thing to consider, assuming you only ever want today's one at any one time, is to rename and repoint the single link to ensure it points to the correct table. That might get complicated though as you'd need to identify the table to rename by something other than its name of course. All just ideas.
Jan 6 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.