Solved

Command similar to SQL's    SELECT * FROM SYSOBJECTS

Posted on 2002-05-21
9
472 Views
Last Modified: 2011-09-20
Is there any access command similar to SQL's
SELECT * FROM SYSOBJECTS
0
Comment
Question by:rvaldivia
  • 5
  • 3
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7025679
Tables and queries can be extracted using the tabledefs collection and queries with the querydefs.
Forms, reports, etc (when not opened) can be found in the container/documents collection.

What do you need?

Nic;o)
0
 
LVL 2

Expert Comment

by:mjasic
ID: 7025725
Oh, yes, there is a command like:
SELECT * FROM MsysObjects.

Fields that might interest you are: Name (i.e. object name) and Type (i.e. object type).

The object types are:
Tables: 1
Queries: 5
Forms: -32768
Reports: -32764
Macros: -32766
Modules: -32761
0
 
LVL 2

Expert Comment

by:mjasic
ID: 7025733
Nico's idea is good too, but is more complicated to be executed. It requires coding with loops and a good knowledge of objects' hierarchy.
I think it is not that difficult to execute a query with a simple Switch function:

SELECT Name, Switch(Type=1,"Tables",Type=5,"Queries",Type=-32768,"Forms",Type=-32764,"Reports",Type=-32766,"Macros",True,"Modules") as ObjectType FROM MsysObjects Where Type= 1 or Type=5 or Type=-32768 or Type=-32764 Or Type=-32766 Or Type = -32761 ORDER BY Type, Name
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:mjasic
ID: 7025737
BTW.
You can omit all the objects starting with ~ because they are temporary objects and dissapear after compacting mdb.
0
 
LVL 2

Expert Comment

by:mjasic
ID: 7025743
BTW.
You can omit all the objects starting with ~ because they are temporary objects and dissapear after compacting mdb.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7025774
Hi mjasic,

It might be "more complicated", but it's a guaranteed way.
The Msys tables are owned by M$ and they can change values and fieldnames as they like when releasing a new access version.
(They did it e.g. with the fieldnames of outlook that won't import anymore like it did...)

Nic;o)
0
 
LVL 2

Expert Comment

by:mjasic
ID: 7025785
Hi Nico
Yes, everything is possible, but SELECT * FROM MsysObjects will work at least until the next few versions of Access. BTW these names haven't changed since a long time and probably will not change soon. And, who guarantees that MS will not change their object hierarchy and make invalid coding as we know it? Who guarantees that Access will not disappear in 2 or 3 years. It works with actual versions, and it works fine.
For values - the people can always find out what 5 or -32761 for MyObjectName means. It's easy: find ObjectName and if you follow the coding convention, you'll know that frmMyWhatever is form, no matter the associated number.

In fact, the things that change in each version of Access (and it makes me mad) are Menus and Toolbars. It's a neverending story :(

I'm not looking for points. I only think that it is easier with a short query than with coding. And probably the people are more familiar with SQL than with VB.

I am with Access since the version 1.1 and I developped tons of procedures that play with objects and hierarchy, but when I don't have my libraries with me, I build a query to find objects (because it is faster to do).
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7226593

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7245619
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Microsoft Access 2016 Bug? 9 42
Access syntax 1 33
Using Access multi Listbox to pass criteria to a query 9 24
How do a DCount on a report 1 12
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question