Solved

Stored Query or VBA Query

Posted on 2014-04-08
5
811 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 35

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

839 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