{"id":888,"date":"2013-12-07T11:12:08","date_gmt":"2013-12-07T16:12:08","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=888"},"modified":"2016-01-01T18:12:58","modified_gmt":"2016-01-01T23:12:58","slug":"sort-by-descriptions-is-gone-in-access-2007-and-up-but","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=888","title":{"rendered":"Sort by Descriptions is gone in Access 2007 and up but…"},"content":{"rendered":"

…you can see them, thanks to a post by JoelS_999 in the Microsoft Community Forums<\/a>.\u00a0 And, while this may not be exactly what you want, this neat little piece of code will allow you\u00a0to see them in a table, tblSysObjProps<\/strong>.\u00a0 This is especially helpful\u00a0when upgrading from Access 2003 and below, where you have used Descriptions to store information pertinent to your Object.<\/p>\n

\r\nPublic Function ListObjProps()\r\n' Created by Joel S. on 12\/05\/2013\r\n'\r\n' This function lists MS Access 2010 objects (i.e. tables, queries, forms, reports,\r\n' etc.) and their top-level properties as shown in the navigation pane. The output\r\n' is written into a table, which allows an open, grid-like view of the object\r\n' properties, similar to the navigation pane in Access 2000-2003.\u00a0 Also, the output\r\n' table makes it possible to sort on the Description field, which the Access 2010\r\n' navigation pane does not allow.\r\n'\r\n' CAVEATS: The DateCreated and LastUpdated fields will reflect the values stored\r\n' in the MSysObjects system table, not the values shown in the navigation pane\r\n' (which sometimes appear to be incorrect).\u00a0 Also, the Type field will show "Tables" not\r\n' "Queries" for query objects, so this could cause confusion if some of the tables\r\n' and queries in the database use exactly the same names.\u00a0 Finally, this function should\r\n' be run whenever the user deems he\/she has added\/deleted\/modified enough\r\n' MS Access objects to warrant refreshing the output table.\r\n\r\nOn Error GoTo Err_ListObjProps\r\n\r\nDim db As Database 'Variable for current database\r\nDim ctnr As Container 'High-level containers for MS Access objects\r\nDim obj As Document 'Individual MS Access Objects--tables, queries, forms, etc.\r\nDim prop As DAO.Property 'Object properties\r\nDim tdf As TableDef 'Output table definition\r\nDim strTbl As String 'Output table name\r\nDim rst As Recordset 'Recordset for updating output table\r\nDim boolRstOpen As Boolean 'Flag to indicate whether recordset is open\r\n\r\n'Turn off warnings\r\nDoCmd.SetWarnings False\r\nDoCmd.Hourglass True\r\n\r\nboolRstOpen = False 'Initiate flag to indicate recordset hasn't been opened.\r\nSet db = CurrentDb()\r\nstrTbl = "tblSysObjProps" 'Name output table. (Can be renamed if user wishes.)\r\n\r\nOn Error Resume Next\r\n'Close and delete previous output table if needed\r\nDoCmd.Close acTable, strTbl, acSaveNo\r\nDoCmd.DeleteObject acTable, strTbl\r\nOn Error GoTo Err_ListObjProps\r\n\r\n'Create new output table definition\r\nSet tdf = db.CreateTableDef(strTbl)\r\nWith tdf\r\n\u00a0 .Fields.Append .CreateField("Name", dbText)\r\n\u00a0 .Fields.Append .CreateField("Type", dbText)\r\n\u00a0 .Fields.Append .CreateField("Description", dbText)\r\n\u00a0 .Fields.Append .CreateField("DateCreated", dbDate)\r\n\u00a0 .Fields.Append .CreateField("LastUpdated", dbDate)\r\nEnd With\r\n'Append new output table definition to database\r\ndb.TableDefs.Append tdf\r\n\r\n'Open output table for updating\r\nSet rst = db.OpenRecordset(tdf.Name)\r\nboolRstOpen = True\r\nIf (rst.RecordCount > 0) Then\r\n\u00a0\u00a0\u00a0 rst.MoveLast\r\nEnd If\r\n\r\n'Cycle through all container types--tables, queries, forms, reports, etc.\r\nFor Each ctnr In db.Containers\r\n\u00a0 'Cycle through each object in the container--individual tables, queries, etc.\r\n\u00a0 For Each obj In ctnr.Documents\r\n\u00a0\u00a0\u00a0 'Create new record in output table\r\n\u00a0\u00a0\u00a0 rst.AddNew\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 obj.Properties.Refresh\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 'Print object name to immediate window if needed\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 'Debug.Print obj.Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 'Cycle through each top-level property for the object in question\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 'and write the value for each specified property into output table.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 For Each prop In obj.Properties\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select Case prop.Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case "Container"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rst!Type = prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case "DateCreated"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rst!DateCreated = prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case "Description"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rst!Description = prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case "LastUpdated"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rst!LastUpdated = prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case "Name"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rst!Name = prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Select\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Print object properties to immediate window if needed\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Debug.Print vbTab & prop.Name & " = " & prop.Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Next prop\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 'Update output table for last record entered.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 rst.Update\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 rst.MoveLast\r\n\u00a0 Next obj\r\nNext ctnr\r\n\r\n'Clean up and close function\r\nIf boolRstOpen Then\r\n\u00a0\u00a0\u00a0 rst.Close\r\nEnd If\r\n'MS Access quirk requires the hidden output table to be explicitly hidden then unhidden.\r\nApplication.SetHiddenAttribute acTable, strTbl, True\r\nApplication.SetHiddenAttribute acTable, strTbl, False\r\ndb.Close\r\nDoCmd.Hourglass False\r\nDoCmd.SetWarnings True\r\nMsgBox ("ListObjProps function completed successfully. See table " _\r\n\u00a0 & strTbl & " for output.")\r\nExit Function\r\n\r\nErr_ListObjProps:\r\n\u00a0 'Clean up and close function\r\n\u00a0 On Error Resume Next\r\n\u00a0 If boolRstOpen Then\r\n\u00a0\u00a0\u00a0 rst.Close\r\n\u00a0 End If\r\n\u00a0 db.Close\r\n\u00a0 DoCmd.Hourglass False\r\n\u00a0 DoCmd.SetWarnings True\r\n\u00a0 MsgBox ("ERROR: ListObjProps function did not complete successfully.")\r\n\u00a0\r\nEnd Function\r\n<\/pre>\n
Still missing the Database Window?\u00a0 Here are some options…
\nAAD Consulting’s Database Window for Access 2007 and Access 2010<\/a>
\nAvenius Gunter’s Database Window for Access 2007, Access 2010 and Access 2013<\/a>
\nAccess Junkie’s Database Window for Access 2007<\/a><\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
…you can see them, thanks to a post by JoelS_999 in the Microsoft Community Forums. And, while this may not be exactly what you want, this neat little piece of code will allow you to see them in a table, tblSysObjProps. This is especially helpful when upgrading from Access 2003 and below, where you have […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[48,49,21],"class_list":["post-888","post","type-post","status-publish","format-standard","hentry","category-access-tips","tag-database-window","tag-navigation-pane","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/888","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=888"}],"version-history":[{"count":9,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/888\/revisions"}],"predecessor-version":[{"id":1163,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/888\/revisions\/1163"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}