Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 25
  • Last Modified:

Form Record Source

When creating a forms record source where would you use sql as apposed to a query?
0
DatabaseDek
Asked:
DatabaseDek
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
Dale FyeCommented:
Not sure what you mean?  SQL and query are synonymous.

When creating a forms recordsource, you would normally use either a table or a query (SQL).  Personally, I prefer to use a query, even if I'm pulling in all of the fields from the table.  I do this, and save the query with a name that looks like:

qry_frm_FormName

This allows me to quickly determine what the source is for each and every form.  If that is not the answer you are looking for, please clarify.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You'd use SQL if you wanted to construct a record source on the fly in code.

 As Dale said,  a SQL and query are mostly synonymous, but a query def does wrap up some other properties other than the SQL.

 Like him, I find it far easier to work with querydefs, but there are times when you want to construct a SQL statement on the fly and then assign it to the recordsource of a form.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
An example of that might be that you want to set a subforms recordsource, but the criteria for it is based on variables in VBA.  

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] or Forms![myForm]![myControl] is null

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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Dale FyeCommented:
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.
0
 
DatabaseDekAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
The point is that you may have created the query before the form, or that you wish to use the saved query for other forms or reports.
And in both cases, that you don't wish to adjust the SQL dynamically (example from Jim).

/gustav
0
 
Dale FyeCommented:
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.
1
 
DatabaseDekAuthor Commented:
Sorry, forgot to thank you all.

Derek
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
Dale FyeCommented:
JIm,

I knew they were saved in the mSysObjects with the tilde prefix, but did not know that they were optimized.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now