Solved

Command similar to SQL's    SELECT * FROM SYSOBJECTS

Posted on 2002-05-21
9
476 Views
Last Modified: 2011-09-20
Is there any access command similar to SQL's
SELECT * FROM SYSOBJECTS
0
Comment
Question by:rvaldivia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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