Deleting tables from code

[pullingteeth]

I maintain a "development" version of a "production" database. Every time I release it I first delete all of the tables in development, import the tables from production (with current data), build an MDE file, and move it to the production location.

I'm working on automating at least the first two stages; so far I have (for deleting all tables):

Private Sub Command3_Click()
    Dim tname As String, tname2 As String
    Dim i As Integer
    
    ' authenticate that user is qualified to do this
    
    For i = 0 To (CurrentDb.Relations.count - 1)
        tname = CurrentDb.Relations(i).Table
        tname2 = CurrentDb.Relations(i).ForeignTable
        If (Not isSysTable(tname)) And (Not isSysTable(tname2)) Then
            ' XXX somehow delete it
        End If
    Next i
    
    For i = 0 To (CurrentDb.TableDefs.count - 1)
        tname = CurrentDb.TableDefs(i)
        If Len(tname) > 0 And (Not isSysTable(i)) Then
            DoCmd.DeleteObject acTable, tname
        End If
    Next i
End Sub

Private Function isSysTable(tname As String) As Boolean
    isSysTable = (InStr(1, tname, "MSys") <> 1)
End Function

This code is non-functional, however, as Access demands that I delete the relationships first, and while I can find all of the relationships, I cannot figure out how to delete them (see the XXX, in the code above).

Any thoughts?

Thanks

 

[willir]

You might want to look at DROP table in SQL and DAO.