Access Database

[badgers]

I want to find the names of all the tables a database contains. The following code gives me the names of the tables and a whole lot of other info that I don't want. Can anyone tell me how to get the table names out of a database? The database is called test.mdb and it has two tables one called "Table One" the other is called "Electrical"

Dim DB As Database
Private Sub Command1_Click()
Set DB = opendatabase("c:temp\customers.mdb")
Dim i As Integer
For i = 0 To DB.TableDefs.Count - 1
Debug.Print DB.TableDefs(i).Name
Next i
DB.Close
Set DB = Nothing
End Sub
This code gives me the following:
Electrical
MSysACEs
MSysModules
MSysModules2
MSysObjects
MSysQueries
MSysRelationships
Table One
The trouble is that if I use a different database it is a different layout. A database with three tables gives me this:
MSysACEs
MSysModules
MSysModules2
MSysObjects
MSysQueries
MSysRelationships
Names
Stock
Transactions
here the names of the tables all come at the end.
There has to be a way to get this info out of access. :D
thank you for your time and have a good day



------------------
warning do not "upgrade" from girlfriend V1.0 to Wife V1.0.

[MartinLiss]

Sure

For i = 0 To DB.TableDefs.Count - 1
If Left(DB.TableDefs(i).Name, 4) <> "MSys" then

Debug.Print DB.TableDefs(i).Name
End If
Next i


------------------
Marty

[badgers]

thanks for the reply. I had thought of that also but I am afraid that somewhere down the line someone will use those first four characters and then my boss will have my butt. :D(hope I did not offend anyone)
Your way will work, but I would think that there has to be a more intuitive way.
thank you Marty for you time and have a good day

[JHausmann]

You could always check the .attributes value. Any table you add directly to Access is going to be set to at least 0 (two system tables have a value of 2, which isn't a value any of your tables are likely to have, the rest are negative values. If you have the time/patience, you can look into TableDefAttributeEnum which is used to derive the value...).

The following conditional should do the same as Marty's code:

if db.tabledefs(i).attributes=0 or db.tabledefs(i).attributes > 2 then
debug.print db.tabledefs(i).name
end if

[badgers]

thank you very much for your help :D