Solved

Form Record Source

Posted on 2016-08-01
11
18 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 41
Trying to open FORM in specific record !! 6 44
Report with several filters - Issue with query? 3 22
DCount Type Mismatch 2 21
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

785 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