Link to home
Start Free TrialLog in
Avatar of jyk_aus
jyk_ausFlag for Australia

asked on

"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

 ( User generated image

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
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jyk_aus

ASKER

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
Avatar of jyk_aus

ASKER

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
"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!
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
Avatar of jyk_aus

ASKER

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


Avatar of jyk_aus

ASKER

Thank You