"Mysterious" SQL Queries That Would Not Go Away in Access 2007

I have a small pop-up form that I call "Database Window"; basically it lists tables and/or queries in a list box for the user to select - The list box record source type is "Value List" that is generated by a sub that reads the database and serve as an SQL row source.for the list box.

It works fine except that due to the large numbers of queries It exceeds the maximum of 32750 characters for for a row source on occasions. I can overcome this by testing the length before it is used as a row source and if it is too large, tell the user to further limit the search; no problem thus far.

However when I limit the serach  sufficiently a number of "mysteruous" queries appears in the windows, all starts with a tidal char (~) and look as if they are old failed SQL's - I certainly did mot allocate theses names

 ( "Mysterious" SQL Queries

I cannot find those "queries" anywhere in the the database (presumable they are hidden) nor can I display them when I click on them in the List

Can someone help me to:

1. get rid of those redundant sql's and more important,
2. What should I do (or not do) to avoid those sql from collecting?

Many thanks
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<No Points wanted>

<look as if they are old failed SQL's>
Actually most are Rowsources for your various comboboxes or Listboxes, ...so don't blindly delete them!

<I cannot find those "queries" anywhere in the the database (presumable they are hidden) nor can I display them when I click on them in the List >
Yeah this is odd..
However, you can see them in the hidden "MsysObjects" table though...


peter57rConnect With a Mentor Commented:
Access generates all sorts of stuff internally and these queries are part of it.

When you are building your rowsource value list you should test the name and not select those with a ~ as the first character, or anything with msys as the first 4 characters
jyk_ausAuthor Commented:
For Peter and Jeff,

Many thanks for your prompt replies.

This is only to acknowledge your postings. I am pressed for time right now I will reply in more details in a few hours when I get back

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

jyk_ausAuthor Commented:
For Peter and Jeff

Thanks again for taking the time to reply.

The purpose of this form (still WIP that needs more work) is to import exchange rates which may come in from many sources (usually banks) and in various formats, although in many cases, but not always, are Excel files that have different columns heading for dates and for exchanges rates. Some banks use the currency ISO code (AUD, USD etc) other use the full currency names and some use both. Eventually this form will produce the sql to append exchange rates into a few a ExRates tables (one table per currency that is in use)

Peter, I have no objects that have a tidal (~) in them nor do I name anything with a prefix 'msys', 'vb', 'db' , 'ac', other common syntax prefix or keywords (naturally). I do not allow the user to defines the date field as 'date'. albeit many banks name the date field that way - in such cases the user must  change the column heading manually before import can take place (I might do it for them at some stage but have thought through how)

Jeff, thanks for pointing me in the right direction; I had been with Access since Office 95 and had always known how to see the system objects but  since I converted to Office (change for a sake of change) 2007 I  wondered where did they go - you have also solved my quandary as to why the row source returned over 32750 characters, I had estimated that the sum of all my queries should be in the vicinity of half that but now I know :-). Looking at 'msysobjects' I can see now that they all relating to to combo/list boxes that I have right around; I will filter them out silently.

Thank you both for your help, much appreciated.

"I have no objects that have a tidal (~) in them nor do I name anything with a prefix 'msys'"

I didn't suggest that you did.   That's why i said what I said!
Jeffrey CoachmanMIS LiasonCommented:

To follow up on Pete's post, ...the ~ prefix is what Access adds to these "rowsources" to distinguish them from "Saved" queries...

The same with the "Msys" prefix... these are System tables. MS names them this way and makes them hidden by default.

No points for me, I was just helping out...

jyk_ausAuthor Commented:
No worries Peter, I had understood you perfectly the first time,I did not mean to convey that you had suggested that I do use such prefixes, I merely expanded on what you had said and told what I do (or not do) mainly to give you the opportunity to pick me up on errors in my approach or if I unknowingly do something wrong,  

Please bear in mind that I am not a professional programmer, I learned VBA /JS by the seats of my pants and the occasional help from sites such as this. In any event, I rather be told something I know 10 times than not being told something that I don't for a concern that I may be offended - no chance of that happening - any help I get is welcome.

As a matter of interests Jeff, I do not use the tidal in naming, particularly as a prefix, not because I knew that MS use it to hide stored RowSoures (I was clearly unaware of it) but because it was originally used by DOS to mark deleted files (yes I am that old :-).

I realise that you do not want points but as your reply was invaluable to me in may other aspects and as I cannot buy you a lunch, I feel that I must  "do something" thus you are "overruled" on that score. Apart from filleting out system objects I realise now that much more than that has to be fitter out, I am talking about, what I call "utilities objects" such as program setup data, backups temporary tables and users temporary tables of other users.

Thank you again  Peter and Jeff - I leave this question open for another couple of days and close it early next week.

best regards

jyk_ausAuthor Commented:
Thank You
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.