Join Adam Wilbert for an in-depth discussion in this video Naming conventions and best practices, part of Access 2007: Queries in Depth.
Now the user interface in Access gives us a little bit of a clue as to what type of object we are dealing with. We have got an icon here that indicates a query, we have also got an icon here that indicates a table. So let me go ahead and open up a table. We have got a DirectCustomers table. I am also going to open up our EmployeesAZ query. You will see that the structure of their query is almost identical to the structure of the table. Without the three letter code here I'd be hard-pressed to tell that this was a query and not a table. Now, we do have the interface here. It shows us a little icon and I know that this is the icon for a query and this one is icon for a table. But there are a lot of areas in Access where Access will lump all of our queries and tables together, and it won't give us any icons, and it won't give us any indication of whether we are actually looking at a query or a table.
For instance if I go up to our Database Tools and then I click on our Relationships window, we will see that Access puts all of our tables in the relationship, but it doesn't show us any icon indicating that these are tables. If I click on Show Table, and then select Query, I can add a query to our relationships, and again it doesn't show us any icon to indicate that it's a query. So without the tag saying that this is a query and that this is a table, I'd be hard-pressed to tell the difference between the two. So let's take a look at some of the tags that we will be using throughout the course. We will be using the tag tbl to indicate a table, we will be using the tag tlkp to indicate a Lookup Table which is basically a simple table that just provides some additional information.
We will be using qry, and this is a big one, for queries, rpt for reports, and frm for Forms, mcr for Macros and we can also use these codes to indicate other objects within our database. for instance cbo is a Combo Box and cmd stands for Command button. Now, let's go back into Access and see how those fits together. In the Navigation pane we can see how using these three letter codes will help us keep our database structured and organized. Now Access is perfectly happy letting you name your objects whatever you'd like. You can use long file names, spaces, capital letters whatever you want.
Access will take care of all the organization behind the scenes. But as we move deeper into queries, using these three letter codes will help us be able to read our query, and understand how they're functioning. If you think that using this type of naming convention is a little bit complex to the type of database that you'll be working with, like I said, feel free to break all of these rules. You can name your objects whatever you like and Access will work just fine. If however there is a slimmest of possibilities that your project will grow beyond a simple personal endeavor, then I would encourage you to consider putting some of these practices into place. Being consistent and deliberate in your methods will only help you out down the road.
- Naming conventions and best practices
- Working with joins and primary keys
- Using comparison operators
- Printing query results
- Creating parameter queries
- Creating calculated fields
- Using the Expression Builder
- Making conditional statements
- Appending queries
- Updating queries