Set objRecordset = objConnection.OpenSchema(adSchemaTables)\n<\/PRE>\nAt this point, we get serious.<\/P>\n
Well, right after we eat another doughnut, that is.<\/P>\n
OK, now<\/I> we get serious. Our first step is to set up a Do Until loop that runs until objRecordset\u2019s EOF<\/B> (end-of-file) property is True. (In other words, a loop that runs until we\u2019ve finished looking at all the items in the recordset.) Inside this loop, we set the value of a variable named strSample to nothing, then assign the value of the Table_Name<\/B> property (which, needless to say, is nothing more than the name of the table) to the variable strTableName.<\/P>\n
That brings us to this line of code:<\/P>
If UCase(objRecordset(“Table_Type”)) = “TABLE” Then\n<\/PRE>\nTo be honest, we threw this in just for the heck of it. By limiting our reported data to those tables that have a Table_Type<\/B> equal to TABLE we end up only displaying information for the database tables that we<\/I> created; any other tables (such as the various sundry Access system tables) are ignored. That seemed like a good idea to us; after all, 99% of the time (at least) these are the only tables you\u2019re interested in anyway.<\/P>\n\n<\/THEAD>\n\n\n| \n Note<\/B>. What about that other 1% of the time? No problem; just delete the If-Then statement and the End If statement and you\u2019ll get back information for all<\/I> the tables in the database.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n<\/DIV>\n Assuming we do<\/I> have a user-created table we then call the OpenSchema<\/B> method in order to return field-related information for the first table in the recordset:<\/P>Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _\n Array(Null, Null, strTableName))\n<\/PRE>\nWe then use this block of code to write a \u201cheader\u201d to the screen:<\/P> Wscript.Echo “============================================================”\nWscript.Echo UCase(objRecordset(“Table_Name”))\nWscript.Echo “============================================================”\nWscript.Echo\n<\/PRE>\nAs you can see, this is no big deal: it\u2019s just the table name (TABLE_NAME) bracketed by some equals sign, the better to make the name stand out and to separate one table from another. In other words, this gives us a header that looks like this:<\/P> ============================================================\nCOMPUTERS\n============================================================\n<\/PRE>\nAnd then we have these two lines of code:<\/P> objRecordset2.Open “SELECT * FROM ” & strTableName, objConnection\nobjRecordset2.MoveFirst\n<\/PRE>\nWhat are these<\/I> lines doing here? Well, we now know the name of each field in the table; that information is safely tucked away in the recordset objFieldSchema. However, the information in objFieldSchema only includes \u201cmetadata\u201d about the fields: it lists the field name, type, and size, but not<\/I> the field value. We use the first line of code to create a regular old ADO recordset, one consisting of all data in the table strTableName. We then use the second line of code to move to the first record in the returned recordset. This first record is going to serve as the source of the sample record we want to display onscreen.<\/P>\n From there we set up a second Do Until loop to loop through the field data stored in objFieldSchema. Inside that loop we echo back some basic information about each field, including the value of the field\u2019s Character_Maximum_Length<\/B> property. And yes, as usual, you\u2019re way ahead of us; that does<\/I> tell us the size of the field:<\/P>Wscript.Echo “Field Size: ” & objFieldSchema(“Character_Maximum_Length”)\n<\/PRE>\n \n<\/THEAD>\n\n\n| \n Note<\/B>. We should point out that fields will only have a size if the underlying data type has a size. A field that can contain a maximum of 100 characters will report back a Character_Maximum_Length value of 100. A numeric field, by contrast, doesn\u2019t have<\/I> a field size. Thus you\u2019ll get back, well, nothing.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n<\/DIV>\n After that we use this line of code to store the field name, as well as the actual field value for the first record in the table, in the variable strSample:<\/P> strSample = strSample & strColumn & ” — ” & _\n objRecordset2.Fields.Item(strColumn) & vbCrLf\n<\/PRE>\nSee what we\u2019re doing? Suppose the first field in the first table is named ComputerName, and the first record in the table has the value atl-ws-01. That means that, at the moment anyway, strSample will be equal to this:<\/P> ComputerName — atl-ws-01\n<\/PRE>\nAnd then we loop around and repeat the process with the next field in the table, adding the field name (and sample value) to strSample. After we\u2019ve looped through all the fields in the table we echo back the value of strSample, close objRecordset2, and then go back and do this all over again with the second table in the database.<\/P>\n When all is said and done, we\u2019ll have a report similar to this:<\/P> ============================================================\nCOMPUTERS\n============================================================<\/p>\nField Name: ComputerName\nData Type: 130\nField Size: 50<\/p>\n Field Name: SerialNumber\nData Type: 130\nField Size: 50<\/p>\n SAMPLE RECORD\nComputerName — atl-ws-01\nSerialNumber — 1<\/p>\n ============================================================\nDISKDRIVES\n============================================================<\/p>\n Field Name: Drive\nData Type: 130\nField Size: 50<\/p>\n Field Name: DriveSize\nData Type: 2\nField Size:<\/p>\n Field Name: SerialNumber\nData Type: 130\nField Size: 50<\/p>\n Field Name: Type\nData Type: 130\nField Size: 50<\/p>\n SAMPLE RECORD\nDrive — C:\nDriveSize — 50\nSerialNumber — 1\nType —\n<\/PRE>\n Yes, very<\/I> exciting.<\/P>\n We hope that answers your question, SP. And Scripting Guy Jean Ross, if you happen to be reading this, well, don\u2019t worry about the rest of the team: if you want to work at home tomorrow that\u2019s fine with us. In fact, feel free to work at home as often as you wish; whatever\u2019s easiest \u2013 and best \u2013 for, uh, you.<\/P>\n Now, where did that last maple bar wander off to \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":" I recently read an article of yours that showed me how I can retrieve the names of all the tables in an Access database, along with a list of all the fields in each table and the data type of each of those fields. That\u2019s a really useful script, but it would be even more […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[54,49,3,5],"class_list":["post-63883","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-access","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" I recently read an article of yours that showed me how I can retrieve the names of all the tables in an Access database, along with a list of all the fields in each table and the data type of each of those fields. That\u2019s a really useful script, but it would be even more […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63883"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63883\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/H2>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n