Solved

Form Record Source

Posted on 2016-08-01
11
16 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 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 57
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 57
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 57
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

12 Experts available now in Live!

Get 1:1 Help Now