![microsoft access database objects microsoft access database objects](https://www.fmsinc.com/microsoftaccess/UnusedObjects/UnusedReports.png)
To that end, take careful note of the last line of code within the Do Until loop: objRecordset.MoveNext. That’s what we do here: Do Until objRecordset.EOFĪs you can see, our Do Until loop is designed to run until the EOF (end of file) property is true in other words, we want to keep looping around until we run out of records. What kind of information are we retrieving? Well, in this case we’re passing OpenSchema the constant adSchemaTables that means we’re going to get back a recordset consisting of information about all the tables found in the database.īecause we’re 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. But what if you’re new to database scripting and have no idea what we’re talking about here? That’s OK: the Scripting Guys webcast Database Scripting for System Administrators will give you all the background information you need to understand this script and how it works.Īs soon as we have our two objects in-hand we can then use the Open method to open the file C:\Scripts\Test.mdb: objConnection.Open _Īnd once we’ve done that we can then call the OpenSchema method and retrieve information about the database itself. If you’ve had some experience working with ADO then you should be very familiar with the Connection and Recordset objects. Are there other kinds of schema objects we can retrieve using a script? You bet: check out the SchemaEnum documentation on MSDN for more information.Īfter defining the constant we then create a pair of objects: the ADODB.Connection object and the ADODB.Recordset object. To begin with, we define a constant named adSchemaTables and set the value to 20 we’ll use this constant to tell the script what kind of database schema objects we’re interested in (needless to say, in this case, all we care about are tables).
![microsoft access database objects microsoft access database objects](https://www.techonthenet.com/access/database/images/display_all2013_001.gif)
Let’s take a closer look at the script and how it works. (Right: as if there even are databases other than Microsoft Access!) And that’s actually a good thing: that means you can use this same basic approach to list the tables found in other kinds of databases. We Scripting Guys know better, however: instead, you get at this information using ADO (ActiveX Data Objects). Wscript.Echo “Table type: ” & (“TABLE_TYPE”)Īdmittedly, people without the Scripting Guys’ 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. Wscript.Echo “Table name: ” & (“TABLE_NAME”) Set objRecordSet = objConnection.OpenSchema(adSchemaTables) Set objRecordSet = CreateObject(“ADODB.Recordset”) Set objConnection = CreateObject(“ADODB.Connection”) Now, where we were? Oh, right: how can you list all the tables in an Access database? That’s an easy one, KW here’s how: Const adSchemaTables = 20 But even the Scripting Guys aren’t dumb enough to own up to something like that. Well, unless we admitted how hard it was for us to answer this question. But as far as the world knows, we Scripting Guys really are smart!
Microsoft access database objects how to#
For example, people who read this column will think, “Man, those Scripting Guys are so smart: they even know how to list all the tables in an Access database!” Those people will never know that we had absolutely no idea 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. 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. Hey, Scripting Guy! How can I list all the tables in an Access database?