10 things you should know about Access Databases?

(1) You don't need to have Access installed to use an existing Access database. There is a "run-time" edition of Access which can be distributed with a database, if the person who developed it has the "Access developers extensions". Note that you cannot make any design changes to the database with the run-time edition.

(2) Creating a calculation involving any field which holds a Null value will always result in the answer being Null. If you know that a field may be null, use the NZ function to replace the Null with something more useful. NZ has two arguments - the first is the field which may (or may not) be null, the second is the value to use instead of Nulls. An example might be nz([QuantityOrdered],0)

(3) When creating tables, always provide each fields "Caption" property. This is the "user-friendly" field name for use in forms and reports. Creating the caption in the design of the table saves you having to replace the field name with something more user-friendly in each of your forms and reports separately.

(4) Although they may be tempting, avoid the use of Sub-Datasheets in tables. If you have a subdatasheet, it forces Access to run a query looking for related records every time you look at the records in the main table, which can have a major impact on performance. To turn off Sub-Datasheets, in Access 2000 - 2003 go to the Format menu --> Subdasheet --> Remove. In Access 2007, go to the Home tab of the Ribbon, within the Records section click "More" --> Subdatasheet --> Remove

(5) If you have a field in a table which is of Number data type and is a foreign key (meaning that it's related to a primary key in another table) remove the Default Value of zero, which Access automatically inserts. It can cause problems if you forget to change the default, as you are unlikely to have a matching record (with an ID of zero) in your parent table.

(6) If you don't ever use macros, it can be an annoyance to have to choose "Code Builder" every time you click the builder next to an event in the properties of an object in a form or report. To have Access default to always taking you to the code window (and creating the Sub / End Sub for you) go to Tools --> Options --> Forms/Reports tab and select "Always use event procedures" (Access 2003 and earlier) or Office Button --> Access Options --> Object Designers --> Forms/Reports section --> Always use event procedures (Access 2007)

(7) It can be frustrating to have the VBA Editor telling you of code errors which are not really errors. For example, if you type "MyVariable = " and then try to copy a value from elsewhere, as soon as you leave this line of code, you'll get a dialog box which reports "Expected: Expression". In order to silence this nagging dialog box, within the VBA Editor, go to the Tools Menu --> Options --> Editor Tab and deselect "Auto Syntax Check". The line with the (potential) error will still go red, but you won't get the dialog box.

(8) There's a quick and easy way of exporting a table or a query from Access to Excel. Open Excel (with a blank workbook showing) and then in Access simply drag the icon for your table or query from the Access window over the Excel button on the Windows task bar... wait for Excel to activate and drop the icon in cell A1 (or wherever you want your data to begin).

(9) If you've spent a long time formatting the perfect form with your choice of fonts, colours and other design elements, it can be frustrating to have to do it all again for other forms. But the good news is, you don't have to! Open the form which you're happy with, then (in Access 2003) go to design view for the form, then select the Format menu --> Autoformat --> click "Customise" --> Select "Create a new AutoFormat based on the Form ". In Access 2007, open the form in design view, then choose the Arrange tab from the Ribbon, click the arrow below AutoFormat --> AutoFormat wizard --> Customize and then select "Create a new AutoFormat based on the Form "

(10) The single biggest gain in your productivity with Microsoft Access can be had by coming on the PTR Access courses....


Access Database Course Schedule