For Each...Next not looping over entire TableDef collection?

[Banana]

I use a modified version of Doug Steele's code for creating a DSN-less connection. I've added a stored procedure which returns a list of tables that can be linked to which is used as a recordset.

I make sure that there are actually tables in the list then proceed to delete all linked tables (only one ODBC source, so that's fine with me), but this doesn't complete the job properly, skipping some tables.

My code:

With rst
    If Not .BOF And Not .EOF Then
        For Each tdfCurrent In dbCurrent.TableDefs
            If Not Len(tdfCurrent.Connect) = 0 Then
                dbCurrent.TableDefs.Delete (tdfCurrent.Name)
            End If
        Next
        
        dbCurrent.TableDefs.Refresh
        
        .MoveFirst
        Do Until .EOF
            Set tdfCurrent = dbCurrent.CreateTableDef(.Fields(0).Value)
            tdfCurrent.Connect = strConnection
            tdfCurrent.SourceTableName = .Fields(0).Value
            dbCurrent.TableDefs.Append tdfCurrent
            GenerateIndexSQL (tdfCurrent.Name)
            .MoveNext
        Loop
    End If
End With

I just stepped through the code, and at one point, I had 8 tables total, 6 system tables, and two linked tables that should be deleted (and meets the criteria for doing so). If I play the code up to the line .MoveFirst, it deletes only one table, leaving me with still one linked table. Repeating the loop, it delete that last linked table, so code is essentially correct, but for some reasons it just doesn't does it. As a consequence, I will get an error when I try to append a new table because same table will still be there when it should have been deleted.

I'd like to say that it's because my computer is too fast, but that quite doesn't make sense. Or maybe I'm missing something obvious?

TIA.

 

[DJkarl]

Instead of using a variable dbCurrent, can you use CurrentDb instead? I have a feeling that the database object in memory is mucking things up.

 

[Banana]

Hmm, will try that when I get back to the work computer.

I did initially wondered why Doug used dbCurrent (which he set to DBEngine(0)(0)) but figured he had a good reason for doing so. Also, I should note that there are actually two database variable, one being the current database and another from OpenDatabase method to execute the stored procedure to get that recordset.

 

[Banana]

Just to report back-

CurrentDb did indeed fixed the problem.

The odd thing, though, was that if I replaced all dbCurrent to CurrentDb, the deleting part of code executes just fine, but appending part no longer works; after it is appended to the tabledefs, VBA chokes on the line:

GenerateIndexSQL (tdfCurrent.Name)

saying that the object variable is no longer set.

For now, I replaced CurrentDb with dbCurrent in the appending section and everything works just peachy. Not most optimized or effective as I'm referencing two variables to same object, but have others to do before I can investigate this deeper.

 

[dwhitman]

For Each may not work right if deleting items

Seems like Banana has this running ok, but at one point he notes that that first For Each loop wasn't deleting all the linked tables.

This looks very similar to a problem I just had deleting relations by cycling through a collection with a For Each loop, discussed in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=154268

Pat Hartman gave a great explanation of how deleting items in a For Each loop seems to mess up the loop counter so that not all items in the collection get examined.

Pat suggested avoiding the problem by cycling BACKWARDS through the collection with a regular For loop, not a For Each loop. I gave a code example in the cited thread that worked for me.

 

[Banana]

Thanks for that reference.

It is a bit disappointing to learn that For Each...Next isn't as reliable (though in case of referencing CurrentDb, it works nicely) because it's supposed to be faster than For...Next.

Will definitely keep that in mind!

 

[gemma-the-husky]

And I just had exactly this problem today, and I knew I had seen a discussion on it before.