Form Record Source
When creating a forms record source where would you use sql as apposed to a query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm usually pretty good and discerning poster intent, but I think you probably hit it on the head this morning, Jim. Guess I need to finish my coffee.
ASKER
Thank you both!
Perhaps I still am not very clear with my questions. In some forms I have sql directly in the forms control source property and some have the name of a query. So if I click the three dotted button next to control source in the properties panel sql opens as a query anyway. I can then either save as a query or just close the query and the sql just changes in the forms property panel.
So what is the point in saving as a query when sql seem to do the trick. I have never tried it with any complex query but simple stuff seems to act in an identical way.
Perhaps I still am not very clear with my questions. In some forms I have sql directly in the forms control source property and some have the name of a query. So if I click the three dotted button next to control source in the properties panel sql opens as a query anyway. I can then either save as a query or just close the query and the sql just changes in the forms property panel.
So what is the point in saving as a query when sql seem to do the trick. I have never tried it with any complex query but simple stuff seems to act in an identical way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you leave a SQL string in the RecordSource property of the form, then that SQL will have to be optimized whenever the form is run. Given the speed of todays processors, this will probably not cause any significant difference between that and saving the SQL in a querydef. When you save it is a querydef, Access (Jet/Ace) does not need to reevaluate the query plan making it slightly faster, but as indicated above, probably not noticeably different.
I just prefer to have all of my the queries my application is using stored as querydefs. Even when I'm writing SQL on the fly, I frequently have a querydef to which I apply my dynamic SQL strings.
I just prefer to have all of my the queries my application is using stored as querydefs. Even when I'm writing SQL on the fly, I frequently have a querydef to which I apply my dynamic SQL strings.
ASKER
Sorry, forgot to thank you all.
Derek
Derek
<<if you leave a SQL string in the RecordSource property of the form, then that SQL will have to be optimized whenever the form is run. >>
Actually that was changed a while back and actually broke some things for me<g>.
In the past you could take advantage of the fact that querydefs did have a saved plan and SQL statements did not. This came in handy when data in tables was quite dynamic and the costing plan would be effective some times and not others..
But then Microsoft made a change and now any record source for a form or row source for a list or combo box is saved as a hidden query def. If you enumerate the querydef collection, you'll find them (they all start with a ~ (tilde)).
<<So what is the point in saving as a query when sql seem to do the trick. I have never tried it with any complex query but simple stuff seems to act in an identical way.>>
Well a query def object does have other things associated with it, such as ODBC timeout or "Use Transactions". So if you don't use it, it's a little more work in code.
But if you don't have need for any of that, then at this point there is no difference. Actually I take that back. Querydefs run in a different context then your code, and one place where you can get caught is with the random function.
But for the purposes of your question, they would be identical at that point.
Jim.
Actually that was changed a while back and actually broke some things for me<g>.
In the past you could take advantage of the fact that querydefs did have a saved plan and SQL statements did not. This came in handy when data in tables was quite dynamic and the costing plan would be effective some times and not others..
But then Microsoft made a change and now any record source for a form or row source for a list or combo box is saved as a hidden query def. If you enumerate the querydef collection, you'll find them (they all start with a ~ (tilde)).
<<So what is the point in saving as a query when sql seem to do the trick. I have never tried it with any complex query but simple stuff seems to act in an identical way.>>
Well a query def object does have other things associated with it, such as ODBC timeout or "Use Transactions". So if you don't use it, it's a little more work in code.
But if you don't have need for any of that, then at this point there is no difference. Actually I take that back. Querydefs run in a different context then your code, and one place where you can get caught is with the random function.
But for the purposes of your question, they would be identical at that point.
Jim.
JIm,
I knew they were saved in the mSysObjects with the tilde prefix, but did not know that they were optimized.
I knew they were saved in the mSysObjects with the tilde prefix, but did not know that they were optimized.
Yes. Microsoft did it as a performance enhancement. What they found is that many were using SQL statements and didn't understand a re-costing was taking place every time. So they added these.
But really all they need to do was tell everyone to save it as a query def. Little more work, but at least that way it would have preserved the option to force a re-cost every time and it meant adding nothing to Access, nor having something like this hidden from view.
But now, there's no way to do that easily. I suppose you could locate the relevant temp querydef and delete it, but I've never tried it.
I've only had a handful of occasions where JET would really mess up costing a query depending on a table being full or not.
Jim.
But really all they need to do was tell everyone to save it as a query def. Little more work, but at least that way it would have preserved the option to force a re-cost every time and it meant adding nothing to Access, nor having something like this hidden from view.
But now, there's no way to do that easily. I suppose you could locate the relevant temp querydef and delete it, but I've never tried it.
I've only had a handful of occasions where JET would really mess up costing a query depending on a table being full or not.
Jim.
In code, you can construct a SQL statement that includes those values, but you can't do that directly in a querydef (well with tempvars you now can - so maybe not a great example<g>).
Another; the SQL is based on options that the user chooses in a filtering form. Rather than try to account for everything in the query like this:
=Forms![myForm]![myControl
You would simply code for the SQL by checking the control:
If NZ(Me.myControl,"")<>"" then
strSQL = strSQL & " AND [ID] = " & Me.myControl
End If
and end up with a cleaner SQL statement.
Jim.