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 Counter

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

End Sub
4 Years
Discussion Span
Last Post by eshajoshi

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.


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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.