Member Avatar for 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
  • 2 Contributors
  • 2 Replies
  • 388 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by eshajoshi

Dani AI

Generated

A quick diagnosis and a practical fix.

The runtime error at the highlighted line means the code is asking the TableDefs collection for an index that no longer exists. Two likely causes visible in the posted code are (1) mismatched indexing — For i = 0 To List1.ListCount - 1 then Set tdf = db.TableDefs(i) assumes the List control indexes line up with the TableDefs collection, which they generally do not — and (2) a control/typo problem where the loop over List2 uses List3 to build the path (that can open the wrong file or nothing at all). Another common cause is deleting items while iterating forward through a collection: removing entries shrinks the collection and makes later indexes invalid.

Recommended approach (robust and simple)

  • Treat table names as names, not numeric indexes. For each source table name, open the target database and attempt to delete that table by name. Deleting by name avoids index-out-of-range problems and is fast.
  • Open each target database once, perform deletions, then close it.
  • Skip system/hidden objects (MSys* or tables with the system/hidden attributes) and handle linked tables/relationships which can block deletion.

Example helper (safe, minimal)

' Delete a table if it exists (ignores "not found" errors)
Sub DeleteTableIfExists(db As DAO.Database, tblName As String)
    On Error Resume Next
    db.TableDefs.Delete tblName
    Err.Clear
    On Error GoTo 0
End Sub

If iterating TableDefs directly is still desired, iterate backward and check attributes:

For j = db2.TableDefs.Count - 1 To 0 Step -1
    Set tdf2 = db2.TableDefs(j)
    If (tdf2.Attributes And dbSystemObject) = 0 Then
        If tdf2.Name = srcName Then db2.TableDefs.Delete tdf2.Name
    End If
Next j

Troubleshooting notes: verify List control contents with Debug.Print before using them, fix the List2/List3 typo, confirm OpenDatabase succeeds, and close DAO objects. As noted, dropping or emptying tables is an option — in Access/Jet there is no TRUNCATE command; use a DELETE FROM statement if preserving schema is preferred.

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.