Solved

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

Posted on 2011-03-07
8
246 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

815 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

10 Experts available now in Live!

Get 1:1 Help Now