Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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;"
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:
qryfrmAddDriverbasCheckLic
for a combo or list box:
qryfrmAddDrivercboSelectDr
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.
I also like to use naming along the lines of:
qryfrmAddDriver
and of the code in that form uses a qry:
qryfrmAddDriverbasCheckLic
for a combo or list box:
qryfrmAddDrivercboSelectDr
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.
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 dataWe'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:
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.
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
strSQL = GetSql("qryMyQuery")
strWhere = " [ID]=" & Forms![myForm]![myIDControl]
strSQL = strSQL & " WHERE " & strWhere
set rst = currentdb.OpenReocrdSet(strSQL)
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial