Delete all Tables

[Betty Boop]

I am trying to import tables using an odbc connection on a periodic basis. I don't want to create a second copy of the tables, so I have to delete them all before doing the import. I found another question similar to this and tried the following code, but it doesn't work.

Private Sub btnPBGUpdate_Click()
Dim TblName As String
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Search for closed Tables.
For Each obj In dbs.AllTables
TblName = obj.Name
If obj.IsLoaded = False Then
' Delete the tables.
If Not (Left(TblName, 4)) = "MSys" Then
Access.DoCmd.DeleteObject acTable, obj.Name
Else

End If
Next obj
End Sub

I get a compile error Next without For. I am very new to this, so I really don't know what I am doing and would appreciate any help. Thanks.

[jyoung2]

You mised the closeing "end if" for the "If obj.IsLoaded = False Then" see the bolded if

Private Sub btnPBGUpdate_Click()
    Dim TblName As String
    Dim obj As AccessObject, dbs As Object
 
    Set dbs = Application.CurrentData
 
     '  Search for closed Tables.
    For Each obj In dbs.AllTables
        TblName = obj.Name
        If obj.IsLoaded = False Then
             ' Delete the tables.
            If Not (Left(TblName, 4)) = "MSys" Then
                Access.DoCmd.DeleteObject acTable, obj.Name
            Else
 
            End If
        End if
    Next obj
    End Sub

I am trying to import tables using an odbc connection on a periodic basis. I don't want to create a second copy of the tables, so I have to delete them all before doing the import. I found another question similar to this and tried the following code, but it doesn't work.

Private Sub btnPBGUpdate_Click()
Dim TblName As String
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Search for closed Tables.
For Each obj In dbs.AllTables
TblName = obj.Name
If obj.IsLoaded = False Then
' Delete the tables.
If Not (Left(TblName, 4)) = "MSys" Then
Access.DoCmd.DeleteObject acTable, obj.Name
Else

End If
Next obj
End Sub

I get a compile error Next without For. I am very new to this, so I really don't know what I am doing and would appreciate any help. Thanks.

[Betty Boop]

Thanks. Now I get a message that says some of the tables can't be deleted because they are part of a relationship. Is there a way to programatically remove all relationships before running this script?

[JustJim]

Thanks. Now I get a message that says some of the tables can't be deleted because they are part of a relationship. Is there a way to programatically remove all relationships before running this script?

You would only have to re-enter the relationships. A better method might be to "Empty" the current tables, then append the incoming data to the same (but now empty) table. This keeps all references to the original table, eg relationships, queries, etc intact.

"Emptying" a table is as simple as opening an editable recordset, deleting a record, moving to the next record and continuing until the end of the recordset.

Air Code Warning: just typing off the top of my head here, following is not tested.

Private Sub SomeButton_Click()
 
Dim dbMyDatabase as Database
Dim rsMyRecordset as Recordset
 
Set dbMyDatabase = CurrentDB()
Set rsMyRecordset = dbMyDatabase.OpenRecordset("TableToEmpty", dbOpenDynaset)
 
Do Until rsMyRecordset.EOF
     With rsMyRecordset
          .Delete
          .MoveNext
     End With
Loop
 
rsMyRecordset.Close
set rsMyRecordset = Nothing
Set dbMyDatabase = Nothing
Exit Sub

Jim