[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Stored Query or VBA Query

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

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 41

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

612 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