{"id":1180,"date":"2016-08-07T21:37:14","date_gmt":"2016-08-08T01:37:14","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=1180"},"modified":"2018-07-18T22:35:46","modified_gmt":"2018-07-19T02:35:46","slug":"list-tables-and-fields","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=1180","title":{"rendered":"List Tables and Fields…"},"content":{"rendered":"

When I’m designing a database I usually start off with one of my Templates. But, as with any Template, the Tables therein do not always fit exactly with the Client’s needs. And, since I like to document changes I built this tool…<\/p>\n

\"\"<\/span><\/p>\n

In a nutshell…
\nAfter the Template database is created I import the Objects from the Tables and File List<\/a> database. From there I can go to Design Mode of the Table, the little blue button, and make my changes, as well as, mark what changes I’ve made in the *Comments* column. Then I have a record of my changes and, just in case someone needs a hard copy, output the results to Excel (template included).<\/p>\n

To use…
\nDownload the Table and File List<\/a> zipped file and unzip the contents to a Folder, not your Desktop. Then open the database you want to run this on and import the Objects into your database and place TableFieldList.xlsx<\/strong> into the same Folder as the database you want to use this in. Open frmTableFileList<\/strong> and click the Redo<\/strong> Command Button.<\/p>\n

The blue button to the left opens the Table<\/strong> in Design Mode<\/strong>. (Thanks to Tony Toews’ database<\/a> for giving me the idea to go directly to Design Mode from here instead to dealing with the Navigation Pane.)<\/p>\n

I use the Check Box<\/strong> to indicate I cam done. Once checked the record (row) will turn light grey (helps me stay focused).<\/p>\n

Blue boxes at the top are for filtering. Once making a selection and\/or entering text, partial entries accepted, select the Filter<\/strong> button at the top, to clear select the Clear Filter<\/strong> button.<\/p>\n

The code (in case your interested)…
\nPretty standard except that it loops thru the Tables in MSysObjects so it does not require the individual input of each Table run. Nor do you need to create tblTableFieldList before running as it will look to see if it’s there. (One caveat, I did not adjust the Form to run without the query which needs the Table, never got around to it. So, if you attempt to open the Form before creating the Table you will get a message. However, in the sample provided there is a Table so no worries there.)<\/p>\n

Function fncTableFieldList()\r\n'7.15.2016 Gina Whipp (Access Diva)\r\n'Purpose:  Write all table and field names to tblTableFieldList\r\n     \r\n    Dim lngTable As Long\r\n    Dim lngField As Long\r\n    Dim db As DAO.Database\r\n    Dim rs As DAO.Recordset\r\n    Dim tdf As DAO.TableDef\r\n    Dim lngRow As Long\r\n    Dim fld As DAO.Field\r\n    Dim strTable As String\r\n    \r\n        Set db = CurrentDb\r\n        \r\n        strTable = "tblTableFieldList"\r\n        \r\n        If DCount("*", "MSysObjects", "Type = 1 AND Name='" & strTable & "'") = 0 Then\r\n            'Create tblTableFieldList\r\n            Set tdf = db.CreateTableDef("tblTableFieldList")\r\n\r\n        With tdf\r\n           'Autonumber\r\n           Set fld = .CreateField("tflID", dbLong)\r\n           fld.Attributes = dbAutoIncrField + dbFixedField\r\n              .Fields.Append fld\r\n              \r\n              .Fields.Append .CreateField("tflTableName", dbText, 50)\r\n              .Fields.Append .CreateField("tflFieldName", dbText, 50)\r\n              .Fields.Append .CreateField("tflDataTypeID", dbLong)\r\n              .Fields.Append .CreateField("tflSize", dbLong)\r\n              .Fields.Append .CreateField("tflAttributeID", dbLong)\r\n              .Fields.Append .CreateField("tflComment", dbText, 255)\r\n              .Fields.Append .CreateField("tflExclude", dbBoolean)\r\n        End With\r\n            'Append new output table definition to database\r\n            db.TableDefs.Append tdf\r\n            Set fld = Nothing\r\n            Set tdf = Nothing\r\n            'Debug.Print "tblTableFieldList created."\r\n            Application.RefreshDatabaseWindow\r\n        Else\r\n            'Empty tblTableFieldList\r\n            strSQL = "DELETE tblTableFieldList.* FROM tblTableFieldList"\r\n                      CurrentDb.Execute strSQL, dbFailOnError\r\n            'Debug.Print "tblTableFieldList emptied."\r\n        End If\r\n        \r\n        Set rs = db.OpenRecordset("tblTableFieldList", dbOpenDynaset)\r\n    'Set on error in case there is no tables\r\n    On Error Resume Next\r\n    \r\n    'Loop through all tables\r\n    For lngTable = 0 To db.TableDefs.Count\r\n        'Ignore temporary (~) and system tables (MSys)\r\n        If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _\r\n            Left(db.TableDefs(lngTable).Name, 4) = "MSys" Then\r\n        Else\r\n            'Otherwise, loop through each table and get Primary Key\r\n            For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1\r\n            'Use this if you don't want Primary Key\r\n            'For lngField = 1 To db.TableDefs(lngTable).Fields.Count - 1\r\n                lngRow = lngRow + 1\r\n                    rs.AddNew\r\n                    rs!tflTableName = db.TableDefs(lngTable).Name\r\n                    rs!tflFieldName = db.TableDefs(lngTable).Fields(lngField).Name\r\n                    rs!tflDataTypeID = db.TableDefs(lngTable).Fields(lngField).Type\r\n                    rs!tflSize = db.TableDefs(lngTable).Fields(lngField).Size\r\n                    rs!tflAttributeID = db.TableDefs(lngTable).Fields(lngField).Attributes\r\n                    rs.Update\r\n            Next lngField\r\n        End If\r\n    Next lngTable\r\n    'Resume error breaks\r\n    On Error GoTo 0\r\n     \r\n    'Release from memory\r\n    Set rs = Nothing\r\n    Set db = Nothing\r\n     \r\nEnd Function\r\n <\/pre>\n
Enjoy!<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
When I’m designing a database I usually start off with one of my Templates. But, as with any Template, the Tables therein do not always fit exactly with the Client’s needs. And, since I like to document changes I built this tool…<\/p>\n<\/p>\n
In a nutshell… After the Template database is created I import the […]<\/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,63],"tags":[64,18,40],"class_list":["post-1180","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","tag-access-tips","tag-database-design","tag-table-design","odd"],"a3_pvc":{"activated":true,"total_views":1660,"today_views":0},"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1180","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=1180"}],"version-history":[{"count":15,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1180\/revisions"}],"predecessor-version":[{"id":1302,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1180\/revisions\/1302"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}