Solved

Command similar to SQL's    SELECT * FROM SYSOBJECTS

Posted on 2002-05-21
9
470 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access VBA Code to Include Library References 4 51
ADODB problem 20 38
trim dropdown value before executing query 8 27
Number of records returned on a form 5 25
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

860 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