Tabledefs.Delete does not delete all the tables

[RichD]

I am trying to delete the links to attached tables, while keeping the system tables and a couple of others.

Here is my code, run off a command button:

Private Sub Command71_Click()
Dim db As Database
Dim tblD As TableDef

Set db = CurrentDb()

For Each tblD In db.TableDefs
If Not Left(tblD.Name, 7) = "DCtable" Then 'tables to be kept
If Not Left(tblD.Name, 4) = "Msys" Then 'system tables
db.TableDefs.Delete tblD.Name
End If
End If
Next

db.TableDefs.Refresh
Set db = Nothing


DoCmd.RunCommand acCmdLinkTables

End Sub

I have approx twenty attached tables. When I run the above code it deletes less than half of them. Does anyone know where I am going wrong?

Any help gratefully received.

TIA,

Rich

Lead Developer

http://www.blsoftware.co.uk

 

[mp9]

Maybe...

...if some of the tables are related to others and don't have "cascade delete" set on the relationship, if you try to delete the parent table before the child table then the parent table will not delete...

...maybe!

http://www22.brinkster.com/accessory/

 

[RichD]

Good point, but I am using test data and there are no relationships set. Also i am only trying to delete the links to attached tables not the tables themselves. The MS Developers HAndbook says that 'if the TAbledef object is a linked table only the link will be removed.'

Thanks for your help... the search continues.
Rich



Lead Developer

http://www.blsoftware.co.uk

 

[KenReay]

Hi

by deleting the tabledef, within the for each loop you are changing the terminator of the loop, ie the number of tabledefs

not the most elegant solution, but to make the point try

Private Sub Command71_Click()
Dim db As Database
Dim tblD As TableDef
Dim blnFinished as Boolean

Set db = CurrentDb()

Do While TRue
For Each tblD In db.TableDefs
If Not Left(tblD.Name, 7) = "DCtable" Then 'tables to be kept
If Not Left(tblD.Name, 4) = "Msys" Then 'system tables
db.TableDefs.Delete tblD.Name
End If
End If

db.TableDefs.Refresh
blnFinished = True
For Each tblD In db.TableDefs
If Not Left(tblD.Name, 7) = "DCtable" Then 'tables to be kept
If Not Left(tblD.Name, 4) = "Msys" Then 'system tables
blnFinished = FAlse
End If
End If

if blnFinish Then
EXit Do
End If

Next
loop
db.TableDefs.Refresh
Set db = Nothing


DoCmd.RunCommand acCmdLinkTables





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[erici]

I realize this isn't your solution, but I would suggest checking for a connect property value to verify that the table is an attached table and not a local table. If you check the connect propery, you can probably get by without checking teh MSYS (local tables) and maybe teh DCtables as well if they are local tables that are not attached.

If Not Left(tblD.Name, 7) = "DCtable" AND Not Left(tblD.Name, 4) = "Msys" AND CBOOL(Len(tbld.Connect) Then db.TableDefs.Delete tblD.Name
End If

 

[psemianonymous]

When looping through a collection and deleting items in that collection, loop using the .Count property looping backwards. Like so:

set tbls = CurrentDb.Tabledefs
For i = tbls.Count - 1 To 0 Step -1
    If Not (tbls(i).Name Like "MSYS*") Then
        tbls(i).Delete
    End If
Next i

 

[RichD]

Thanks guys for all your help. Its almost there.

One other problem I have with this is that I want to open the 'link tables' dialog box immediately after the user has deleted the currently attached tables (so that he can work on another database).

These are the last three lines of code:

db.TableDefs.Refresh
Set db = Nothing
DoCmd.RunCommand acCmdLinkTables

I had hoped that db.Tabledefs.Refresh would remove the names of the deleted tables from the database window, but they remain there although the tables themselves have gone.
Clicking on another tab in the database window (say Forms) and then back to tables clears the names.

The problem is if I delete the links and then relink to another database any tables with the same name will have the suffix 1 added. eg tblOrders and tblOrders1

Any ideas?

I will be out today, so will not be able to respond to any replies, but will be back at it tomorrow.

Thanks again to everyone.

Rich



Lead Developer

http://www.blsoftware.co.uk

 

[KenReay]

Hi

Two thoughts (first one a guess)

Would hiding the db window, then showing it again help?

Would it not be preferable to hide the db window from the user anyway, and simply ask then for the path of the db to be linked to, using a dialog box, and then relink the tables, why would the user want/need to see tehdatabase window?, it just encourages clicking and tinkering

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Mtlca401]

Here is the code to refresh the database window

Application.RefreshDatabaseWindow

How did you fix your code to delete all of the tables you want? I can't figure it out. I know what it's doing, I just can't figure out how to fix it.

 

[RichD]

Thanks for the input. I have not had time to go back to this problem, so it is still not resolved, but I think that KenReay has the answer; you just keep looping around until all the tables are deleted. Then with your answer to update the database window it should be OK.

I wlll probably get back to this in a week or so.

Thanks to everyone who contributed.

Rich

Lead Developer

http://www.blsoftware.co.uk