Solved

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

Posted on 2011-03-07
8
254 Views
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
Jacob
0
Comment
Question by:jyk_aus
  • 4
  • 2
  • 2
8 Comments
 
LVL 77

Assisted Solution

by:peter57r
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
0
 
LVL 74

Accepted Solution

by:
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, ...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...

;-)

JeffCoachman
0
 
LVL 1

Author Comment

by:jyk_aus
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

Jacob
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 1

Author Comment

by:jyk_aus
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.

Jacob
0
 
LVL 77

Expert Comment

by:peter57r
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35083650
jyk_aus,

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

Jeff
0
 
LVL 1

Author Comment

by:jyk_aus
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
Jacob


0
 
LVL 1

Author Closing Comment

by:jyk_aus
ID: 35119775
Thank You
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

860 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