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

x
?
Solved

Stored Query or VBA Query

Posted on 2014-04-08
5
Medium Priority
?
939 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 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1400 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 40

Assisted Solution

by:PatHartman
PatHartman earned 600 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

927 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