A function that implements a query

Question

I have some parameterized queries in Access 2010. They are often used in VBA functions having the same parameters. In such cases, I give the query and function the same name. The following is such a function (from my actual project):

Public Function get_assignments(e_id As Long, yr As Integer, wk As Integer) As DAO.Recordset
 Dim db As DAO.Database
 Set db = CurrentDb
 Dim qd As DAO.QueryDef
 Set qd = db.QueryDefs!get_assignments
 qd.Parameters![e_id] = e_id
 qd.Parameters![year] = yr
 qd.Parameters![week] = wk
 Set get_assignments = qd.OpenRecordset
 qd.Close
End Function

Both the query and function are named get_assignments and things work fine.

Am I asking for trouble? While I don't usually add "type" suffixes to names, I could rename the query as get_assignments_qry or even rename the function as get_assignments_fn. What's best?

Feel free to give feedback on any aspects of this code.

Answer 1

Having different types of objects with the same name can be confusing. In Access it is a established practice to use prefixes for stored objects and sometimes also for functions.

I use these prefixes:

• frm for a normal data entry form bound to a table or query.
• fdlg for modal dialog forms typically having "OK" and "Cancel" buttons.
• qfrm for queries used as record source of a form.
• qrpt for queries used as record source of a report.
• qsel for other select queries.
• qupd for update queries.
• qdel for delete queries.
• qapp for append (insert) queries.
• qcbo for queries used as row source of ComboBoxes.
• tbl for main tables.
• tlkp for lookup tables (countries, address-types etc.)
• rpt for reports.

and so on ... but not for sub procedures or functions which I prefer to identify by a speaking name only.

Often I have objects related to the same table like tblCustomer, frmCustomer, qrptCustomer, rptCustomer. If you look at the list of all your queries, you know immediately that qrptCustomer is used in a report. If you sort the objects by name, these prefixes have the advantage of grouping objects of the same kind together.

I make the distinction between two types of sub-forms (or sub-reports): sub-forms used at different places get a prefix fsub (e.g. fsubHeader), sub-forms used in one main form start with the full name of the main form. Then, after a underscore character, I add the name of the child entity. That way they appear just below the main form in lexical order (e.g. frmCustomer and frmCustomer_Contact).

I also always use the singular (tblCustomer, rsCustomer, frmCustomer, rptCustomer...). I call the part "Customer" the entity name and I have a lot of VBA code accepting the entity name as argument and performing the logic based on the naming conventions.

---

I would call the query qselAssignments and the function GetAssignments or GetAssignmentsRecordset.

But this is a personal preference. Find your own style and stick to it!

• Martin F: Thanks. See my comment to ThunderFrame answer. But i do like sel_assignments for query and get_assignments for function.
• Olivier Jacot-Descombes: A query can be based on queries as well as tables. If you don't have prefixes, it makes it hard to distinguish the two. I make the distinction between two types of sub-forms (or sub-reports); sub-forms used at different places get a prefix fsub (e.g. "fsubHeader"), sub-forms used in one main form are prefixed with the full name of the main form. That way they appear just below the main form in lexical order (e.g. "frmCustomer" and "frmCustomer_Contacts"). If you have different kind of objects relating to the same entity (see my Customer example) how do you name and distinguish them?
• Martin F: Accepted this ... espec. for the idea about naming sub-forms.
• RubberDuck: But what happens to your naming scheme when I want to use a qrpt as a data source for a form?
• Olivier Jacot-Descombes: A qrpt is the data source of a report and a qfrm is the data source of a form. It is always better to duplicate a query, even if the SQLs are identical. Assume that two years later, you have to make a change to the report. Say: You want to dismiss all the the records that have a flag "archived" set. This would break the functioning of your form. But will you remember that you are using the same query in the form as in your report?

Answer 2

Namespace scope and pollution is often a problem in programming, and when reading code, if there are two usages of a particular identifier, a reader may not understand exactly which one you mean, and the complier may have different scope rules than you expect, so it is worthwhile to not re-use names.

In this particular case, consider using query instead of get in the public API

Public Function query_assignments(e_id As Long, yr As Integer, wk As Integer) As DAO.Recordset

• Martin F: First impression: great idea. But i think i prefer fetch_assignments for the function name. (Naming it with the word "query" is rather at odds with the intention of distinguishing it from a query!)
• rbp: that's exactly why I wrote 'consider'

Answer 3

As rbp says, while the shared name between function name and query name might be intuitive in your mind, and might work under DAO/Access, it might not be intuitive to users that inherit your code, and it might cause other data access libraries or SQL/VBA wizards to get confused by the similar names.

Picture a scenario where you do a simple Find and Replace, because you've changed the query name. You, or your successor, will have to know about, and think hard about the best way to complete the Find/Replace without inadvertently replacing the function names too.

Although modern versions of Access make viewing object categories a little easier, as a database grows, you can end up with hundreds or even thousands of objects. And while the auto-rename feature has existed for a while, it isn't always reliable or accurate, in my experience. So, it pays to settle on a naming convention early in your database's development, as renaming tables and queries later can be troublesome.

Following conventions can be useful in this area, but as long as you are consistent, it should be obvious to new users, reasonably quickly, how the naming conventions work.

Also, prefixes tend to work better than suffixes, as they naturally group similarly purposed objects together when sorting by name.

For example, I like to prefix all of my queries with a "q" to distinguish them from table names in my other queries. I then tend to follow the q with 2-3 letters that identify the type of query, or that identifies the type of object that will use that query.

For example, for queries that are essentially a view based on a table, but that filter out the disabled records, I might have a table called Employees and a query called *qvwEmployees" that is just a view of active employees, and then for a query that gets employee names for a form, I might have a query called qfrmEmployeeNames which gets employee IDs and employee names in ascending order from the qvwEmployees query.

Here are some examples of suggested query prefixes, but you can use your own convention for queries, and even for other object types.

'qvw = Query View of Table(s)
'qdel = DeleteQuery
'qupd = Update Query
'qmak = Make Table
'qapp = Append Query
'qfrm = Query used bu Access Form
'qrpt = Query used by Access Report
'qlk = Query used by a Lookup
'qxl = Query used by Excel user-defined-function

And finally, your code would be more efficient if it used With..End With blocks

Public Function get_assignments(e_id As Long, yr As Integer, wk As Integer) As DAO.Recordset
 Dim db As DAO.Database
 Set db = CurrentDb
 'Here's the queryname with a q-prefix
 With db.QueryDefs!qget_assignments
 .Parameters![e_id] = e_id
 .Parameters![year] = yr
 .Parameters![week] = wk
 'This line uses the incorrect function name, as per my comment on original question.
 'Set get_assign_projects = .OpenRecordset
 Set get_assignments = .OpenRecordset
 .Close
 End With
End Function

• Martin F: I generally hate prefixes-for-the-sake-of-prefixes. The only two exceptions in my Access project: zub_ for sub-forms and lcl_ for local linked worksheets -- used, as you say, to group in alphabetic listings. I often use the exact same query for lookups, forms, views, etc, so see no possible special prefix.
• Martin F: The wrong fun name was merely a typo ... now fixed, thanks.
• ThunderFrame: I also like to use the "zz" prefix for objects that I know are safe to delete. And "yy" for objects that I think are safe to delete (but need to regression test before marking them zz.

Answer 4

In a situation like this I would probably drop the get_ from the query. You already know it is a query, and you are just specifying what the query is. In this case it is a query for assignments. The function reads properly as with a get_ prefix, so I wouldn't change anything there. Take a look at how it would read with my suggested change:

Public Function get_assignments(e_id As Long, yr As Integer, wk As Integer) As DAO.Recordset
 Dim db As DAO.Database
 Set db = CurrentDb
 Dim qd As DAO.QueryDef
 Set qd = db.QueryDefs!assignments
 qd.Parameters![e_id] = e_id
 qd.Parameters![year] = yr
 qd.Parameters![week] = wk
 Set get_assign_projects = qd.OpenRecordset
 qd.Close
End Function

• Martin F: Thanks for the suggestion, but i already have several non-parameterized queries having names like assignments. I originally chose to add the get_ prefix to help distinguish the paramaterized ones.