{"id":64493,"date":"2007-07-10T00:38:00","date_gmt":"2007-07-10T00:38:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/07\/10\/how-can-i-list-all-the-fields-and-data-types-in-an-access-database\/"},"modified":"2007-07-10T00:38:00","modified_gmt":"2007-07-10T00:38:00","slug":"how-can-i-list-all-the-fields-and-data-types-in-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-list-all-the-fields-and-data-types-in-an-access-database\/","title":{"rendered":"How Can I List All the Fields and Data Types in an Access Database?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! In a previous column<\/B><\/A> you showed us how to list all the tables in an Access database. How can I list all the fields in those tables, along with the data type of each field?

— MF <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, MF. As you may or may not know, there\u2019s a Microsoft researcher named Gordon Bell who has devoted his professional efforts to \u201clifelogging,\u201d an activity in which he uses cameras, scanners, tape recorders, and other gadgets to keep a digital record of everything he ever does. According to a recent article<\/B><\/A> in The New Yorker<\/I>:<\/P>\n

\u201cBell\u2019s archive now also contains a hundred and twenty-two thousand e-mails; fifty-eight thousand photographs; thousands of recordings of phone calls he has made; every Web page he has visited and instant-messaging exchange he has conducted since 2003; all the activity of his desktop (which windows, for example, he has opened); eight hundred pages of health records, including information on the life of the battery in his pacemaker; and a sprawling category he describes as \u201cephemera,\u201d which contains such things as books he has written and books from his library; the labels of bottles of wine he has enjoyed; and the record of a bicycle trip through Burgundy, where he tried to eat in as many starred restaurants as he could (he averaged 2.2 stars per meal\u2014\u201cI do a lot of measuring,\u201d he says).\u201d<\/P>\n

Before you ask, the Scripting Guys do not engage in lifelogging, mainly because none of them have lives worth logging. For example, the lifelog of the Scripting Guy who writes this column would consist of little more than an endless pile of receipts from the local doughnut shop. You can only log \u201ctwo maple bars and a pair of cinnamon twists\u201d so many times before it starts to seem a little pointless. <\/P>\n

However, it\u2019s also true that the Scripting Guys don\u2019t need<\/I> a lifelog, at least not as long as they have the Hey, Scripting Guy!<\/I> column. For example, when the Scripting Guy who writes this column first read this question he thought, \u201cList all the tables in an Access database, huh? That would<\/I> be a useful script, wouldn\u2019t it? But who knows how to write a script like that?\u201d And then, as he read a little further, he realized that not only did he<\/I> know how to write a script that lists all the tables in an Access database, but that he\u2019d already done so. \u201cWell, what do you know,\u201d he thought to himself. \u201cMaybe I should read this column more often.\u201d<\/P>\n

At any rate, thanks to the Hey, Scripting Guy!<\/I> lifelog the Scripting Guy who writes this column was able to recreate that magic moment when he wrote a script that retrieved a list of all the tables in an Access database. From there it was relatively painless to take the next step and list the fields and data types for each of those tables:<\/P>

Const adSchemaTables = 20\nConst adSchemaColumns = 4<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = ‘C:\\Scripts\\Test.mdb'” <\/p>\n
Set objRecordSet = objConnection.OpenSchema(adSchemaTables)<\/p>\n
Do Until objRecordset.EOF\n    strTableName = objRecordset(“Table_Name”)\n    Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _\n        Array(Null, Null, strTableName))\n    Wscript.Echo UCase(objRecordset(“Table_Name”))<\/p>\n
    Do While Not objFieldSchema.EOF\n        Wscript.Echo objFieldSchema(“Column_Name”) & “, ” & objFieldSchema(“Data_Type”)\n        objFieldSchema.MoveNext\n    Loop<\/p>\n
    Wscript.Echo\n    objRecordset.MoveNext\nLoop\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Feel free to add this script to your own lifelog: the Scripting Guys don\u2019t have copyrights on their lives; we\u2019ve never been all that concerned that people would try to copy our lives and claim them for their own. We also have plenty of doughnut shop receipts, you\u2019re more than welcome to add those to your lifelog as well. Just let us know how many you need.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Let\u2019s take a few moments out from our busy lives and see if we can figure out how this script works. We start out by defining a pair of constants, adSchemaTables and adSchemaColumns. Eventually we\u2019ll use one of these constants to tell the script to retrieve table information from the database, then use the other to retrieve column (field) information from the database. See if you can guess which constant will be used for which task.<\/P>\n
OK, apparently that wasn\u2019t as hard as we thought it would be. Good thing we didn\u2019t make that<\/I> the basis of our recent TechEd Challenge<\/B><\/A>.<\/P>\n
After defining the constants we create instances of the ADODB.Connection<\/B> and ADODB.Recordset<\/B> objects, then use the following line of code to open the database C:\\Scripts\\Test.mdb:<\/P>
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = ‘C:\\Scripts\\Test.mdb'”\n<\/PRE>\n
Once we\u2019ve made the connection to the database we can retrieve a list of tables by using a single line of code and the OpenSchema<\/B> method, passing the constant adSchemaTables as the only parameter for that method:<\/P>
Set objRecordSet = objConnection.OpenSchema(adSchemaTables)\n<\/PRE>\n
As soon as we\u2019ve done that we\u2019re ready to really<\/I> spice up the old lifelog: we\u2019re now ready to loop through the collection of tables and, for each one, retrieve not only a list of all the fields in the table, but also the data type of each field.<\/P>\n
To that end we start out by setting up a Do Until loop that runs until we\u2019ve finished iterating through all the records in the recordset (that is, until the recordset\u2019s EOF<\/B> \u2013 end-of-file \u2013 property is True). The first thing we do inside the loop is take a look at record 1, grab the value of the Table_Name<\/B> property, then store that value in a variable named strTableName:<\/P>
strTableName = objRecordset(“Table_Name”)\n<\/PRE>\n
Once we know the name of the first table we can then use this line of code to retrieve a second recordset, this one consisting of all the fields (columns) in the table:<\/P>
Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _\n    Array(Null, Null, strTableName))\n<\/PRE>\n
As you can see, we\u2019re once again calling the OpenSchema<\/B> method in order to access the database schema. This time, however, we need to pass a few additional parameters to OpenSchema. For starters, we pass the constant adSchemaColumns, which simply tells the script that we want to return column information. We then pass an array of \u201cconstraint columns.\u201d This array consists of the following values:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
A Null<\/B> value representing the table catalog.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
A Null<\/B> value representing the table schema.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The variable strTableName<\/B>, representing the name of the table we want to bind to.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
For the time being, don\u2019t worry too much about the table catalog and table schema. Just make sure your table name appears as the third item in the array and everything should work just fine.<\/P>\n
After retrieving the second recordset (objFieldSchema) we can start displaying the data for the first table in the database. To kick off that process, we first echo back the name of the table, using the UCase<\/B> function to make the table name appear in all uppercase characters:<\/P>
Wscript.Echo UCase(objRecordset(“Table_Name”))\n<\/PRE>\n
We then set up a second Do Until loop, this one designed to loop through the collection of fields in table 1:<\/P>
Do While Not objFieldSchema.EOF\n<\/PRE>\n
And what are we going to do inside this<\/I> loop? Two things. First, we\u2019re going to echo back the values of the Column_Name<\/B> and Data_Type<\/B> properties:<\/P>
Wscript.Echo objFieldSchema(“Column_Name”) & “, ” & objFieldSchema(“Data_Type”)\n<\/PRE>\n
Second, we\u2019re going to call the MoveNext<\/B> method to move us from the first record in the recordset to the next record. After we\u2019ve echoed back information about each item in the recordset we echo a blank line to the screen and then call MoveNext a second time, this time to move to the next record (table) in our original recordset. When we\u2019re finished we\u2019ll have a report that looks something like this (depending on the tables and fields you have in your database):<\/P>
COMPUTERS\nComputerName, 130\nSerialNumber, 130<\/p>\n
DISKDRIVES\nDrive, 130\nDriveSize, 2\nSerialNumber, 130\nType, 130<\/p>\n
EMPLOYEES\nBonus, 6\nDepartment, 130\nDivision, 130\nEmployeeID, 5\nEmployeeName, 130\nSalary, 6\n<\/PRE>\n
Ah, good question: what are<\/I> those numbers following the field names? Well, as it turns out, data types are stored as numeric values; for example, the field Salary<\/I> has a data type of 6. If you look up the values list for the DatatypeEnum<\/B><\/A> you\u2019ll see that a 6 indicates a currency data type. And yes, we probably could have \u2013 and maybe should have \u2013 included all this information in our sample script. We didn\u2019t do that simply because we were afraid the script would become too long and look far more complicated than it really is.<\/P>\n
Besides, our lifelog was already beginning to fill up.<\/P>\n
Incidentally, you don\u2019t need to look at all<\/I> the tables in the database. For example, suppose you were only interested in getting field information for the Employees<\/I> table:<\/P>
Const adSchemaColumns = 4<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = ‘C:\\Scripts\\Test.mdb'” <\/p>\n
Set objRecordSet = objConnection.OpenSchema(adSchemaColumns, _\n    Array(Null, Null, “Employees”))<\/p>\n
Do Until objRecordset.EOF\n    Wscript.Echo objRecordset(“Column_Name”) & “, ” & objRecordset(“Data_Type”)\n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
That should do it, MF. At least we hope it does, because we have to leave: it\u2019s time for our mid-morning doughnut break.<\/P>\n
Well, OK: time for our second<\/I> mid-morning doughnut break. Just two more after that and it\u2019ll be lunch time.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! In a previous column you showed us how to list all the tables in an Access database. How can I list all the fields in those tables, along with the data type of each field?— MF Hey, MF. As you may or may not know, there\u2019s a Microsoft researcher named Gordon Bell […]<\/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":[19,146,54,49,3,5],"class_list":["post-64493","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! In a previous column you showed us how to list all the tables in an Access database. How can I list all the fields in those tables, along with the data type of each field?— MF Hey, MF. As you may or may not know, there\u2019s a Microsoft researcher named Gordon Bell […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64493","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=64493"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64493\/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=64493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}