Find/Delete Table in TableDefs Collection

[casey]

Hello,

I am trying to delete a set of tables using the TableDefs.Delete method. I would like to specify a TableName and have VB search the TableDefs.Name to see if that table exists. If it does, I would like to delete it. If it doesn't exist, I would like to search on the next TableName.

I can get it to work, but only by searching through all TableDefs.Names in the database for each TableName to search for. Is there a way to specify a TableName and then have VB Find the table in the collection? Sort of like the DAO.Recordset.FindFirst Method?

Thanks,

 

[raskew]

Hi -

Give this a look. Be very careful (backup your db first) because if you mess-up, there's no recovery.

Sub DeleteTableTest3(pMyTable As String)
' This procedure deletes specified tables and any
' existing relationships the tables are participating
' in.
' Used inappropriately, it will have a devastating
' effect on an application.
' For safety's sake, I've commented-out the actual
' commands (they follow debug.print in every case).
' Once you've examined the code and are ready to go,
' remove the comments.

Dim db      As Database
Dim td      As TableDef
Dim Test    As String
Dim tName   As String
Dim thisrel As Relation

    Set db = CurrentDb
    
    On Error Resume Next
    
    tName = pMyTable
    '**********
    'Does table tName exist?
    'If true, delete it;
    '**********
    
      Test = db.TableDefs(tName).Name
      If Err <> 3265 Then
         '**********
         ' Since the delete action will fail if the
         ' table is participating in any relation, first
         ' find and delete existing relations for table.
         '**********
         For Each thisrel In db.Relations
            If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
               Debug.Print tName & " | " & thisrel.Name
               'db.Relations.Delete thisrel.Name
            End If
         Next thisrel
         '**********
         ' Now, we're ready to delete the table.
         '**********
         Debug.Print tName & " will be deleted"
         'docmd.SetWarnings False
         'docmd.DeleteObject acTable, tName
         'docmd.SetWarnings True
      End If
    'End If
    db.Close
    Set db = Nothing
End Sub

HTH - Bob

 

[casey]

That worked. Just Plugged it in and it Played. Now if I could get the logic corrected in the rest of the program.

Thanks.

 

[casey]

raskew,

I didn't notice this right away, but for some reason the part that's supposed to delete the relations is not working correctly. It's not finding the relations that I have between the tables. When I step through the code, it states "object variable or With block variable not set" when I place the cursor over the thisrel.Table or thisrel.ForeignTable in the following line...

If thisrel.Table = tName Or thisrel.ForeignTable = tName Then

Is there a step I'm missing? It seems from the other examples I've seen that this should find the relations, but it's not. Is this deleting the relations for you?

 

[casey]

Actually, I closed the db and reopened it and now the relations are gone, but the tables are still there. I have them set up in the Relationships. Do I need to delete them from the Relationships before the changes take affect? I'm puzzled why this is occuring. Before I added the relations between teh tables, this was working great. Now it doesn't seem to work? Any ideas?

 

[casey]

I guess the reason that it wouldnb't delete the table is that I had a form open that was making use of the data in the table "oopsie". I'm guessing that's the problem. I'll keep playing with it.