Where to put/save my queries?

Hi everyone

I have been asked to create a database which will be used by several departments.
My question is about queries in general.

As someone else might make changes in the future, I want to keep it as maintainable as possible.
Should I create a query object for every (SELECT) query I do (some forms might have several)?
Is there something like a golden rule / best practise or does it not matter?
Or maybe I should ask the question another way: What would you like to see when you take over someone else's Access project?

Thanks

Massimo
Massimo ScolaInternshipAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
There is only one rule:
Ensure that users can't touch the queries.

Personnally, I tend to write queries in the code, indented of course.
Something like the Following: 
Dim sql As String
SQL = vbNullString
SQL = SQL & "SELECT column1,    column2" & vbCrLf
SQL = SQL & "       column3,    column4" & vbCrLf
SQL = SQL & "FROM   table1 INNER JOIN table2" & vbCrLf
SQL = SQL & "           ON Table1.ID = table2.ID" & vbCrLf
SQL = SQL & "WHERE  column1 > 120" & vbCrLf
SQL = SQL & "  AND  column1 < 200;"

Open in new window

Column names, criteria in the WHERE clause are aligned, and FROM clause is indented, this way the sql variable's content is easy to read in the code as well as in the execution window.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I like to see queries for each individual object where their used.   All too often, the needs of a form change and having separate queries for everything makes that simple.

 I also like to use naming along the lines of:

qryfrmAddDriver

 and of the code in that form uses a qry:

 qryfrmAddDriverbasCheckLicense

 for a combo or list box:

 qryfrmAddDrivercboSelectDriver


 sometimes though for a query that's used everywhere and has little chance of changing, I will use one query everywhere.  i.e.

 qrylupDriver

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gskTechnology consultantCommented:
please grant only read access priviledge and only on the required tables, no need to grant updat ,alter,insert priviledges if you think the users may mdify data
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
Fabrice LambertConsultingCommented:
please grant only read access priviledge and only on the required tables, no need to grant updat ,alter,insert priviledges if you think the users may mdify data
We're speaking about MS Access here, there is no such things as access priviledges.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple of additional comments:

On SQL in code:

1. You will not find it easy to maintain these.
2. SQL in code does not have a saved costing plan.  That may work for you or against you (pre-compiled queries are not always faster).
3. You do not need to use SQL in code to keep them away from users.   You can distribute a Access db so that it works only in run time mode, which disables all design capability.

 But if you do decide to work with SQL in code, do the SQL work in the query designer, then use a public function to pull the SQL into your code:

Public Function GetSql(strQuery As String) As String
 
   GetSql = CurrentDb.QueryDefs(strQuery).SQL
   
   GetSql = Left(GetSql, Len(GetSql) - 3)
   
End Function

Open in new window

 You can then do stuff like this:

   strSQL = GetSql("qryMyQuery")
   
   
   strWhere = " [ID]=" & Forms![myForm]![myIDControl]
 
   strSQL = strSQL & " WHERE " & strWhere
 
   set rst = currentdb.OpenReocrdSet(strSQL)

Open in new window


That gives you the best of both worlds.

On granting privs on queries;

1. That only applies to DB's other than Access based ones (a SQL back end for example).  With a JET/ACE DB, you can't set permissions in this way.

Jim.
Dale FyeOwner, Developing Solutions LLCCommented:
I tend to use the same technique that Jim' describes above.

Every form has a saved query object, every control with a row source also has it's own saved query object.

Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.