Solved

Command similar to SQL's    SELECT * FROM SYSOBJECTS

Posted on 2002-05-21
9
469 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 41
Access 2016 importing text files 13 20
Dynamically Reorder List Box 4 37
Field Properties in the DB Vs Bound Control on the form 9 28
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

808 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