QueryDef the Best?

[Excel_Kid1081]

Hello-

I have two questions that pertain to the end product I would like to achieve. I am basically creating a reporting DB that will have between 15-20 different reports in it. However, the data feeding the reports requires a few different sub queries to be created before the final output.

Question #1: Are using QueryDefs and then deleting the saved query objects after the report is completed and printed the best way to do it if I want to have all of them not visible to the user?

Question #2: I have created this UNION QueryDef and I am having some trouble debugging it. All the suporting queries were run successfully and I can see their objects in the Queries under the DB Objects and they work fine.

Sub Create_UnionCreditWts_CrossTab()
Dim db As DAO.Database
Set db = CurrentDb
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim QryDefName As String

'Name the UNION Query
QryDefName = "qryReport_Index_Credit_Weights"

strSQL = "SELECT * FROM Index_CreditWts_A UNION ALL " & _
strSQL = strSQL & "SELECT * FROM Index_CreditWts_AA UNION ALL " & _
strSQL = strSQL & "SELECT * FROM Index_CreditWts_AAA UNION ALL " & _
strSQL = strSQL & "SELECT * FROM Index_CreditWts_BBB UNION ALL " & _
strSQL = strSQL & "SELECT * FROM Index_CreditWts_OTHER " & _
strSQL = strSQL & "ORDER BY RatingOrder;"

Set qd = db.CreateQueryDef(QryDefName, strSQL)

qd.Close
Set db = Nothing
MsgBox "done"
End Sub

Thanks!!

 

[namliam]

1) Yes kindoff, but why? Any 'semi skilled' user would go find them in code.

2)
strSQL = ""
strSQL = strSQL & " SELECT * FROM Index_CreditWts_A UNION ALL " & _
strSQL = strSQL & " SELECT * FROM Index_CreditWts_AA UNION ALL " & _
strSQL = strSQL & " SELECT * FROM Index_CreditWts_AAA UNION ALL " & _
strSQL = strSQL & " SELECT * FROM Index_CreditWts_BBB UNION ALL " & _
strSQL = strSQL & " SELECT * FROM Index_CreditWts_OTHER " & _
strSQL = strSQL & " ORDER BY RatingOrder;"

Dont know for sure what is the problem... Above looks a little better, nothing more...
Also including the space in front of the Select makes sure you dont miss any spaces.

make sure all your queries have the same number of columns and the same order or columns and column types. You can force this by adding the column names to the selects instead of using *

 

[Excel_Kid1081]

fortunately for me no semi skilled users thanks for your input!

 

[namliam]

Well if you dont have semi skilled users, why not 'simply' hide the database screen upon startup?? That should "lock them out" just as well... And much less work...

did you get your second issue resolved?? About the union query not working?

 

[Excel_Kid1081]

Good point. I will try that. And yes, I got the union query to work...it was the '& _' at the end of each line. Sometimes taking a break and coming back to look at some code you can see the obvious thanks again for your help!

 

[namliam]

& _ *UGH* wonder why I didnt catch that... LOL