Finding system objects - Access 2K [Resolved]

[salvelinus]

I'm populating a listbox with the names of the tables, queries, forms, etc, in the database. The eventual goal is to be able to make changes in one template database and have a form that will copy the changes to about sixty other db's automatically.
Anyway, I don't want the (normally) hidden system objects to appear in the listbox. I know I could check Left(tbl.Name, 4) for "Msys", but is there a more OOP way, like checking for a system or hidden property?
Also, is there a reason that there's no .AddItem property for a standard listbox?
Thanks.

Dim ObjAO As AccessObject
      Dim Obj As Object
      Dim s As String
           
      Set Obj = Application.CurrentData
      lstObjects.RowSourceType = "Value List"
      
      For Each ObjAO In Obj.AllTables
            If Not ObjAO.blah = vbSystem Then   'This is where I want a check
               s = s & ObjAO.Name & ";"
            End If
      Next
     
      lstObjects.RowSource = s

[dannymking]

To the best of my knowledge I think the only way you can determine the system tables is through left(tablename,4) = "Msys" method that you have mentioned...

Don't have Access 2000 installed, but could of sworn that .Additem was introduced in this version.. it's definately not in Access 97

[salvelinus]

Thanks. There's an .AddItem method for a commandbar listbox in 2000, but apparently not for a toolbox one.
Another odd thing is that you have to use .CurrentData to iterate tables & queries with a For Each statement, but .CurrentProject for forms & reports.

[dannymking]

That'll be because .CurrentData refers to uncodable items such as Tables and Queries.. And .CurrentProject Refers to codable items such as Forms, Reports and modules.

You could also use the Document Property for all objects

[RobDog888]

You can run a query against the system table.

"SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') " & _
"AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;"

This will retrieve all the user Tables in the DB.

[salvelinus]

Thanks both, that's usefull info I didn't know. The MSysObjects table is nice to know.

[salvelinus]

That'll be because .CurrentData refers to uncodable items such as Tables and Queries.. And .CurrentProject Refers to codable items such as Forms, Reports and modules.

You could also use the Document Property for all objects

Well, you'd think there'd be one property that could reference all. Not familiar with the Document property, will look that up Monday.

[dannymking]

Document propert is actually an element of DAO..

Sub GetTableNames()
    Dim Db As DAO.Database
    Dim Doc As DAO.Document
    'Initialize the DAO Database by setting it to the current database
    Set Db = CurrentDb
    'Now loop around the number of tables found within the database, because we are using the documents collection
    'we do not need to set an upper and lower limit
    For Each Doc In Db.Containers!Tables.Documents
        'Display each tablename to the immediate window
        Debug.Print Doc.Name
    Next Doc
    'Close the reference
    Set Db = Nothing
End Sub

Subsitute Tables for Forms, Modules, Reports or Queries

[RobDog888]

Ok, got the rest of the objects...

'Queries:
"SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') " & _
"AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
 
'Reports:
"SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') " & _
"AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;"
 
'Forms:
"SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') " & _
"AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;"
 
'Modules:
"SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') " & _
"AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;"
 
'Macros:
"SELECT MSysObjects.Name FROM MsysObjects WHERE  (Left([name], 1) <>'~') " & _
"AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;"

[salvelinus]

Thanks, but who the heck ever heard of (MSysObjects.Type)= -32766?
It would just be a lot simpler if MS gave this stuff a boolean System property.
But I do appreciate your help. Thanks.

[RobDog888]

Well the Type is Access' constant value for a Form (-32768). If you have ever browsed through the system tables you will see allot of records with field values like this. Its just the way Access does it.

[salvelinus]

I suppose it would be helpful to do that, but it still seems like there ought to be a property to check. If I see a value like 32768 I start thinking of the limit to integer values, 32767.

[RobDog888]

You could also do this by opening the DB Schema via ADOX but it will be more work and code since you will have to make a few calls to the db. One for the tables, then one for its field types. Then repeat for each db object type.

[salvelinus]

Aaaghh! That's my whole point. There ought to be a simple object property to check, like .Visible. MS, grrr...

[RobDog888]

Maybe you could execute the menu items to do a Tools > Anaylze > Documenter with the DoCmd.DoMenuItem ?

[salvelinus]

I'm fine with gritting my teeth & muttering "G-D MS". But thanks.