vb6 Deleting table(s) from database

[eshajoshi]

Hi,

I have two databases. In my code, I open the first database and list all of its databases.

With my code, I open the second database. Essentially I want to override the tables in database2 with the tables in database1 so I am trying to delete the tables in database2 that have the same name as the tables in database1.

When I try to do this, I have the error: "Item not found in this collection". And the error highlights the line below with the stars next to it.

Can someone please help? Here is the code:

Private Sub Command4_Click()
    ' Copy Tables from List1 to Database(s) in List2

    Dim db As DAO.Database
    Dim db2 As DAO.Database
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef

    Set db = OpenDatabase(Text1.text)

    ' Opening the selected database(s) in List2
    For i = 0 To List1.ListCount - 1
        Set tdf = db.TableDefs(i)

        For Counter = 0 To List2.ListCount - 1
            Text3.text = List3.List(Counter)
            Set db2 = OpenDatabase(Text3.text)

            For j = 0 To db2.TableDefs.Count - 1
                **Set tdf2 = db2.TableDefs(j)**

                ' Deleting the tables (listed in List1) from the database(s) in List2
                If tdf2.Name = tdf.Name Then
                    db2.TableDefs.Delete (tdf2.Name)
                End If
            Next
        Next Counter
    Next


'        For j = 0 To db2.TableDefs.Count - 1
'            Print db2.TableDefs(j).Name
'        Next

End Sub

[Reverend Jim]

Hard to say without knowing your database definitions, what your DBMS is and looking at this app in the debugger to get variable values, but I have two alternate suggestions:

1. Delete and recreate the database tables you want to override. The SQL query to do delete a table is

   DROP TABLE tablename

2. Delete all the data in the tables you want to override. The SQL query to do this is

   TRUNCATE TABLE tablename

I suspect it would be better to TRUNCATE rather than DROP.

[eshajoshi]

Hi Revered Jim,

Thanks for your response and sorry for the late reply. I was looking more into this error and it seems that my program is telling me that it cannot access the tables in the database (this comes from the command where I keep getting the error) because the tables in the database do not access.

I have done testing and it does appear that tables do exist so would dropping or truncating a table help. I will try that though.