General Networking/Lan/Wan/Access queries


QUESTION: Dear Scott
I have set up my queries using:

Dim qd2f As DAO.QueryDef
If Not QueryExists("UMStep2") Then
   Set qdf2 = db.CreateQueryDef("UMStep2")
   Set qdf2 = db.QueryDefs("UMStep2")
End If
strSQL2 = "TRANSFORM Sum(UMStep1.Marks) AS SumOfMark " & _
       " SELECT UMStep1.UnionResults.StudentNo " & _
       " FROM UMStep1 " & _
       " GROUP BY UMStep1.UnionResults.StudentNo " & _
       " PIVOT UMStep1.ass In ('Ass1','Ass1Supp1','Ass1Supp2','Ass1Supp3','Ass2','Ass2Supp1','Ass2Supp2','Ass2Supp3','Ass3','Ass3Supp1','Ass3Supp2','Ass3Supp3','Ass4','Ass4Supp1','Ass4Supp2','Ass4Supp3','Ass5','Ass5Supp1','Ass5Supp2','Ass5Supp3');"
qdf2.SQL = strSQL2

I have about 6 of these queries and, at the end after I have used them in reports, I delete them.  Is this the best way or should I simply use the design view? I find that the number of queries becomes very large;
Many thanks once again, Kary

ANSWER: The only time I generate a SQL statement in VBA is if I need to include variables or data entered into a form in the SQL statement. I don't see that being done here.

I see no reason not to create this query as a stored (named) query. Stored queries as semi complied so they will run faster. Constantly creating and deleting objects puts your app at greater risk for corruption.

So I would say your way is not the best way.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott, thanks for your reply.  I do get data from my form but can include that in my
criteria row.  I have always done named queries before but thought that perhaps it would
be better to do thro VBA.
I find the long list of queries I generate quite daunting and was trying to limit it.
Thanks again for your help.

Hi Kary,
The way to limit queries is to make them more dynamic. Supply criteria via forms so the same query can be used in multiple ways. Add fields that you may not need in some reports so you can use the same query in different instances.

But I try to use Saved queries, except where I need to concatenate in values from forms.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers

Answers by Expert:

Ask Experts




Most general questions on LANs, especially home networks. Some admin and design issue.


I've been an IT Professional for more then 16 years. I've accumulated a larege store of knowledge that allows me to answer a wide variety of IT related questions.

©2017 All rights reserved.