Solved

Stored Query or VBA Query

Posted on 2014-04-08
5
826 Views
Last Modified: 2014-04-09
Hello

Whenever I need to display in a userform, I creat a query, save it and fetch the data from the query which had just been created.

I'm a bit concerned that Access might slow down too much if I have many queries (20+).

Is it better to retrieve data (in a userform, report) with a SQL query which is created with VBA?

Thanks for the feedback

Massimo
0
Comment
Question by:Massimo Scola
  • 2
  • 2
5 Comments
 
LVL 57
ID: 39985699
<<I'm a bit concerned that Access might slow down too much if I have many queries (20+).>>

 No worries.  Your far better off to create queries for each specific purpose and tailor the queries for exactly what you need for what your doing at the moment.

<<Is it better to retrieve data (in a userform, report) with a SQL query which is created with VBA? >>

 If you create a query in VBA in code, the costing plan is not saved and this will cause extra overhead when it's run.

  With a query def or with a SQL statement that appears as part of a record or row source (for a combo/list box), a costing plan will be saved.

Jim.
0
 

Author Comment

by:Massimo Scola
ID: 39985729
What is your opinion about the queries which are stored within the userform/report? I forgot to ask about that. Would that also create an overhead if I were to retrieve the data from there?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 350 total points
ID: 39985768
<<What is your opinion about the queries which are stored within the userform/report? I forgot to ask about that. Would that also create an overhead if I were to retrieve the data from there? >>

 In the past, they used to be different, but now they are no different than a querydef.

 Behind the scenes, any SQL that appears as part of a Rowsource or Recordsource is saved by Access as a temp query def (these appear in the querydefs collection starting with a ~ character).

So in those cases, there really is no difference between creating a query, saving it, and using the query name a the rowsource  vs simply putting the SQL statement in directly as the rowsource.

However if you create SQL statements on the fly in VBA code, then assign it to the rowsource at runtime, that SQL will need to be "costed"  (figure out the best way to run it) each time.

With the saved queries and temp querydefs, the costing plan is done once and saved.  It's not re-done unless you:

1. Change the design of the query
2. Perform a compact and repair.

Jim.
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 39986550
Terminology check (no points please) - In Access, forms are simply called Forms, not user forms.  In Excel and Word and possibly other Office products, forms are called User Forms.  I say this because you may get the wrong answers if you search for User Forms on the web since "Access Forms" and "User Forms" for other Office products are different.

PS - my preference is QueryDefs because that gives me the flexibility to reuse queries.  Of course if you change one, you need to be sure that you are not negatively impacting some other process.  My second choice is as the RecordSource of forms and reports or the RowSource of combos and listboxes.  The advantage here is that if you export an object or clone it, you don't have to find the queries that go with it also.   The only time I create embedded queries (strings in VBA) is if I need to build them on the fly because the selection criteria is so variable that it makes a QueryDef either impossible or just too complex and even then, I frequently create the Select and From clauses and save them as a querydef and then just select the querydef name and add criteria in VBA.  That also simplifies the SQL strings which can be cumbersome to build in VBA.
0
 

Author Closing Comment

by:Massimo Scola
ID: 39988165
Thanks for the information.  In my case, I will continue creating and using saved queries. There are only a few times I had to use SQL & VBA as I couldn't find another way to make have the data displayed in the form (thanks Pat :-) ) .

Massimo
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 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