Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quick Question- How do you list all tables in ... (Access)

    How do you list all tables in a database? Was going to make a query to do this then it dawned on me i dont know how!

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    nevermind found out how, for future reference...
    hidden table named mysysobjects
    pulled the name from it
    then did a like tbl* for critrea

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    You have got the right table, but your criteria is not entirely correct. You might name them tbl* but not everyone does this.
    What you should do is to put a criteria for Type=1 (local table) or 6 (linked table) then name holds the tablename.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Try this.

    It will return all tables, whether networked or not, and count all entries also.

    SELECT [MsysObjects].[Name], DCount("*",[Name]) AS TableCount
    FROM MsysObjects
    WHERE (((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys") And (([MsysObjects].[Type])=1 Or ([MsysObjects].[Type])=6))
    ORDER BY [MsysObjects].[Name];

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Thanks guys. I was on a one track mind there, lol. Now gota make me somthing for it to refresh it everytime i run my report.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    I assume that you created a selection query and used that as record source of a report. The query will be executed every time you open the report, so you'll always get an up-to-date list.

  7. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Hi Dave,

    How could I use this method to display queries, forms, reports, macros, and modules?

    Also, (sounds dumb), but how can I view these hidden objects?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Hi Bob,

    You can view hidden and/or system objects (this is not the same) by selecting Tools/Options..., view tab. There are separate check boxes for hidden objects and system objects.

    Here is a list of types to use in queries that list objects:
    <table border=1> <td>Object</td> <td>Type</td> <td>Table</td> <td>1</td> <td>Linked table</td> <td>6</td> <td>Query</td> <td>5</td> <td>Form</td> <td>-32768</td> <td>Report</td> <td>-32764</td> <td>Macro</td> <td>-32766</td> <td>Module</td> <td>-32761</td> <td>Data Access Page</td> <td>-32756</td> </table>

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    More trivial nitpicking: Type 6 = ISAM Linked tables, while Type 4 = ODBC Linked Tables. Type 5, Queries, includes SQL statements used as RecordSource or RowSource properties (included in MSysObjects for optimization purposes). These can be identified by Flags field = 3 when querying MSysObjects table for Type = 5, and thus be excluded by specifying that Flags <> 3 in query criteria. Since System tables are officially undocumented, the usual caveats apply, tho AFAIK the "Type" numbers used to identify database objects work same in A97, A2K, and AXP.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Thanks, Mark. The extra condition for queries is especially useful.

    (Final nit in my own table: the last entry, Data Access Pages, is not valid for Access 97, only for Access 2000 and 2002/XP, since Access 97 didn't have those)

  11. #11
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Thank you, Hans. Is there another function that displays the DESCRIPTION for the tables, queries, etc? I include a description with all of my objects, and think it would be nice to have the ability to print a TOC of my objects and their descriptions.

    Bob in Indy

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    Hans,

    Thanks for the reply. I will check out the link, and also the Documentor. My apologies to anyone whom I may have offended. I've removed that sentence.

    Regards,

    Bob in Indy

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    I don't think you can get the descriptions from the MSysObjects table. You will have to loop through all objects in the collection of tables (or queries, ...)

    You can do this with DAO, or with the AllTables etc. collections available in Access 2000 and higher. If you want to get an idea of how to do this, take a look at the Documenter database from Access moderator <!profile=MarkLiquorman>MarkLiquorman<!/profile>. You will find a link to his web site in his profile.

    But it is going to be a lot of work. Can you use Tools/Analyze/Documentation instead? Study the various options to see if it will work for you.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Quick Question- How do you list all tables in ... (Access)

    In short, you examine the properties of the objects. Description is not one of the properties in intellisense, so you address it like <object>.Properties("description"), where <object> represents an object variable like a tabledef.
    Charlotte

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Quick Question- How do you list all tables in ... (Access)

    If interested this function use DAO methods to get Description property (if any) for specified database object. This function can be used in query based on MSysObjects table to list Description for each object type specified in query. Example:

    Option Compare Database
    Option Explicit

    Enum DbObject
    Table = 1
    Query = 2
    Form = 3
    Report = 4
    Macro = 5
    Module = 6
    End Enum

    Public Function daoGetObjDescription(strObjName As String, intObjType As DbObject) As String
    On Error GoTo Err_Handler

    ' intObjType: see DbObject Enum for valid arg values

    Dim db As DAO.Database
    Dim doc As DAO.Document
    Dim strContainer As String
    Dim strMsg As String

    Set db = CurrentDb

    Select Case intObjType
    Case 1, 2
    strContainer = "Tables"
    Case 3
    strContainer = "Forms"
    Case 4
    strContainer = "Reports"
    Case 5
    strContainer = "Scripts"
    Case 6
    strContainer = "Modules"
    End Select

    db.Containers(strContainer).Documents.Refresh
    Set doc = db.Containers(strContainer).Documents(strObjName)
    daoGetObjDescription = doc.Properties("Description")

    Exit_Sub:
    Set db = Nothing
    Set doc = Nothing
    Exit Function
    Err_Handler:
    Select Case Err.Number
    Case 3265 ' Item not found in collection
    strMsg = "Invalid object name - object not found in specified database container."
    MsgBox strMsg, vbExclamation, "OBJECT NOT FOUND"
    Resume Exit_Sub
    Case 3270 ' Property not found:
    daoGetObjDescription = ""
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "GET OBJECT DESCRIPTION ERROR"
    Resume Exit_Sub
    End Select

    End Function

    Example of use in query (list all forms in current db):

    SELECT MSysObjects.Name AS <!t>[Object Name]<!/t>, daoGetObjDescription(<!t>[Name]<!/t>,3) AS Description, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=-32768))
    ORDER BY MSysObjects.Name;

    NOTE: This will not exactly be lightning-quick in query, as function runs once for each row returned by query. If this is issue you can always create your own table to store object info, but you'd need a reliable method to keep table updated. If no description has been entered for object, function returns a zero-length string. Also note use of user-defined Enum in Declarations section of sample code, this helps avoid errors by providing "Intellisense" list of valid arguments when using function in VBA - see attd screen shot of use in Immediate window. If using this code, ensure reference to DAO library is set.

    HTH
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •