Allen Browne's Database and Training

Allen Browne's Database and Training

no longer accepting clients

Welcome to Allen Browne's tips for Microsoft Access

Where to ask for help

For a list of places where you can post questions, see Forums on our Links page.

Brief tutorials and help for novices and database developers. Examples and solutions to implement. Bugs and traps to avoid. Sample code and utilities to download. All free.

This is literally an FAQ. It has grown in response to the most frequently asked questions in Access newsgroups, for 16 years. Each page explains the most efficient solution that works completely within Access, as simply as possible.

Re newer versions, see Converting to Access 2007 or 2010.
Examples have not been tested in versions beyond Access 2010.
Articles marked MS are quoted by Microsoft (on the Office website.)

Allen is no longer accepting database clients. He's blogging on a different topic at https://allenbrowne.blog

Search this site

Tips for Casual Users

If you don't know how to write code, these may help.

Table Design

Forms

Reports

Expressions

Tips for Serious Users

Solutions for developers.

Forms

Combo Boxes

Reports

Queries

Relational Design

Tips for Programmers

Coding examples for VBA (Visual Basic for Applications.)

Solutions (VBA)

Functions (VBA)

  • MinOfList(), MaxOfList(): Get the min/max of a list of values Access 97 and later
  • Soundex(): Fuzzy matches - find names that sound alike Access 95 and later
  • Age(): Calculate a person's age, from date of birth data Access 95 and later
  • Text2Clipboard(), Clipboard2Text() Copy to and from the Windows Clipboard Access 95 and later
  • TableInfo(), FieldTypeName(): List the names, types, descriptions of fields in a table Access 95 and later
  • DirListBox(): Fill a list box with the files in a directory All versions
  • PlaySound(): Play WAV files in Access events All versions
  • ELookup() - an extended replacement for DLookup() Access 95 and later
  • ParseWord(): Parses the first, last, or n-th word/item from a field/list Access 2000 and later
  • FileExists(), FolderExists(): Determine if a file or folder exists Access 95 and later
  • ClearList(), SelectAll(): Select or clear all items in a multi-select list box Access 95 and later
  • DeleteAllRelationships(): Delete all relations in a database. Useful for repairs Access 95 and later
  • CountLines(): How many lines of code in the current database? Access 2000 and later
  • InsertAtCursor() - Insert characters at the cursor position Access 95 and later
  • GoHyperlink() - Handle warnings, special characters, and errors opening hyperlinks Access 2000 and later
  • AdjustDateForYear() - Intelligent handling of dates at the start of a calendar year. Access 2000 and later
  • Keep1Open() - Open a switchboard when other forms/reports close. Access 97 and later

Pitfalls and Traps

Examples by Library

Developer reference section: create, delete, modify, and list the database objects (tables, fields, indexes, relations, queries, databases) and set their properties. Heaps of code, with only limited explanations.

  • DAO code - 25 examples Access 97 and later
  • ADO code - 9 examples Access 2000 and later
  • ADOX code - 21 examples Access 2000 and later
  • DDL query - 8 examples Access 2000 and later

See also the Index of VBA Functions - list of the code from all pages on this site.

Applications / Utilities

Specific solutions and downloadable tools.

Applications

Utilities

Corruption / Repair

Flaws in Access

Service packs fix known bugs. To get the latest service pack for your version of Office, visit http://support.microsoft.com/sp.

New bugs

Engine-level bugs

The flaws listed below remain unfixed for at least three versions of Access. You must know how to work around them, or they will bite you. Flaws marked "Sample" are demonstrated in AccessFlaws.zip (Access 2000, 125KB).

If you use queries, be aware!

These can all fail:

PARAMETERS clause
SELECT clause
DISTINCT predicate
FROM clause
WHERE clause
GROUP BY clause
ORDER BY clause

Some of these describe cases where Access fails or crashes.
Worse still, some give wrong answers, with no indication that the results are incorrect.

Interface bugs

Cases where Access displays or handles data wrongly in forms and reports.

Other bugs

Miscellaneous issues:

Upgrading

Converting Access versions

Issues you may encounter in changing version:

Microsoft also has an Access 2.0 Converter for Access 2003.

Converting from the old xBase

If you have a background in the old dBase/Foxbase, you may appreciate:

Contact us

All tips and samples are offered without liability: use at your own risk.

Permission

You may freely use anything (code, forms, algorithms, ...) from these articles and sample databases for any purpose (personal, educational, commercial, resale, ...). All we ask is that you acknowledge this website in your code, with comments such as:

'Source: http://allenbrowne.com
'Adapted from: http://allenbrowne.com

Support

We are not able to give support for all the free tips, utilities, and solutions in these pages. We are no longer providing paid support either. For free help, see the Forums section on our Links page.

Occasionally, Access users offer to send something in gratitude. We prefer you give your donation to a child charity that provides education and health for children (such as Compassion.) Then drop us an email so we can share the pleasure of your gift.


Home Top

Tips for Casual Users

Design

Forms

Reports

Expressions

Tips for Serious Users

Forms

Combos

Reports

Queries

Relational Design

Tips for Programmers

Solutions (VBA)

Functions (VBA)

Pitfalls / Traps

Examples by Library

(Index of VBA code)

Applications and Utilities

Applications

Utilities

Corruption / Repair

Flaws in Access

New Bugs

Data Engine Bugs

Interface Bugs

Other Bugs

Upgrading

Access Versions

old xBase

Contact us

Permissions

Microsoft MVP
2002 - 2012

Home / Contact

About MVPs

About Allen Browne

Links

Search this site

Visit allenbrowne.blog

Access 2000 and 2002 users:

Many of these tips require the
Microsoft DAO Library.

If Database or Recordset gives an "unknown type" error, set your References.

(All other versions have this reference by default.)

Last update:   26 Feb 2019