Cycling Through Tables

[rizd999]

Hello everyone,

I'm trying to fill an array with all the tablenames of an Access database meeting a certain criteria (includes a certain field) in VB6 so that it can be used at a later time. Currently it's programmed as so...

Dim DB as DAO.Database
Dim tdfLoop as TableDef

Set DB=OpenDatabase(SomeAccessDB.mdb)
intTableTotal=DB.TableDefs.Count        'total number of tables on database
intTableCount=0
ReDim MyTableList(intTableTotal-1)

For Each tdfLoop In DB.TableDefs
     if tdfLoop.Fields(0).Name="KeyID" then
          MyTableList(intTableCount)=tdfLoop.Name
          intTableCount=intTableCount+1
     End if
Next

However, when this code is run, it goes through the first cycle but on 'Next', it returns a Run Time Error '3265' - 'Item not found in this collection.' I know it is seeing all of the tables in DB as intTableTotal returns the correct number of tables on the database. I'm not sure what I'm doing wrong here. I do know when this error occurs, it is still on the Access system tables and it still hasn't reached any of the actual tables in the database. Does anyone have any insight?? TIA.

-Edit
Just wanted to include the codeline how I got intTableTotal.

 

[Jon K]

Try excluding the system tables as some of them don't have a Fields collection.

For Each tdfLoop In DB.TableDefs
   [b]If Left(tdfLoop.Name, 4) <> "MSys" Then[/b]
      if tdfLoop.Fields(0).Name="KeyID" then
          MyTableList(intTableCount)=tdfLoop.Name
          intTableCount=intTableCount+1
      End if
   [b]End If[/b]
Next

 

[rizd999]

Hey JonK,

You were right on!! Thanks for the excellent suggestion!!