Cycling through Table Defs and Deleting

I have a subroutine that runs on a local copy of a database and a network stored master.

To allow users to run my Excel add-in offline, I create a local version that has the same table names as the master but with only the relevant fields.

The first sub does not give any errors however when I reviewed the local database to ensure that the tables were being deleted and recreated, only 2 of the 4 tables were being deleted.

I developed a work-around, but I’d like to know if there is something I missed or did incorrectly or is this just a quirk of working with Access databases via DAO?

First Sub (only deletes 2 tables instead of all 4):

Sub gUpdateDBTables()
    Dim DBPath As String
    Dim sSQL_Table1 As String
    Dim sSQL_Table2 As String
    Dim sSQL_Table3 As String
    Dim sSQL_Table4 As String
    Dim DB As DAO.Database
    Dim DB2 As DAO.Database
    Dim tblDef As DAO.TableDef
        'Get DB File Locations From Registry
        LocalDBPath = Interaction.GetSetting("Tools", "SettingsFileInfo", "Local_DB_Location")
        DBPath = Interaction.GetSetting("Tools", "SettingsFileInfo", "DB_Location")
        'Set SQL statements
        sSQL_Table1 = "SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table1.Field INTO Table1 IN '" & LocalDBPath & "' FROM Table1 WHERE (((Table1.Field1)='1') AND ((Table1.Field4)='A' Or (Table1.Field4)='T')) OR (((Table1.Field1)='1' Or (Table1.Field1)='20'));"
        sSQL_Table2 = "SELECT Table2.Field1, Table2.Field2, Table2.Field3, Table2.Field4, Table2.Field5, Table2.Field6, Table2.Field7, Table2.Field8 INTO Table2 IN '" & LocalDBPath & "' FROM Table2 WHERE (((Table2.Field4)=99999) AND ((Table2.Field6)='1' Or (Table2.Field6)='20'));"
        sSQL_Table3 = "SELECT Table3.Field1, Table3.Field2, Table3.Field3, Table3.Field4, Table3.Field5, Table3.Field6, Table3.Field7, Table3.Field8, Table3.Field9, Table3.Field10, Table3.Field11, Table3.Field12 INTO Table3 IN '" & LocalDBPath & "' FROM Table3 WHERE (((Table3.Field1)='1' Or (Table3.Field1)='20') AND ((Table3.Field12)=0 Or (Table3.Field12)=99999));"
        sSQL_Table4 = "SELECT Table4.Field1, Table4.Field2, Table4.Field3, Table4.Field4, Table4.Field5, Table4.Field6, Table4.Field7, Table4.Field8 INTO Table4 IN '" & LocalDBPath & "' FROM Table4 WHERE (((Table4.Field8)='1' Or (Table4.Field8)='20'));"
        'Open Local DB and Cycle Through Table Definitions and Delete as Needed
        Set DB = OpenDatabase(LocalDBPath, False, False)
        For Each tblDef In DB.TableDefs
            Select Case tblDef.Name
                Case "Table1`"
                    DB.TableDefs.Delete ("Table1")
                Case "Table2"
                    DB.TableDefs.Delete ("Table2")
                Case "Table3"
                    DB.TableDefs.Delete ("Table3")
                Case "Table4"
                    DB.TableDefs.Delete ("Table4")
            End Select
        Next tblDef
        DB.Close
        'Open Networked Database and Process SQL Statements To Refresh Table Data in Local DB File
        Set DB2 = OpenDatabase(DBPath, True, False)
        With DB2
            .Execute sSQL_Table1
            .Execute sSQL_Table2
            .Execute sSQL_Table3
            .Execute sSQL_Table4
        End With
        DB2.Close
        Set DB = Nothing
        Set DB2 = Nothing
End Sub

Second Sub (work-around):

Sub gUpdateDBTables()
    Dim DBPath As String
    Dim sSQL_Table1 As String
    Dim sSQL_Table2 As String
    Dim sSQL_Table3 As String
    Dim sSQL_Table4 As String
    Dim delTbl1 As Boolean
    Dim delTbl2 As Boolean
    Dim delTbl3 As Boolean
    Dim delTbl4 As Boolean
    Dim DB As DAO.Database
    Dim DB2 As DAO.Database
    Dim tblDef As DAO.TableDef
        'Get DB File Locations From Registry
        LocalDBPath = Interaction.GetSetting("Tools", "SettingsFileInfo", "Local_DB_Location")
        DBPath = Interaction.GetSetting("Tools", "SettingsFileInfo", "DB_Location")
        'Set SQL statements
        sSQL_Table1 = "SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table1.Field INTO Table1 IN '" & LocalDBPath & "' FROM Table1 WHERE (((Table1.Field1)='1') AND ((Table1.Field4)='A' Or (Table1.Field4)='T')) OR (((Table1.Field1)='1' Or (Table1.Field1)='20'));"
        sSQL_Table2 = "SELECT Table2.Field1, Table2.Field2, Table2.Field3, Table2.Field4, Table2.Field5, Table2.Field6, Table2.Field7, Table2.Field8 INTO Table2 IN '" & LocalDBPath & "' FROM Table2 WHERE (((Table2.Field4)=99999) AND ((Table2.Field6)='1' Or (Table2.Field6)='20'));"
        sSQL_Table3 = "SELECT Table3.Field1, Table3.Field2, Table3.Field3, Table3.Field4, Table3.Field5, Table3.Field6, Table3.Field7, Table3.Field8, Table3.Field9, Table3.Field10, Table3.Field11, Table3.Field12 INTO Table3 IN '" & LocalDBPath & "' FROM Table3 WHERE (((Table3.Field1)='1' Or (Table3.Field1)='20') AND ((Table3.Field12)=0 Or (Table3.Field12)=99999));"
        sSQL_Table4 = "SELECT Table4.Field1, Table4.Field2, Table4.Field3, Table4.Field4, Table4.Field5, Table4.Field6, Table4.Field7, Table4.Field8 INTO Table4 IN '" & LocalDBPath & "' FROM Table4 WHERE (((Table4.Field8)='1' Or (Table4.Field8)='20'));"
        'Open Local DB and Cycle Through Table Definitions and Delete as Needed
        Set DB = OpenDatabase(LocalDBPath, False, False)
        For Each tblDef In DB.TableDefs
            If tblDef.Name = "Table1" Then delTbl1 = True
            If tblDef.Name = "Table2" Then delTbl2 = True
            If tblDef.Name = "Table3" Then delTbl3 = True
            If tblDef.Name = "Table4" Then delTbl4 = True
        Next tblDef
        If delTbl1 = True Then DB.TableDefs.Delete ("Table1")
        If delTbl2 = True Then DB.TableDefs.Delete ("Table2")
        If delTbl3 = True Then DB.TableDefs.Delete ("Table3")
        If delTbl4 = True Then DB.TableDefs.Delete ("Table4")
        DB.Close
        'Open Networked Database and Process SQL Statements To Refresh Table Data in Local DB File
        Set DB2 = OpenDatabase(DBPath, True, False)
        With DB2
            .Execute sSQL_Table1
            .Execute sSQL_Table2
            .Execute sSQL_Table3
            .Execute sSQL_Table4
        End With
        DB2.Close
        Set DB = Nothing
        Set DB2 = Nothing
End Sub

Answer

You often get odd results when looping over a collection and deleting/adding items. Your second approach avoids that, as would looping backwards numerically through the collection e.g.

For x = DB.TableDefs.Count to 1 Step -1
    'Check DB.TableDefs(x)
Next x

Alternatively just try to delete each definition and ignore any errors:

Dim arrTD, td
arrTD = Array("Table1","Table2","Table3","Table4")
For Each td in arrTD
    On Error Resume Next
    DB.TableDefs.Delete td
    On Error Goto 0
Next td

Advertisement

Answer