dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Microsoft Access Tips and Techniques

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

Last updated on 2019-01-09 by David Wallis.


Content

This page starts with sundry issues relating to aspects of working with Microsoft Access. It continues with topics about converting to Access 2016 databases going back to Access 97. Towards the bottom, there are links to pages about tables and their relationships for databases, eg contacts and project management.

These topics have not been vetted by third parties, so please treat them critically.


These topics have not been vetted by third parties, so please treat them critically.

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 DMW's 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.

Compacting databases and database speed is about 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.

Excel-like rounding of numbers presents an Access VBA function that produces the same results as Excel’s ROUND function.

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 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.

List all database objects presents VBA functions for making lists of the tables, queries, forms and reports that make up a database.

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

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


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

Converting Access 97 databases describes the steps in converting Access 97 databases to Access 2013 and 2016, which will not read or convert Access 97 files.

Converting Access 2003 databases describes the steps in converting Access 2003 databases to Access 2013 and 2016, which will not read or convert Access 2003 files.


Creating a contacts database considers the choice 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.

Project management databases My 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.

The party entity I take the contacts database model and argue in favour of introducing the Party for databases in which People and Organisations may share the the same properties, eg addresses and roles.


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)