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

Posted on 2011-03-07
Last Modified: 2012-05-11

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
Question by:jyk_aus
  • 4
  • 2
  • 2
LVL 77

Assisted Solution

peter57r earned 150 total points
ID: 35066934
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
LVL 74

Accepted Solution

Jeffrey Coachman earned 350 total points
ID: 35070784
<No Points wanted>

<look as if they are old failed SQL's>
Actually most are Rowsources for your various comboboxes or Listboxes, 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...



Author Comment

ID: 35076811
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.


Author Comment

ID: 35080682
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.

LVL 77

Expert Comment

ID: 35080710
"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!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35083650

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...


Author Comment

ID: 35102381
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


Author Closing Comment

ID: 35119775
Thank You

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

679 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