Table Collection

[Guest]

Want to cycle through all the tables in a database using:

Dim MyTable as TableDef
For i=1 to tables.count
or
For each MyTable in Tables

Can't seem to do it. What is the collection of tables called? Do I need to
set an additional reference to get to the tables collection?

If this can't be done, should I create a datasheet form for each table and
cycle through those? Do they have to be open for that?

Thanks in advance.

 

[Guest]

Dim tdf As TableDef

For each tdf in Currentdb.TableDefs
Debug.Print tdf.Name
Next

The Debug.Print is just an example. This is where you would do whatever you
want with the table.

 

[Douglas J. Steele]

And just for the sake of completeness, you could also use

Dim intLoop As Integer
Dim tdf As TableDef

For intLoop = 0 To (CurrentDb.TableDefs.Count - 1)
Set tdf = CurrentDb.TableDefs(intLoop)
Debug.Print tdf.Name
Next intLoop

 

[Guest]

Yes. That would be the second best way to do it
(actually, Doug, I don't know if one is preferred and if so, why)

 

[Douglas J. Steele]

The only reason I ever use the Count approach is when the order of operation
matters (such as when you want to delete all the members, so that you need
to go backwards from .Count - 1 To 0 Step -1.

 

[Dirk Goldgar]

In

And just for the sake of completeness, you could also use

Dim intLoop As Integer
Dim tdf As TableDef

For intLoop = 0 To (CurrentDb.TableDefs.Count - 1)
Set tdf = CurrentDb.TableDefs(intLoop)
Debug.Print tdf.Name
Next intLoop

Do you really want to call CurrentDb inside that loop? How about:

With CurrentDb
For intLoop = 0 To (.TableDefs.Count - 1)
Set tdf = .TableDefs(intLoop)
Debug.Print tdf.Name
Next intLoop
End With

Or even:

With CurrentDb.TableDefs
For intLoop = 0 To (.Count - 1)
Set tdf = .Item(intLoop)
Debug.Print tdf.Name
Next intLoop
End With

Or:

With CurrentDb.TableDefs
For intLoop = 0 To (.Count - 1)
Debug.Print .Item(intLoop).Name
Next intLoop
End With

 

[Douglas J. Steele]

In

Do you really want to call CurrentDb inside that loop? How about:

With CurrentDb
For intLoop = 0 To (.TableDefs.Count - 1)
Set tdf = .TableDefs(intLoop)
Debug.Print tdf.Name
Next intLoop
End With

Or even:

With CurrentDb.TableDefs
For intLoop = 0 To (.Count - 1)
Set tdf = .Item(intLoop)
Debug.Print tdf.Name
Next intLoop
End With

Or:

With CurrentDb.TableDefs
For intLoop = 0 To (.Count - 1)
Debug.Print .Item(intLoop).Name
Next intLoop
End With

As usual, you're right, Dirk.

 

[David W. Fenton]

And just for the sake of completeness, you could also use

Dim intLoop As Integer
Dim tdf As TableDef

For intLoop = 0 To (CurrentDb.TableDefs.Count - 1)
Set tdf = CurrentDb.TableDefs(intLoop)
Debug.Print tdf.Name
Next intLoop

I don't understand why anyone would use a counter for an index
number, except when you want to traverse a collection in something
other than its natural order.

 

[David W. Fenton]

As usual, you're right, Dirk.

The original suggested solution is much better, seems to me:

Dim tdf As TableDef

For Each tdf in Currentdb.TableDefs
Debug.Print tdf.Name
Next tdf

I would add after the end of the loop:

Set tdf = Nothing

just in case there's an unreleased implicit reference hanging
around.

Seems to me that the other solutions are only relevant when, say,
you want to traverse the collection in reverse.

 

[Dirk Goldgar]

The original suggested solution is much better, seems to me:

Dim tdf As TableDef

For Each tdf in Currentdb.TableDefs
Debug.Print tdf.Name
Next tdf

I would add after the end of the loop:

Set tdf = Nothing

just in case there's an unreleased implicit reference hanging
around.

Seems to me that the other solutions are only relevant when, say,
you want to traverse the collection in reverse.

And I'd agree, though I don't believe you need to explicitly set tdf to
Nothing.