Old QueryDEF values

Posted on 2011-10-19
Last Modified: 2012-05-12
I have some code that modifies query defs.   As I step through it, trying to find a problem, I am noticing that some of the query def names that cycle through a name check no longer exist in the database.  It is like there a bunch of junk in there....  the names typically begin with ~   as shown in the screenshot.  In the screenshot, some of that looks similar to  the name of and old subform "SF_COI_INS_CO_MAINT"  that is long since been deleted...

 How do I get rid of that junk?  I have already tried a compact and repair.  Thanks...
Question by:snyperj
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Those are actually SQL in say Combo or List Box Row Sources, or the Form Record Source ... where SQL was pasted directly into those properties.

    LVL 75

    Accepted Solution

    So, in other words, they may not be 'junk' ... especially if you have done a Compact & Repair.

    These are from my EE db ...

    LVL 26

    Assisted Solution

    If you turn on hidden objects in Tools|Options, you be able to view the MSysObjects table, that @mx put in a screenshot of.
    The names have a structure


    Refers to my form frmClients and to a control cboToBeKilled


    refers to the rowsource for  cbo_choices on your form c_COI_INS_CO_MAINT

    Check the form and control.  You'll see raw SQL in the rowsource.
    Save it out as a query, and you'll find that the entry is removed on compact and repair
    Whatever you do, don't muck with anything in the system tables!
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "Whatever you do, don't muck with anything in the system tables!"
    Not to worry ... because you can't make any changes to MSys tables ...


    Author Comment

    I guess what I don't understand is that I haven't had the form: F_COI_INS_CO_MAINT in this database for a long long time, yet the sql for a combo box that was on that form is still showing up in query defs.   In fact, I had over 100 items in the MSysObjects table that no longer exist in this database (which use to be a much larger app.)

    So I tried creating a new database and importing all of the existing objects (except the sys tables..)

    That seems to have cleared it all up.

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    odd ... C&R should ... clean that out ...


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now