DMW logo
contact@consultdmw.com

Microsoft® Access Tips and Techniques

This page contains links to other pages on this website of tips on Microsoft Access database development and Access VBA programmimg.

Thanks to everyone who in 2023 has made a donation to this website. Much appreciated.

Last updated on 2023-04-10 by David Wallis.


Content

This page starts with sundry issues relating to working with Microsoft Access. It continues with topics about converting old databases (going back to Access 97) to Access 2019. The final section has links to pages about the choice of tables and their relationships for certain databases, e.g. contacts and project management.


General Topics

Access 32 and 64 bit versions The differences between 32 and 64 bit Access, and the choices to be made when choosing one or the othe.

AND, OR and AND/OR queries How to manage logic-based criteria in Access queries to filter data.

Access 2002 form loses subform Addresses the problem of a main form loosing one of its subforms during the design process. This is something that happened during my work on rare occasions but when it did it was never straighforward to deal with.

Attaching labels to control objects on a form Shows how to restore a label to a control in the design of a form.

AutoNumber field error Offers diagnosis of, and solution to, the problem of an AutoNumber primary-key field causing errors when inputting a new record is attempted: primary key duplicate value error.

Compacting databases and database speed Taking care of Access database files and addressing the issue of “bloat”.

Coping with null values in query expression fields Shows the use of the NZ function in preventing errors in the results of calculations in Access queries.

Thanks for donation

Data modelling How to plan the structure of a database before starting its construction, to make certain no aspect of the database’s tables and their inter-relationships are overlooked.

Dates and times Discusses issues relating to the input, storage and reporting of dates and times, and presents techniques for avoiding some of the pitfalls experienced with dates and times in Access databases.

Extracting dates from text strings Presents a custom Access VBA function for isolating a date from a text string in which the date is mixed with other text in no particular order.

Exporting Access data to Excel Builds a VBA program for exporting data from an Access database into an Excel worksheet and formatting the worksheet once the data have arrived in it.

Form or report control displays #Error Explains one common cause for the unwelcome appearance of this result in a text box.

Linking back-end tables (1) VBA procedure for linking tables from back-end into front-end of split database on start-up. The aim is to provide a means of linking front-end to back-end in a multi-user, LAN-based database, allowing for changes to folder structure and mapping to be made without need to involve the developer.

Linking back-end tables (2) A VBA procedure for linking tables from back-end into front-end of split database on start-up. This is a less code heavy than the procedure (1) above, but it does require a modicum of VBA knowlege in order to prime it.

Lost sub-forms Observations on the seemingly random loss of an Access 2002 database subform by its parent form.

Missing data How to locate in databases records with fields that have missing or inappropriate values due to user error or oversight.

Rounding numbers Presents a function for the Excel-like rounding of numbers.

VLookUp function Presents a function that out-performs Access’s own DLookup function.


Listing Database Objects

List database forms Presents VBA functions for making lists of the forms that comprise a user’s experience of a database.

List database modules Presents VBA functions for making lists of the macros and modules that are the programming elements of a database.

List database queries Presents VBA functions for making lists of the queries included in a database.

List database reports Presents VBA functions for making lists of the reports that make up a database.

List database tables Presents VBA functions for making lists of the tables that make up a database, with distinctions between local and linked tables.

List objects query Presents a select query that outputs the objects in any Access database.


Database Design and Development

Object naming conventions A much simplified version of the widely regarded Leszynski naming convention for objects of Access databases.

Designing and creating forms Considers what to take into account when designing a form; and describes how to create forms and subforms that conform to the design thus achieving consistency across a database.

Creating a database dashboard Suggests a novel design for a dashboard, or switchboard, as an alternative to screens presenting the user with lots of buttons. This design is quick to adapt as new forms and reports are added to a database; and it’s portable to any new database with little effort in configuring it for its new environment.

Creating a contacts database Considerations of the choices of tables necessary for keeping the details of people and organisations. The proposed suite of tables could be incorporated in any database in which contact information forms a part.

Creating a search form How to create a powerful search from that is easy to deploy in any database.

Date Picker How to to get the Date Picker working when it fails to show when you want it to.

The party entity Takes the contacts database model in favour of introducing the Party for databases in which People and Organisations may share the the same properties, e.g. addresses and roles.

People and organisations How to model the relationships between people and organisations and the roles of the parties within those relationships.

Products database Considerations of the choice of tables for products, that is, goods and services.

Project management databases Suggestions for the data tables and their relationships for a Microsoft Access database for recording who does what and when — for projects, tasks, jobs and activities.


Converting Access Databases

Converting Access databases Summarises the issues relating to converting old databases to Access 2013, 2016 and 2019. Access 2013, 2016 and 2019 do not have in-built capacity for the straightforward conversion of databases created in versions of Access older than 2010.

Converting Access 95 and 97 databases Describes the steps in converting Access 95 and 97 databases to Access 2013, 2016, 2019 or Microsoft 365, not one of which will read or convert Access 97 files.

Converting Access 2003 databases Describes the steps in converting Access 2003 databases to Access 2013, 2016, 2019 or Microsoft 365, which will not read or convert Access 2003 files.

Converting Access 2007 databases Describes the steps in converting Access 2007 databases to Access 2013, 2016, 2019 or Microsoft 365 and includes some tips on preparing your databses in Access 2007 prior to attempting the convertion.


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.

“I see no progress in this industry. These clocks are no faster than the ones they made a hundred years ago.”

Henry Ford (while visiting a museum)