Microsoft Access hints and tips

These tips are for users and developers working in Access 97, Access 2000, Access XP and Access 2007. Microsoft Access is an easy tool for casual use at the desktop but is also a powerful relational database. Careful database design is needed to make the best use of it and we hope that these tips will help you get the best from Access.

These pages are not an Access tutorial, you will need to know something about the language to get the most benefit from the tips. They are provided for your guidance in database development rather than as solutions to particular problems. Refer to the Access Help system or MSDN for more detailed information about the commands used.

Hints and tips are listed here alphabetically under seven categories:

Code | Data | Forms | Miscellaneous | Queries | Reports | Visual FoxPro equivalents

Code and programs

• Class module.
• Class module example.
• Common Dialog control to choose a file name in Access.
• Constants in Access.
• Date functions in Access.
• Datestamp as a filename.
• Editing VBA method code in Microsoft Access.
• Error log to improve debugging.
• File and folder commands to match Visual FoxPro.
• Generating unique keys in VBA.
• Logical functions in Access.
• Macro arguments in Access 2007
• Numeric functions in Access.
• Password generator for Access VBA.
• Random numbers from Visual Basic.
• Text handling functions in Access.
• Wrap the calls to MsgBox.

Data and mdb files

• Access 2007 fields with multiple values.
• Backup an Access database.
• Choose the right tool - Access or Excel.
• Compacting an Access database.
• Compacting an Access 2007 mdb database file.
• Create a custom toolbar in Access.
• DAO in Access 97.
• Database Support
• Generating primary keys in VBA instead of using AutoNumber
• Linking tables from another database.
• Locking with an ldb file.
• ODBC connection to a SQL Server database.
• OLEDB connection string.
• Splitting an mdb database into code and data components.
• Upsize Access to Microsoft SQL Server.

Forms

• Centre controls on Access forms.
• Common Dialog control to choose a file name in Access.
• Control Groups in Access 2007 Layout View.
• Custom Access toolbars.
• Design a form to make data entry easier.
• Fonts for data entry
• Form design without using a wizard.
• Hide the Access window and just show a form.
• ID codes for CommandBarButtons in an Access toolbar.
• Lines on Access forms.
• Linking two Access listboxes.
• Make a quick backup copy of a form.
• Multiple parameters in an Access form.
• Navigation buttons on an Access subform.
• Passing a parameter to a form in Access.
• Pictures on Access forms.
• Search options.
• Shortcuts for entering data into an Access form or datasheet.
• Switchboard forms in Access - Part 1
• Switchboard forms in Access - Part 2
• Switchboard forms in Access - Part 3
• Switchboard forms in Access - Part 4
• Tabbed forms in Access 2007

Miscellaneous

• Access 2007 file extensions.
• Access XP.
• Choose the right tool - Access or Excel.
• Cut, Copy, and Paste.
• Links to other Access pages.
• Migrating to Access XP.
• Multiple versions of Access on the same PC.
• Options at startup.
• Options in Access 2007.
• Startup options in Access.
• Top ten tips.
• Trust Center in Access 2007.
• Turn off the Office Assistant.
• User name in VBA.
• Wild cards in searches.

Queries and SQL

• Faster Access queries.
• Outer joins using Access SQL.
• Queries with "In" and "Between".
• SQL date formats.
• Subqueries in Access SQL.
• Top records in an Access query.
• Types of Access Query - Modify, Delete and Create Table queries.
• Types of join in SQL.

Reports

• Straight lines on Access reports.
• Simple way to make a backup copy of a report or form.

Visual FoxPro equivalents to VBA commands

• Comparing Visual FoxPro and Visual Basic.
• Arrays
• Case and Select Case structures.
• Change characters within a string.
• Change case of a string.
• Comments
• Concatenate strings.
• Constants
• Convert from one data type to another.
• Find characters within a string.
• For loops.
• Format text and numbers.
• If and Else
• Procedures and functions
• Repeat characters in a string
• Scan through a FoxPro table
• Substrings
• Text delimiters.
• Trim spaces from a string.
• Variables
• While loops

Training courses

If you need to know more about working with databases in Microsoft Access then Alvechurch Data run a series of workshops, tutorials and training courses to help you.