Solved

Stored Query or VBA Query

Posted on 2014-04-08
5
778 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:mscola
  • 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:mscola
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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now