Table Names and VBA 2

[TidyTrax]

Is there a piece of code that i can use that will return a list of all the tables in my database???

 

[Zathras]

Try this query:
[blue]

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) and (MSysObjects.Name not like 'MSys*'));

[/color]

 

[TonyJollans]

Hi TidyTrax,

While Zathras' code will certainly work it relies on undocumented internals of Access and it is generally better to use the defined User Interface:

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef

Set tjDb = CurrentDb

For Each tjTab In tjDb.TableDefs
    If (tjTab.Attributes And dbSystemObject) = 0 Then
        MsgBox tjTab.Name

' Or add to list or whatever else you want to do

    End If
Next

tjDb.Close

Set tjTab = Nothing
Set tjDb = Nothing

Enjoy,
Tony

 

[Zathras]

Not documented true, but not really hidden, either.

If you want to view the underlying table (MSysObjects), use the menu to pick Tools/Options, select the "View" tab and check the box that says "System Objects". You can then open the MSysObjects table like any other. The codes in the Type column may not be documented, but it is rather obvious that your user tables are type=1.

Nevertheless, if I were going to populate a list of table names, I would use Tony's technique (now that I know it -- thanks Tony ).