ASE, SQL, OLE, ADE

Reply
*Adam, Claire
Message 1 of 4 (116 Views)

Access Table Names

116 Views, 3 Replies
10-27-1999 01:36 AM
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
*Poat, Alan
Message 2 of 4 (116 Views)

Re: Access Table Names

10-27-1999 07:58 AM in reply to: *Adam, Claire
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
*Holder, Mark
Message 3 of 4 (116 Views)

Re:

10-27-1999 10:28 AM in reply to: *Adam, Claire
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
*Watts, Steve
Message 4 of 4 (116 Views)

Re:

10-27-1999 04:31 PM in reply to: *Adam, Claire
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
>
>
Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Announcements
Do you have 60 seconds to spare? The Autodesk Community Team is revamping our site ranking system and we want your feedback! Please click here to launch the 5 question survey. As always your input is greatly appreciated.