Where to put/save my queries?

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;" 

Select all 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.

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

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.

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

Select all 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)

Select all 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.

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