Results 1 to 7 of 7
  1. #1
    Rev12 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    11

    How to get rid from displaying the MSysObjects Name in a list box in a form?

    Hi Everyone!
    A pleasant day!
    I have two listboxes in my Form one would display all my Tables and the other will display all my Queries.
    I have tried Using the SQL in my Row Source as :
    TABLES_LIST : RowSource = SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND [Name] Not Like "~MSysACEs*" ORDER BY [Name];
    QRY_LIST : SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE "~sq_*" ORDER BY [Name];



    The problem are it would still display like below;
    MSysAccessStorage
    MSysAccessXML
    ......
    Is there a way of getting red of this?
    Please any kind of help is much appreciated.
    Thanks in advance,
    Rev12

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    SYS objects should NEVER be available to be seen aNyway.
    if you can see them in your table browser,then they need to be set to invisible,
    in the access settings.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't think you want the tilde in there, or you want a second test without it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your criteria is

    [Name] Not Like "~MSysACEs*"

    So why do you not think it should not return

    MSysAccessStorage
    MSysAccessXML

    since they are not like MSysACEs

    if your criteria was

    [Name] Not Like "MSysAC
    CEs*"

    you may have a point - but then you would see the MSysACEs table

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    This works thru v2007.

    For Tables:
    Code:
    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
        (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

    For Queries:
    Code:
    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
        (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Here's a simpler way of showing local tables only - no hidden tables & no system tables:

    Code:
    SELECT MSysObjects.NameFROM MSysObjects
    WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0));
    This will show all queries without showing so called 'temp queries' (row sources for controls in forms & reports)
    Code:
    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3));
    Both work in all Access versions i've ever tested them with
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Rev12 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    11
    Dear All,

    Thanks a lot to all your help, it works though.

    thanks again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-05-2018, 09:26 PM
  2. Replies: 9
    Last Post: 11-24-2014, 02:20 PM
  3. Replies: 5
    Last Post: 08-11-2014, 03:08 PM
  4. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  5. Replies: 3
    Last Post: 03-25-2010, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums