Search The Web

Today's Headlines

Monday, March 1, 2010

Microsoft Access Tips & Tricks: Working With System Objects

Microsoft Access internally keeps track of tables, queries, forms, reports, macros and modules you create as part of a database file in various system objects. By default, these system objects are not visible to users of the database, and these objects are not well-documented. But, learning to use some of these system objects can come in very handy.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries and crosstab queries.

As mentioned earlier, the system objects are usually not visible. To make them visible in Access 2003, you have to click on Tools->Options. Switch to the View tab, and put a check-mark in the option "System Objects" under Show. Hit OK to close out the options. If you now go to the tables pane of your database, you will see a bunch of new tables there. They all start with the letters "MSys", to indicate that they are system objects.

Out of these system objects, the one we are going to use in this post is the MSysObjects table. This table contains details about various objects in the database, such as tables, queries, forms, etc. In this post, we will look at how to get a list of names of these different objects in the database.

Before we go any further, heed this warning carefully: The system objects in a database are not designed to be modified by users directly. Microsoft Access keeps track of these objects and their contents as part of its book-keeping. Modifying the contents of these system tables can render the database unuseable. So, use extreme caution in using the queries in this post. They only read the system tables and do not make any modifications, so they are pretty safe, but make sure you do not change the queries into action queries and make any modifications to the system tables. Once you have satisfied your curiosity, it is best to go back into the options and turn off the checkmark that enables you to see these tables in the first place.

One may require a list of tables, queries, forms or other objects in a database for a variety of reasons. One of them might be to programmatically loop over all database objects for some reason. Another reason might be to populate comboboxes or listboxes with names of these objects.

If you look at the contents of the MSysObjects table, you will find that it contains several fields such as "DateCreate", "DateUpdate", "Name", "Type", etc. We will see how we use these fields to get a list of various user-created database objects such as tables, queries, etc. The following queries can be used to get lists of various user-created database objects.

Tables:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Left([Name],4) <> "MSys") AND (Type)=1
ORDER BY Name

Notice in the above query that we have excluded names that start with "~". We have also excluded names that start with "MSys" since the system objects (including MSysObjects itself) are tables, and we don't want them in the list returned by this query since they are not user-created tables.

Instead of ordering by Name, one can order the list by DateUpdate or DateCreate also to get a list in chronological order of creation date or the date when the tables were last modified. The DateUpdate field contains the date on which a table's design was last updated, not the date on which a table's contents were last modified. One can also use the DateCreate and DateUpdate fields in a WHERE clause in the query to exclude or include tables by creation or update date.

Linked Tables:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Left([Name],4) <> "MSys") AND (Type)=6
ORDER BY Name

Notice that the type has been changed in the above query to 6. Every database object has a different type number and 1 stands for tables while 6 stands for linked tables.

Queries:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Type)=5 ORDER BY Name

Notice that in this query, we have not excluded objects that start with "MSys". The reason is that all the objects that start with MSys are tables, so none of them have type 5, which stands for queries. This query can also be modified to include ordering or filtering by creation or update date. Once again, the DateUpdate field contains the date and time when the query's design was last modified, not the date and time when it was last run.

Forms:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Type)=-32768 ORDER BY Name

Reports:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Type)= -32764 ORDER BY Name

Macros:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Type)= -32766 ORDER BY Name

Modules:

SELECT Name FROM MSysObjects WHERE (Left([Name],1)<>"~") AND
(Type)= -32761 ORDER BY Name

The following table tells you the type codes for the different types of database objects catalogued in MSysObjects for your quick reference:

Object Type Code
Tables 1
Linked Tables 6
Queries 5
Forms -32768
Reports -32764
Macros -32766
Modules -32761


Using this reference, you can now catalogue all the user-created objects in the database using a query like the one below. Notice the use of nested iif() calls to derive the name of the object type from the type code. One could write a small user-defined function to take the type code as input and return the object type name if you want to get rid of the long nested iif() which can make the query hard to read, interpret and maintain.

SELECT Name, DateCreate, DateUpdate,
iif(type = 1,"Table", iif(type = 6, "Linked Table",
iif(type = 5,"Query", iif(type = -32768,"Form",
iif(type = -32764,"Report", iif(type=-32766,"Module",
iif(type = -32761,"Module", "Unknown"))))))) as [Object Type]
FROM MSysObjects WHERE Type IN (1, 5, 6, -32768, -32764, -32766, -32761)
and LEFT(Name, 4) <> "MSys" and LEFT(Name, 1) <> "~"
ORDER BY Name

Hope this post has been helpful in solving any problems you might have had with using system objects in Access. If you have any problems or concerns with the queries in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web