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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Quick Question- How do you list all tables in … (Access)

    Author
    Topic
    #384831

    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!

    Viewing 1 reply thread
    Author
    Replies
    • #661572

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

      • #661574

        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 cheers

        • #661600

          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];

          • #662198

            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.

            • #662201

              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.

          • #662371

            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?

            • #662380

              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:

              Object Type
              Table 1
              Linked table 6
              Query 5
              Form -32768
              Report -32764
              Macro -32766
              Module -32761
              Data Access Page -32756
            • #662436

              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.

            • #662553

              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)

            • #662567

              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

            • #662570

              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 MarkLiquorman. 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.

            • #662582

              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

            • #662600

              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 [Object Name], daoGetObjDescription([Name],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

            • #1088566

              Hi Mark

              How would I change the function to return the description for all object types?

              Thanks, John

              SELECT MSysObjects.Name AS [Object Name], daoGetObjDescription([Name]) AS Description, MSysObjects.Type
              FROM MSysObjects
              ORDER BY MSysObjects.Name;
              
            • #1088601

              Do you want to change the function (which already works for all object types) or the query (which you have already modified)? confused

            • #1088682

              At this point I think I would want to clone the existing function and make any necessary query adjustments or is there an easier way?

              Thanks, John

            • #1088689

              The existing function should work with the modified query you posted. Doesn’t it work the way you want?

            • #1088692

              My query contains all Object Types, tables, queries, forms, reports, ect

              When I use

              SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription([Name]) AS Description
              FROM MSysObjects
              ORDER BY MSysObjects.Type, MSysObjects.Name;
              

              I Get Wrong number of arguments used with function in query expression daoGetObjDescription([Name])

              daoGetObjDescription([Name]) is looking for the Object Type to return the description

              John

            • #1088695

              Try

              SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription(MSysObjects.Name, MSysObjects.Type) AS Description
              FROM …

            • #1088710

              This works, thanks for your help

              John

              SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription([Name],
              IIf([type]=-32768,3,
              IIf([type]=-32766,5,
              IIf([type]=-32764,4,
              IIf([type]=-32761,6,
              IIf([type]=1,1,
              IIf([type]=6,1,
              IIf([type]=5,2,2)))))))) AS Description
              FROM MSysObjects
              WHERE (((MSysObjects.Type)=-32768)) 
              OR (((MSysObjects.Type)=-32766)) 
              OR (((MSysObjects.Type)=-32764)) 
              OR (((MSysObjects.Type)=-32761)) 
              OR (((MSysObjects.Type)=1)) 
              OR (((MSysObjects.Type)=6)) 
              OR (((MSysObjects.Type)=5))
              ORDER BY MSysObjects.Type, MSysObjects.Name;
              
            • #662601

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

    • #1089513

      A lot of methods posted. Personally, I prefer the ADO method (which will work for tables and queries):

      Function GetDBTables()
      On Error GoTo ErrorHandler
      Dim cnn As ADODB.Connection
      Dim rs As ADODB.Recordset
      If DBConnect(cnn) Then
      Set rs = cnn.OpenSchema(adSchemaTables)
      Me.lstTables.Clear
      rs.MoveFirst
      Do Until rs.EOF = True
      If Me.optTables Then
      If rs.Fields(“TABLE_TYPE”) = “TABLE” Then Me.lstTables.AddItem rs.Fields(“TABLE_NAME”)
      Else
      If rs.Fields(“TABLE_TYPE”) = “VIEW” Then Me.lstTables.AddItem rs.Fields(“TABLE_NAME”)
      End If
      rs.MoveNext
      Loop
      rs.Close
      Set rs = Nothing
      cnn.Close
      Set cnn = Nothing
      End If
      Exit Function

      ErrorHandler:
      Err.Clear
      End Function

      This is right out of an application I wrote that builds classes in ASP from tables/queries. It’s displaying them in a listbox. And there are option buttons to display tables, or to display queries. The function DBConnect is essentially:

      Function DBConnect(ByRef cnn as ADODB.Connection)
      set cnn=new ADODB.connection
      cnn.Provider=”Microsoft.Jet.OLEDB.4.0″
      cnn.Open “C:SomePathSomeDatabase.mdb”
      Exit function

      However, you can connect that ‘cnn’, the connection object, to any database you want (Access, SQL Server, Oracle, etc), and it will list the tables for you.

    Viewing 1 reply thread
    Reply To: Quick Question- How do you list all tables in … (Access)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: