(Last updated 2010-09-27)
These are, of course, just my opinion. Feel free to disagree with me.
How do you know what version of your app a client/user is running? I could've used an MDB property to contain this data but you can only view and update this programmatically. So I have a table in the FE and another in the BE which I update. I also display this information on the main menu and the About form. If I have a change in the BE format, ie new tables, fields or indexes I use the BE version number to run some code which does the appropriate updates. Updating an Access Backend MDBs structure using VBA code
In some apps I display the number of records
in the main table, i.e. number of volunteers or number of vehicles. This really
saved my butt once when I converted a file which had been emailed to me several
days ago rather than the one they just emailed to me.
You shouldn't assume your users will always use mm/dd/yy format. Especially if your app can be used outside your U.S. or Canadian organization. So see Date/Time: Return Dates in US #mm/dd/yyyy# format for queries. Also note the time format in there as well. I also like defining the regional date format on my system as dd/mm/yyyy just to help locate these problems. If I was more disciplined this wouldn't be necessary. <smile>
What Microsoft forgot to put in the code behind the wizard generated logic for most command buttons on bound forms was the following lines to save the record.
If Me.Dirty Then _
Me.Dirty = False
This is placed immediately after the On Error Goto ... line.
(As a matter of syntax I prefer to have any single line If statements continued on the next line so it's easier to pick out such conditional statements.)
In the Command Button wizard it's the Form Operations and Report Operations generated code which is particular susceptible to this problem.
Examples of these kinds of command buttons are ones that call other forms or reports. Any changes you've made to the data will likely not be displayed on the other forms or reports. If you've just added a record you will likely get a syntax error in the Where clause on the form open but that's easy to locate and figure out.
What is particularly of concern though is that occasionally you have some field level validation such as a required field or a foreign key which hasn't been entered but defaults to 0. Access will happily close the form and *NOT* save the record. Without giving you a message. The explicit save of the record will generate an error message for the user allowing the users to correct the data.
The below statement was what I always used in Access 2.0 and Access 97 but it
appears to not work in Access 2000 and newer if the record doesn't require
saving.
DoCmd.RunCommand acCmdSaveRecord
I could put in error handling but why bother.
I always want to be told whenever I have a variable I haven't declared. Because it quite likely is a typo.
I also proper case the variable name. For example "lngTransHeaderID". Should I misspell the variable name, then once I hit the space bar, and the VBA editor does not change the case of the variable name from lower to proper case, then I also know I made a typo without waiting to get a compiler error.
For DAO I very much prefer to use Currentdb.Execute strSQL,dbfailonerror command instead of docmd.runsql.
The ADO equivalent would be
CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText
where strCommand is the SQL statement, lngRecordsAffected is a Long
variable in which ADO returns the number of records affected, and adCmdText
identifies strCommand as a text command rather than, for example, the name
of a table, view, or stored procedure.
If you're going to use docmd.setwarnings make very sure you put the
DoCmd.SetWarnings True
statement in any error handling code as well. Otherwise weird things may happen
later on especially while you are working on the app. For example you will no
longer get the "Do you wish to save your changes" message if you close an
object. This may mean that unwanted changes, deletions or additions will be
saved to your MDB.
One difference though is that docmd.runsql supports the Jet Expression
Service which allows you to have expressions using form
controls and such. However I always change these to values within the
string that I pass to the Execute command anyhow.
If you don't put error handling in your routines then Access can produce the message "a run-time error has been encountered" and it will exit your app. Without telling you or your users what the error really was.
At the top of your subroutine/function place the following line:
On error goto tagerror
At the bottom of your subroutine/function place the following lines:
Exit Sub
tagError:
MsgBox Err.Description
Exit Sub
Resume
End Sub
Why is Resume in the code when it will never be executed?
This requires use of the NotInlist event as documented at http://www.mvps.org/access/forms/frm0015.htm. I prefer my page at Using NotInList in a combo box. Note that you want to use this option as appropriate in your application. For example there is likely no need for a list of states/provinces or countries to be updated on-the-fly. Also you may not want parts counter people adding customers without having the customers approved by the credit department.
Minimize the amount of code between the .AddNew/.Edit and the .Update lines. This will minimize the chances of corruptions. Also open the table using the appropriate read-only, update or append only options. The performance hit may be negligible but in complex code it will help and life will be easier should decide to upsize to SQL Server.
When opening a recordset why use the following each time?
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Instead, in a form which is opened upon start and which always stays open, such as a Global Options form, open a global database variable and reuse it everywhere.
Compact both your front end, on your system as the developer, and the backend on a regular basis. Not that I don't trust the compact process but I'd never compact without keeping a copy of the original MDB. In other words don't compact from within the MDB. See Backup, do you trust the users or system administrators? for more details.
Now how do you define regular? I'd say weekly. Quite possibly daily if there is hundreds of records being inserted on a daily basis. I know one client who are adding a thousand records per day or more don't even compact weekly.
Where feasible of course. This will lessen any problems should the users upgrade their version of Outlook, Word, Excel, ... without telling you.
This is almost a joke but not quite. If you're designing the tables and aren't sure if you need to add another table with a one to many relationship then you probably do.
One simple example is telephone numbers. Suppose you allow for four telephone numbers? What happens if the person has five? Or six? Do you fix the first phone number to be residence, the second to be business, third fax and forth cell phone? What if the person has a pager? What if someone wants to search by phone number because they see a phone number on their caller ID but no name? Do you then write a query which searches all four or five or six columns of phone numbers? What if they have a satellite phone as well as a cell phone?
Far better to have a separate table containing the phone number on a one to many relationship to the person or business. But how do you the decide what kind of telephone numbers are allowed? Ie residence, business, fax, cell, pager or satellite phone? Put these in another table.
Then you reference the phone numbers of a person using a subform. Within that subform you reference the type of phone using a combo box.
So now instead of using a person table with four columns of phone numbers I've now added two tables, a subform for the persons phone numbers and a form for the type of phone. More work you say? Yup. But this is a much more accurate representation of real life.
I'm embarrassed. Thanks to fellow MVP John Viescas coauthor of SQL Queries for Mere Mortals for pointing out this error on my part. The book is highly recommended by several fellow MVPs.
Users have been complaining of a slow process that they frequently use. Worse they do many of these at one sitting. Well it turns out that a bad habit of mine made this process take at least eight times as long. I was using right joins in a query when I should've been using equal joins in this particular situation. One of those tables has 200,000 records in it which makes this process that more inefficient.
Now frequently you can't avoid right joins as data may not exist in the other table. An example in Northwinds might be an order without a shipper because the customer is just down the street and decided to pick up the order.
[ Access Tips | Access | Main ]
Auto FE Updater
distribute new and updated Front End databases to your users with several mouse
clicks.
Granite Fleet Manager
- the best designed fleet maintenance tracking and
management system available
| Comments email Tony | Search | Contact | Tony's Blog | Privacy Policy | Table of Contents |
Website copyright © 1995-2013 Tony Toews |