ASE, SQL, OLE, ADE

[#1]

Dear all,

I want to be able to list all tables within an access data base from visual
basic. I use the commands below:

'list all tables within the database file
Dim tdfNew As TableDef
Dim tdfLoop As TableDef
Dim dbsMyDataBase As Database
Set dbsMyDataBase = OpenDatabase(lblDataBaseName.Caption)
With dbsMyDataBase
' Enumerate TableDefs collection.
For Each tdfLoop In .TableDefs
Debug.Print " " & tdfLoop.Name
Next tdfLoop
.Close
End With

This works well as i get all the tables listed in the data base but I also
get the following listed:
MSysACEs
MSysModules
MSysModules2
MSysObjects
MSysQueries
MSysRelationships
Is there any way of filtering these out or any other unknown tabledef
objects.

thanks
Claire

[#2]

Try this After your for Each statement:

If Left (tdfloop.Name, 4) <> "MSys" Then
Debug.Print " " & tdfLoop.Name
End If
Next tdfLoop

This orks in VB.

Claire Adam wrote:

> Dear all,
>
> I want to be able to list all tables within an access data base from visual
> basic. I use the commands below:
>
> 'list all tables within the database file
> Dim tdfNew As TableDef
> Dim tdfLoop As TableDef
> Dim dbsMyDataBase As Database
> Set dbsMyDataBase = OpenDatabase(lblDataBaseName.Caption)
> With dbsMyDataBase
> ' Enumerate TableDefs collection.
> For Each tdfLoop In .TableDefs
> Debug.Print " " & tdfLoop.Name
> Next tdfLoop
> .Close
> End With
>
> This works well as i get all the tables listed in the data base but I also
> get the following listed:
> MSysACEs
> MSysModules
> MSysModules2
> MSysObjects
> MSysQueries
> MSysRelationships
> Is there any way of filtering these out or any other unknown tabledef
> objects.
>
> thanks
> Claire

--
Alan Poat
DisplayWorks
470 Valley Drive
Brisbane, CA 94005
Phone: 415-467-0198
Fax: 415-467-8371
Email: apoat@displayworks.com
http://www.displayworks.com

[#3]

TableDefs also have an Attributes property, which is a bitmapped value that
indicates not only system tables, but attached tables as well. See the online
docs for TableDef Attributes property.

Mark Holder

Alan Poat wrote:

> Try this After your for Each statement:
>
> If Left (tdfloop.Name, 4) <> "MSys" Then
> Debug.Print " " & tdfLoop.Name
> End If
> Next tdfLoop
>
> This orks in VB.
>
> Claire Adam wrote:
>
> > Dear all,
> >
> > I want to be able to list all tables within an access data base from visual
> > basic. I use the commands below:
> >
> > 'list all tables within the database file
> > Dim tdfNew As TableDef
> > Dim tdfLoop As TableDef
> > Dim dbsMyDataBase As Database
> > Set dbsMyDataBase = OpenDatabase(lblDataBaseName.Caption)
> > With dbsMyDataBase
> > ' Enumerate TableDefs collection.
> > For Each tdfLoop In .TableDefs
> > Debug.Print " " & tdfLoop.Name
> > Next tdfLoop
> > .Close
> > End With
> >
> > This works well as i get all the tables listed in the data base but I also
> > get the following listed:
> > MSysACEs
> > MSysModules
> > MSysModules2
> > MSysObjects
> > MSysQueries
> > MSysRelationships
> > Is there any way of filtering these out or any other unknown tabledef
> > objects.
> >
> > thanks
> > Claire
>
> --
> Alan Poat
> DisplayWorks
> 470 Valley Drive
> Brisbane, CA 94005
> Phone: 415-467-0198
> Fax: 415-467-8371
> Email: apoat@displayworks.com
> http://www.displayworks.com

[#4]

Here is a portion of the code I use to fill a list box with valid table
names:

Dim dbss As Database, tdf As TableDef
Static dbs(127) As String, Entries As Integer

' Return reference to current database.
Set dbss = CurrentDb
Entries = 0
For Each tdf In dbss.TableDefs
' Compare property setting and constant in question.
If (tdf.Attributes And dbSystemObject) Or _
(tdf.Attributes And dbHiddenObject) Then
Else: dbs(Entries) = tdf.Name
Entries = Entries + 1
End If
Next tdf
Set dbss = Nothing

Steve

Alan Poat wrote in message
news:3817132A.E2A11893@displayworks.com...
> Try this After your for Each statement:
>
> If Left (tdfloop.Name, 4) <> "MSys" Then
> Debug.Print " " & tdfLoop.Name
> End If
> Next tdfLoop
>
> This orks in VB.
>
>
> Claire Adam wrote:
>
> > Dear all,
> >
> > I want to be able to list all tables within an access data base from
visual
> > basic. I use the commands below:
> >
> > 'list all tables within the database file
> > Dim tdfNew As TableDef
> > Dim tdfLoop As TableDef
> > Dim dbsMyDataBase As Database
> > Set dbsMyDataBase = OpenDatabase(lblDataBaseName.Caption)
> > With dbsMyDataBase
> > ' Enumerate TableDefs collection.
> > For Each tdfLoop In .TableDefs
> > Debug.Print " " & tdfLoop.Name
> > Next tdfLoop
> > .Close
> > End With
> >
> > This works well as i get all the tables listed in the data base but I
also
> > get the following listed:
> > MSysACEs
> > MSysModules
> > MSysModules2
> > MSysObjects
> > MSysQueries
> > MSysRelationships
> > Is there any way of filtering these out or any other unknown tabledef
> > objects.
> >
> > thanks
> > Claire
>
> --
> Alan Poat
> DisplayWorks
> 470 Valley Drive
> Brisbane, CA 94005
> Phone: 415-467-0198
> Fax: 415-467-8371
> Email: apoat@displayworks.com
> http://www.displayworks.com
>
>