Deleting Linked Tables - Access 97 1

[suzey]

Hi Everyone,

I have a small database that I link to various tables. The number of tables can vary, based on what the user asked for, so for example the user could ask for tables 1 to 10, which I then link to and show them the results. The next time they could ask for tables 2 to 25, so I then link to these tables and show them the results.

I would like to tidy up the database after each query and delete all the linked tables.

I currently have the following code :

Function deletelinks()

Dim db As Database
Dim TD As TableDef
Set db = CurrentDb
For x = 1 To db.TableDefs.Count
Set TD = db.TableDefs(db.TableDefs.Item(x).Name)
If db.TableDefs.Item(x).Name Like "*bom" Then
Debug.Print "" & db.TableDefs.Item(x).Name
db.TableDefs.Delete db.TableDefs.Item(x).Name
End If
Next x
End Function

But when I use this code, the tabledefs.count could for example start at 20 tables, but as soon as I find the first one I want to delete, it appears to renumber all the tables, so if it deletes table 1 in the tablesdefs, it would skip table 2 as the next time in the loop table 2 has moved to table 1.

I've also tried changing my for.. next loop to a for....each, but this appears to do the same, and means I need to rerun this function several times before all the links have been deleted.

Has anyone got any ideas on a better way to do this.

Thanks in advance




Sue

 

[TonyJollans]

Hi suzey,

Run your loop from the end backwards -

For x = db.TableDefs.Count To 1 Step -1

- that way you don't change anything you haven't yet processed.

Enjoy,
Tony

 

[suzey]

Thanks Tony, worked a treat.


Sue