{"id":67153,"date":"2006-06-08T09:20:00","date_gmt":"2006-06-08T09:20:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/06\/08\/how-can-i-list-all-the-tables-in-an-access-database\/"},"modified":"2006-06-08T09:20:00","modified_gmt":"2006-06-08T09:20:00","slug":"how-can-i-list-all-the-tables-in-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-list-all-the-tables-in-an-access-database\/","title":{"rendered":"How Can I List All the Tables in an Access Database?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I list all the tables in an Access database?

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

Hey, KW. You know, the best part about writing a daily column on system administration scripting is that people only see the finished result; they never see all the detours, dead-ends, and other frustrations we go through in order to produce that column. For example, people who read this column will think, \u201cMan, those Scripting Guys are so smart: they even know how to list all the tables in an Access database!\u201d Those people will never know that we had absolutely no idea<\/I> how to list all the tables in an Access database, and that we stumbled upon the answer only after a long and fruitless search of the Access object model. But as far as the world knows, we Scripting Guys really are<\/I> smart!<\/P>\n

Well, unless we admitted how hard it was for us to answer this question. But even the Scripting Guys aren\u2019t dumb enough to own up to something like that.<\/P>\n

Now, where we were? Oh, right: how can you list all the tables in an Access database? That\u2019s an easy one, KW; here\u2019s how:<\/P>

Const adSchemaTables = 20<\/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    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)\n    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)\n    Wscript.Echo\n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
Admittedly, people without the Scripting Guys\u2019 experience and expertise in the world of system administration scripting might think that the way to attack this problem would be to use the Microsoft Access object model. We Scripting Guys know better, however: instead, you get at this information using ADO (ActiveX Data Objects). And that\u2019s actually a good thing: that means you can use this same basic approach to list the tables found in other kinds of databases. (Right: as if there even are<\/I> databases other than Microsoft Access!)<\/P>\n
Let\u2019s take a closer look at the script and how it works. To begin with, we define a constant named adSchemaTables and set the value to 20; we\u2019ll use this constant to tell the script what kind of database schema objects we\u2019re interested in (needless to say, in this case, all we care about are tables). Are there other kinds of schema objects we can retrieve using a script? You bet: check out the SchemaEnum<\/B><\/A> documentation on MSDN for more information.<\/P>\n
After defining the constant we then create a pair of objects: the ADODB.Connection<\/B> object and the ADODB.Recordset<\/B> object. If you\u2019ve had some experience working with ADO then you should be very familiar with the Connection and Recordset objects. But what if you\u2019re new to database scripting and have no idea what we\u2019re talking about here? That\u2019s OK: the Scripting Guys webcast Database Scripting for System Administrators<\/B><\/A> will give you all the background information you need to understand this script and how it works.<\/P>\n
As soon as we have our two objects in-hand we can then use the Open<\/B> method to open the file C:\\Scripts\\Test.mdb:<\/P>
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = ‘C:\\Scripts\\Test.mdb'”\n<\/PRE>\n
And once we\u2019ve done that<\/I> we can then call the OpenSchema<\/B> method and retrieve information about the database itself. What kind<\/I> of information are we retrieving? Well, in this case we\u2019re passing OpenSchema the constant adSchemaTables; that means we\u2019re going to get back a recordset consisting of information about all the tables found in the database.<\/P>\n
Because we\u2019re getting back a recordset we need to next set up a Do Until loop to walk through and echo back information about all the records (that is, all the tables) in the recordset. That\u2019s what we do here:<\/P>
Do Until objRecordset.EOF\n    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)\n    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)\n    Wscript.Echo\n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
As you can see, our Do Until loop is designed to run until the EOF<\/B> (end of file) property is true; in other words, we want to keep looping around until we run out of records. To that end, take careful note of the last line of code within the Do Until loop: objRecordset.MoveNext<\/B>. Is that important? You bet it is: that\u2019s the line of code that tells the script, hey, after you finish with one record move on to the next. If you don\u2019t include the MoveNext method the script will remain stuck on the first record. In turn, that means you\u2019ll continue to echo back information about record 1 and will never, ever reach the end of the recordset. (For a more detailed explanation of this take a look at the answer to the scripting puzzle Yes, Ken, We See You Already!<\/B><\/A>)<\/P>\n
So do we do anything inside the loop besides call the MoveNext method? Of course we do; we also echo back the values of the TABLE_NAME and TABLE_TYPE properties:<\/P>
Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)\nWscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)\n<\/PRE>\n
Is that<\/I> important? You might say that; after all, that happens to be the list of tables (and their associated table types) found in the database C:\\Scripts\\Test.mdb:<\/P>
Table name: UserInformation\nTable type: TABLE<\/p>\n
Table name: ComputerInformation\nTable type: TABLE<\/p>\n
Table name: MSysAccessObjects\nTable type: ACCESS TABLE<\/p>\n
Table name: MSysACEs\nTable type: SYSTEM TABLE<\/p>\n
Table name: MSysObjects\nTable type: SYSTEM TABLE<\/p>\n
Table name: MSysQueries\nTable type: SYSTEM TABLE<\/p>\n
Table name: MSysRelationships\nTable type: SYSTEM TABLE\n<\/PRE>\n
As you probably noticed, this script brings back information about all<\/I> the tables in the database, including system tables you typically don\u2019t interact with (or care about). Can you limit the returned data just to those tables (like UserInformation and ComputerInformation) that you typically do<\/I> interact with? Sure; all you have to do is modify the script like so:<\/P>
arrCriteria = Array(Empty, Empty, Empty, “Table”) <\/p>\n
Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)\n<\/PRE>\n
What we\u2019ve done here is created an array named arrCriteria and told it to look just for tables where the TABLE_TYPE is equal to Table<\/I>. (The three Empty parameters represent other criteria we could use in our filter; for more information, see the SchemaEnum<\/B><\/A> documentation.) We then add the variable arrCriteria as an optional second parameter to the OpenSchema method.<\/P>\n
What will we get back when we run this modified script? We\u2019ll get back this:<\/P>
Table name: UserInformation\nTable type: TABLE<\/p>\n
Table name: ComputerInformation\nTable type: TABLE\n<\/PRE>\n
Here\u2019s what the entire script looks like:<\/P>
Const adSchemaTables = 20<\/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
arrCriteria = Array(Empty, Empty, Empty, “Table”) <\/p>\n
Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)<\/p>\n
Do Until objRecordset.EOF\n    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)\n    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)\n    Wscript.Echo\n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
And, yes, all that was<\/I> pretty easy, wasn\u2019t it? Well, at least for the Scripting Guys \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I list all the tables in an Access database?— KW Hey, KW. You know, the best part about writing a daily column on system administration scripting is that people only see the finished result; they never see all the detours, dead-ends, and other frustrations we go through in order to […]<\/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-67153","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! How can I list all the tables in an Access database?— KW Hey, KW. You know, the best part about writing a daily column on system administration scripting is that people only see the finished result; they never see all the detours, dead-ends, and other frustrations we go through in order to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67153","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=67153"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67153\/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=67153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}