Table Collection

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.

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.
--
Dave Hargis, Microsoft Access MVP


"Max" wrote:

> 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.

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
news:E204F989-52BE-40CE-8D30-07AB949B1A87@microsoft.com...
> 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.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Max" wrote:
>
>> 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.

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)
--
Dave Hargis, Microsoft Access MVP


"Douglas J. Steele" wrote:

> 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
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
> news:E204F989-52BE-40CE-8D30-07AB949B1A87@microsoft.com...
> > 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.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Max" wrote:
> >
> >> 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.
>
>
>

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
news:098E42EC-7979-43C5-89F5-CBD45A70C1A0@microsoft.com...
> 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)
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Douglas J. Steele" wrote:
>
>> 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
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
>> news:E204F989-52BE-40CE-8D30-07AB949B1A87@microsoft.com...
>> > 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.
>> > --
>> > Dave Hargis, Microsoft Access MVP
>> >
>> >
>> > "Max" wrote:
>> >
>> >> 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.
>>
>>
>>

In news:e2oXxiT1HHA.4184@TK2MSFTNGP06.phx.gbl,
Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> 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


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:usxvo6V1HHA.1124@TK2MSFTNGP06.phx.gbl...
> In news:e2oXxiT1HHA.4184@TK2MSFTNGP06.phx.gbl,
> Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>> 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

As usual, you're right, Dirk.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:e2oXxiT1HHA.4184@TK2MSFTNGP06.phx.gbl:

> 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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:eWP6$hW1HHA.5360@TK2MSFTNGP03.phx.gbl:

> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
> news:usxvo6V1HHA.1124@TK2MSFTNGP06.phx.gbl...
>> In news:e2oXxiT1HHA.4184@TK2MSFTNGP06.phx.gbl,
>> Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>>> 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
>
> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)