Data Management

Use a form to display a list of reports and queries in an Access database

When you need to know what objects are being created for a database, you can run a query against an Access system table to generate a list of all current reports and queries.

Ever wonder exactly what reports and queries are being created for a database? As an administrator, you may need a quick way of determining what reports and queries are being run against a database. Fortunately, Access stores the names of all the top-level database objects in a system table called MSysObjects. You can run queries against MSysObjects just as you would any other table in the database.  Follow these steps:

  1. Open the database and create a form with two unbound list boxes, as shown in Figure A.

Figure A

two unbound boxes

  1. In Design view, right-click the first list box and select Properties.
  2. Under the Data tab, right-click the RowSource property box and select Zoom.
  3. Enter the code as shown in Figure B and click Close.

Figure B

rowsource property

  1. Right-click the second list box and select Properties.
  2. Under the Data tab, right-click the RowSource property box and select Zoom.
  3. Enter the code as shown in Figure C.

Figure C

queries

When you run the form, Access outputs all current reports and queries for that database to the respective list boxes (Figure D).

Figure D

object query

To access other object types, use their type value. For example, to list all the tables in the database, enter 6. To list forms, enter -32768. To list macros, enter -32766.


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

5 comments
ttrollen
ttrollen

When using MsysObjects to harvest the names of the queries, you obtain several queries whose name begins with ~sq_. These are embedded queries that serve as the RowSource for list and combo boxes and embedded queries that serve as the RecordSource for a Form or Report. If you want to display only saved query objects and not display these embedded queries, change Figure C to read: SELECT [Name] FROM MsysObjects WHERE [Type]=5 And Left([Name],1)"~" ORDER BY [Name];

Dayle1011
Dayle1011

Is there a way that you can click on an object on the list and open that particular report or query?

rtaub
rtaub

Great Tip!!! I have been teaching basic MS Access classes at the City of Houston for over 13 years. This tip is one that I have thought about for a long time, but never had the "values" necessary to make it work. Thanks

rw2000
rw2000

For the On Click Event set the module to: If Len(.value & "") > 0 Then DoCmd.OpenReport value, acPreview (or DoCmd.OpenQuery .value) End If

beck.joycem
beck.joycem

I'm using Access 2003, and your code doesn't seem to work for me. It says that .value is an 'invalid or unqualified reference'. Bit of a VBA novice, can you help?

Editor's Picks