?
Solved

Form Record Source

Posted on 2016-08-01
11
Medium Priority
?
27 Views
Last Modified: 2016-08-01
When creating a forms record source where would you use sql as apposed to a query?
0
Comment
Question by:DatabaseDek
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 41737276
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
 
LVL 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41737277
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
 
LVL 59
ID: 41737282
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 41737284
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
 

Author Comment

by:DatabaseDek
ID: 41737420
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
 
LVL 53

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 41737430
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41737435
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
 

Author Comment

by:DatabaseDek
ID: 41737453
Sorry, forgot to thank you all.

Derek
0
 
LVL 59
ID: 41737473
<<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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41737499
JIm,

I knew they were saved in the mSysObjects with the tilde prefix, but did not know that they were optimized.
0
 
LVL 59
ID: 41737510
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

601 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