General Networking/Lan/Wan/Access queries

Advertisement


Question
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")
Else
   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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
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.
Kary

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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.

©2016 About.com. All rights reserved.