Continuing our series of Tips – Tricks and Guidelines with Microsoft Access shares some additional items for this month. The ongoing articles will continue to focus on the tables within an Access database.
Using tables for drop down lists?
The tables within an Access database are used to store all the database information. This is the fundamental purpose for all the data within the database. But what other reasons would there be to store other components of the database?
Throughout most databases, there are choices for drop down lists in forms. For example, a customer table will store a field such as a state within the United States. Another example would be a specific product that a company is selling or manufacturing or another example would be a table to store employees, etc.. There is an option in a form called a value list, but to better manage the choices, a separate table should be used to store the specific items that are contained in the list.
To ensure that each record has the proper value, the table of options should be the primary source of the value. You would not want the end user to “make up” values on the fly as this could lead to entering inconsistent values for whatever the reason.
The employee table as noted below may have the following design.
As noted, it makes sense to set the status of each employee to “Active” as these are the employees that will be included in the drop-down list. By not deleting them, then they are still available for future use.
Tip – Naming the actual dropdown list table a proper way can be helpful. For example, naming all tables for drop down lists starting with “TblDrp……” will store them all together in the database window as noted in the below figure:
Validation of data when entering data into the database at the table level
Along with drop down tables forcing accurate data into tables, another method within the table itself is setting a validation rule. This is done in the design of the table inside the field properties itself.
For example, to make sure an accurate shipping date in the future is entered, you can use a validation rule to control this by doing the following.
Validation Rule:[ShippedDate] >=Date()
Validation Text: The shipping date must be the current date or a date in the future.
See the figure below
When the validation rule is “broken”, then the following message appears. See below figure.
These are just a few time saving tips when using Access tables. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.
In our previous blog, we highlighted some tips and tricks within Microsoft Access. In this article, we highlight some additional tips and tricks when using Access forms. The below features are available in Access 2016 and higher.
Viewing a form in Layout View
When using Access forms, the user tends to either be in the “Form View” or “Design View”. The design view allows for physical layout changes of the form. The form view is primarily used to enter data or run reports via command buttons.
However, there is another viewing option within Access called the “Layout View”. The layout view is only available for use with tabular forms. The tabular view is a combination of design and form view. As noted in Figure 1, a nice option is that the user can select a form control while seeing the actual data. The user can then resize or move a control while viewing actual live data. This saves time by not having to keep switching between the form view and design view.
Conditional Formatting Option
Conditional formatting is often dominated in use in Microsoft Excel. However, Microsoft Access also has this feature within Access forms and reports. The idea behind conditional formatting is that a field or fields meets some condition(s) and then some sort of formatting can be done on the control.
For example, in Figure 2, if the quantity field in the form is greater than 100, the control color would be set to bold with the color red. Another option, and there are several, is if the field has the focus on the form, the background color can be changed to a certain background color. This condition is beneficial when tabbing through a form to know which field has the focus.
Setting the Default Formatting for a Specific Control
Changing form control formatting can be time consuming. When creating form after form, you may tend to use the same formatting features. Access has default settings, but these are set at the program level. Altering the field properties may involve making many changes to that specific control. Having to do this repeatedly, can be time consuming if the form has several controls to change.
An option buried in the formatting bar as noted in Figure 3 is the “Set Control Defaults” option. The process to use this feature is first formatting a control to the way the user wants it to look. For example, you may want to have a field that has the sunken affect, using the arial font and is bold.
Once the control is formatted the way the user desires, select the control and then select the “Set Control Defaults” option. Then, going forward all field controls that are added to the form are then formatted in the with the same features, reducing the steps of formatting itself.
These are just a few time saving tips when using Access Forms. If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.
All programs seem to have some sort of time saving tips or special shortcuts and Microsoft Access is no exception. The casual user may not be aware of many of these tips, so at Arkware, we have shared a few query tips and tricks.
Top Values Setting
When in the design of any query, there is a setting to filter out the top records. The top values setting provides several different options. One option is to return a certain percentage of data. If you want to know the top 25% of your highest price orders, you just select the 25% option from the list. The important part of this feature is to always have the data in the proper sort order. The drop-down list has some pre-defined choices, but it allows any values entered.
It is often difficult to see everything in any part of a query when typing in the criteria or field name area. A keyboard shortcut of “Shift + F2” will bring up the zoom box. The zoom box allows you to see everything in one separate window.
Datasheet View button versus Run Button
When creating a standard query, there are two choices to see the query results. You can select either the datasheet button or the run button. However, when creating action queries (queries that perform action on records), using the run button will carry out the query. Before you run a delete or update query using the run button, you may want to see what or how many records are going to be updated or changed. Selecting the datasheet button will allow you to do this. Once you are confident of what records will be updated, then the run or action query button can be used.
ODBC Timeout Setting
Microsoft Access allows you to connect to other data sources such as a SQL Server database. Depending on the design of the SQL Server database and the number of records that are retrieved, it can take some time to retrieve those records.
The default ODBC (ODBC stands for “Object Database Connectivity) timeout setting is 60 seconds. This is the time it will take for the query to return data results. If it takes longer, the user will receive a time out error. A tip for this setting is to enter the value of zero in the setting. By entering a zero, the time out is set to unlimited. This means the user will not see a timeout error if it takes longer.
Parameter Query Setting
Microsoft Access offers many options for entering query criteria. One option is the parameter query option. This option prompts the user for specific data input. For example, you can have the option prompt for a specific data range by using the Between [Enter the start date] And [Enter the end date].
Sometimes you want to see all the records for a specific situation. For example, if you do not know the starting or ending dates for example, you may be excluding some records.
The tip for this feature is to add a statement at the end of the criteria. As noted below, the way to use this feature is once the query prompts the user to enter the date range, but not entering a date for the start and end date prompts, it will return all the records from the select query, and in this case all dates.
Hopefully, these tips will help you in your work with Microsoft Access. If you need help with your project or do not know where to begin in the design of any database, please reach out to Arkware for assistance.
Most users of Microsoft Access have, at some point, encountered the message that their database has been corrupted or placed in an “Inconsistent State”. You will see a dialog with the message: Microsoft Access has detected that this database is in an inconsistent state, and the software will attempt to recover the database.
Since my start with Microsoft Access 25 years ago, the frustrating aspect of this error is that there isn’t a definitive reason why the database became corrupted. Error messages in a variety of other software programs are much more descriptive on what to do to prevent violating the software rules. In Access, for example, there are characters that are not allowed when naming fields in a table. If you choose a character that is not aligned to the rules, the message that follows is clear. Select the Help button and then follow the rules displayed regarding table name rules.
So, what does one do regarding database corruption in Access?
First, it’s important to know that most Access databases become corrupt at some point in time. I’ve found the most proven methods to deal with corruption are not to waste time trying to determine why the database got corrupted, but rather focus on methods that prevent the database from getting corrupted in the first place.
Listed below are some things that Arkware consultants do that have helped reduce database corruption significantly:
- Split your database into a front-end and back-end (two separate files). All the database tables are in the back-end database and each user has their own copy of the front-end database on the desktop or local hard-drive. The front-end database contains all the database objects (queries, forms, reports, and macros/VBA). Each PC has the same folder structure such as (C:\WorkingDatabase\DatabaseName). Using the Linked Table Manager, link the tables from the back-end database to the front-end database. Access has a database splitting wizard that can assist with splitting the original database into the front-end and back-end databases. Look for a future Blog entry on using this wizard.
- Force the database to be shut down at close of business or other specific downtimes. This is done because users sometimes leave the system open and the unattended Access connection may get disconnected from the network, which can cause corruption. The code can be placed on the “OnTimer” event of any database form that remains open. The best database form to use is a main switchboard form. Simply add DoCmd.Quit to the VBA code to close the system down at a certain time of the day (ie 2:00 am).
- Make sure all users are using the same version of Access on all computers.
- Create a MS-DOS command batch file that copies a new front-end database to the local C:\ drive of the user’s computer. This is not mandatory, but it does ensure that everyone receives a clean copy of the local front-end database.
- Do not have tables from other Access databases linked to your main database that are using different versions of Access.
- Disable Access auto-correct feature. This can be done by selecting File > Options > Current Database. Under “Name Autocorrect Features”, deselect “Track Name Autocorrect”
These are some of the main preventive measures to guard against database corruption. If you are struggling with database corruption, reach out to Arkware and we can assist you in achieving a more stable environment for your Microsoft Access database.