32 to 64-bit Conversion

Last updated: 7 Nov 2023

The first in a series of articles providing links to various web pages & utilities to assist with converting VBA code for use in 64-bit applications.


More Details



32 to 64-bit Conditional Compilation

Last updated: 17 Nov 2023

The second in a series of articles which outlines how and when to use conditional compilation when converting API declarations in your 32-bit Access applications to run in 64-bit Access.


More Details



32 to 64-bit Manage Procedures

Last updated: 10 Dec 2023

The fourth in a series of articles which outlines how to manage issues with procedures when converting API declarations in your 32-bit Access applications to run in 64-bit Access.


More Details



32 to 64-bit My Future Plans

Last updated: 16 Dec 2023

The fifth in a series of articles on 32 to 64-bit conversion. Over 13 years after the introduction of 64-bit Office with version 2010, I have decided to no longer support versions of Access prior to VBA7 in all new applications from now on. This article explains my reasons, how it will affect my code and how those still using Access 2007 or earlier will be able to adapt the code if needed.


More Details



32 to 64-bit Variables & Type Statements

Last updated: 7 Dec 2023

The third in a series of articles outlines how and when to use conditional compilation when converting variables and Type statements in your 32-bit Access applications to run in 64-bit Access.


More Details



A Trip Down Memory Lane - Access Past ... And Future

Last updated: 7 May 2023

This article reviews the main changes in Access features and appearance from its first release in 1992 through to the current day. It also looks at developments currently in progress and planned


More Details



Access file security compared - MDB/MDE vs ACCDB/ACCDE

Last updated: 12 Oct 2018

There are many things that developers can do to improve the security of their applications (both design & data). However, no Access database can EVER be made 100% secure.
A capable and determined hacker can break any Access database given sufficient time and determination.

The older MDB / MDE file format is FAR LESS SECURE than the newer ACCDB / ACCDE format. Whilst this is hardly news to many developers, I’ve had several requests to provide more details to justify this statement.

This article describes a simple way of comparing the security of the MDB & ACCDB file formats together with the compiled versions MDE & ACCDE.
To do this I used a hex (text) editor to view each of the files and examine the contents

More Details



Access Specifications Issues

Last updated: 22 Oct 2024

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

These include the maximum values for each of the following:
a)   number of objects in a databaseh
b)   database password length
c)   number of code modules
d)   number of controls on a form or report
e)   number of printed pages on a report


More Details



Access Wizards, User Data & Cache

Last updated: 16 Mar 2024

This article explores a relatively obscure area about which there is little or no official information.

The article explains where user data is stored, including that associated with Access wizards, and what it does.


More Details



Accurately Move Forms & Controls

Version 7.4      Last updated: 6 Mar 2019

The example application demonstrates some complex processes including:
1.   Moving a popup form such as a customised zoom box to a precise position over
      another form irrespective of form settings

2.   Highlighting a listbox record to 'select' it without clicking on the listbox.
      This is done by accurately detecting the record underneath the mouse cursor based
      on the height of each row in the listbox.
      This means the record can be used e.g. to open a filtered form / view an image without
      actually selecting the listbox record!

3.   Determining the x-y coordinates of a form object and nudging the object by a
      specified amount in any direction

This requires an understanding of the relationship between the various units of measurement used in Windows applications: pixels, points and twips

The application also makes use of the little known and undocumented VBA Wizhook function

More Details



AI: Using ChatGPT to write code

Last updated: 18 Jan 2023

This is a series of articles about the ChatGPT tool which has been in the news a lot recently. Many articles are very positive about its capabilities. However, there have also been some significant concerns raised.

The articles outlines its potential use for writing code but also raises some important issues including its likely use by hackers to create malware


More Details



Annotate Google Maps in Access

Version 1.7       Last updated: 7 Dec 2022

This is a series of three articles demonstrating ways of 'annotating' downloaded maps to personalise these for your clients. Features include the use of marker points, lines, circles & boundary shapes, nearby places and styles.

All features are added as new map layers. An example app is provided.


More Details



Application Parts & Templates

Last updated: 8 Jan 2023

This is the third in a series of articles discussing ways of creating databases in code and from templates

This article discusses two little known features in Access and how to make them useful for you when creating new databases


More Details



Automatic Form Resizing Tutorial - ResizeForm Me

Version 3.90   Last updated: 28 Dec 2022

This is a series of four articles discussing the benefits of automatic form resizing when developing applications for use with different monitor sizes and resolutions.

It also covers an alternative approach based on layout guides and anchoring.

The latest version includes a number of recently added features including:
a)   refresh form when moved to another monitor or resolution is changed
b)   zoom forms in/out according to user preference
c)   stretch/shrink forms and controls on the fly by dragging with the mouse
d)   support for high resolution and portrait monitors
e)   resizing of datasheets, split forms and navigation forms
f)   scaling of images on command buttons

The article explains how the code works and provides tips for using the code most effectively in your own applications. It also covers some potential issues you may experience and possible solutions for each of these.

More Details



Centre Form On Screen

Last updated: 8 July 2023

Access provides a built in 'Center Form' feature as part of the form property sheet.
However this only centres the form in a horizontal direction.
The results can, at times, be visibly well off-centre.

Over the years, I have acquired & created various code samples that can be used to centre forms in both horizontal and vertical directions. However, I have also found that these do not all give identical results.

The outcomes depend on whether:
a) Access is maximised or not (occupies part of the screen only)
b) the navigation pane is maximised/minimised or hidden
b) the forms are popup or not

This article compares the outcomes from 4 different examples of centre form code      

More Details



Compare Control Help Text Methods

Last updated: 25 Mar 2023

This article compares 5 different methods of displaying help for form controls. The advantages and disadvantages of each method are discussed

More Details



Debunking Myths about the Autoexec Macro

Last updated: 29 June 2022

This article debunks several widely believed fallacies about the autoexec macro which is often used to run various actions when a database app is started

More Details



Filtering with Contains gives inconsistent results

Last updated: 25 May 2024

This article describes issues with one of the two context filter menu 'Contains' items (AKA the Batlle Bug). One of these methods always give the correct outcomes. The other method often gives incorrect or confusing results.

The Access team have acknowledged this as a bug and are investigating the best solution to the issue.

The article provides the results of many detailed tests and also demonstrates how the temporary filters can be saved.

More Details



Gradient Background Fill in Forms

Last updated: 19 Nov 2023

This article includes an example app demonstating how to add a gradient background fill on forms using several different methods: using code, gradient images or command buttons.
The advantages and disadvantages of each method are discussed.

More Details



How Access Stores Queries - the MSysQueries table

Last updated: 8 Aug 2022

This first part of this article explains how Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window and in SQL view.

This second part explains how Access determines the last saved view (design or SQL view) for each query which is then used the next time the query is opened.

More Details



Improve Security in Access Databases

Last updated: 8 June 2023

The first part of this article summarises many of the actions that all Access developers should do when deploying databases in a multi-user environment. These will significantly reduce the risk of data corruption as well as minimising the possibility of data being misused or stolen

The second part gives a detailed explanation about each of the methods involved together with an example application containing all the code required

The third part of this article discusses a wish list of changes I would like to see added to ACCDE databases to further improve security.

More Details



Installing Multiple Office Versions

Last updated: 13 Mar 2022

This article explains how you can successfully install more than one version of Office on the same workstation including:
a)   Two or more versions of the same bitness
b)   Both Access runtime and full versions of Access
c)   Mixed bitness installations . . . yes, it can often be done

More Details



Issues Modifying Split Forms: Computer Says No!

Last updated: 7 Mar 2023

The Access split form can work well but has several important limitations and issues. They are difficult to adapt if you want to modify them in any significant way.

This article discusses some of these issues and outlines alternative approaches to overcome them

More Details



Issues with Lookup Fields in Tables

Last updated: 6 Jan 2019

Access allows you to use lookup fields at table level.
Doing so, allows you to have combo boxes to guide end users with a list of available choices.

Although at first sight this may seem an attractive option, experienced developers normally advise against their use. Arvin Meyer described several reasons against their use in his article: The Evils of Lookup Fields in Tables

This article describes another issue - exporting lookup field data to Excel

More Details



Modern Chart Improvements

Last updated: 27 Aug 2024

This article summarises the very welcome improvements to modern charts that are being rolled out to Current Channel users in Access 365 version 2408.
Changes include many new chart types and additional functionality with existing charts

More Details



Monaco SQL Editor

Last updated: 28 Aug 2024

This article summarises the new Monaco SQL Editor feature that is being rolled out to Current Channel users in Access 365 version 2408.

More Details



Multivalued fields . . . and why you really shouldn't use them

Last updated: 7 July 2022

Multivalued fields (MVFs) were a new feature introduced with Access 2007
This article explains what MVFs are and how they can be used.

Although often attractive to new users, most experienced developers avoid them completely.
This article explains why this feature is really not a good idea

More Details



Purpose of System Tables

Last updated: 27 Nov 2023

This is a series of three articles about the system tables used by Access to make databases function correctly. With a few exceptions, there is very little information available online about most of the system tables.

The purpose of the articles is to summarise known information about Access system tables.
Some of it is fairly obscure and/or incomplete.

It is also an invitation to other developers to add to this pool of knowledge.

Some system tables can be viewed & a few can be edited
However you should only do so ...IF YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING
Incorrectly editing system tables may corrupt your database or prevent you opening it

More Details



Query Join Types

Last updated: 17 Jan 2019

This article explores the different types of select query joins available in Access and how these affect the query output in each case:
a)   Inner Join
b)   Left Outer Join
c)   Right Outer Join
d)   Union Query (AKA Full Outer Join)
e)   Cartesian Join (AKA No Join)

In addition it discusses the causes of ambiguous join errors and how to solve these

More Details



Recover Deleted Database Objects

Last updated: 24 June 2022

This is a detailed guide to the different methods of recovering different types of database objects, together with the advantages and disadvantages of each method

More Details



Relationships & Referential Integrity

Last updated: 25 Feb 2022

This article describes the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity.

Three methods of adding relationships are discussed. In addition, the article explains how Access uses the MSysRelationships system table to store the relationships that have been applied

More Details



Remove deleted objects from MSysObjects

Last updated: 4 Dec 2020

The read only system table MSysObjects keeps a record of all items in a database so these can be used by forms/reports/module code etc. Normally the MSysObjects table works perfectly but things can go wrong leaving incorrect data in the table e.g. if the database crashes for any reason. This article discusses 3 types of problem that can occur & a solution for each

More Details



Running Access on a Tablet PC

Last updated: 29 July 2022

This article discusses how to design Access apps for use on a tablet PC. It includes an example app with two different on-screen keyboards for testing purposes.


More Details



Securing Your Data - Encrypted Split No Strings Database

Version 5.25   Last updated: 26 Dec 2020

This is an updated version of the article discussing ways of helping to keep Access data secure against both hackers and theft whilst still allowing full functionality to authorised users.

This example application should behave exactly as any split database BUT there are no linked tables and therefore no connection strings visible in the MSysObjects system table.

In addition, the data is protected using a strong 128-bit RC4 encryption cipher.

More Details



Security Alerts in the Message Bar

Last updated: 5 Dec 2023

This article shows several of the security alerts you may experience in Access and what action is needed in each case.


More Details



Set Default Control Properties in Forms & Reports

Last updated: 7 Mar 2023

This article explains how to set default control properties in Access databases either manually or using code. This will allow you to easily customise all form & report controls to suit your preferences.


More Details



Show Plan - Run Faster

Last updated: 17 Dec 2021

During development work, all of us will want to ensure that tasks are completed as quickly as possible. However, it is not always obvious that the current design is inefficient until performance slows to a crawl and clients start to complain.

Where queries or VBA SQL statements are concerned, help is available by making use of the Jet ShowPlan feature which is available with both the JET database engine (up to A2003) and the newer ACE engine (A2007 onwards). The ShowPlan option prints the query execution plan to a text file so you can review and, if possible, improve the design.

The purpose of this article is to provide more details about the Jet ShowPlan feature for which little documentation is available online

More Details



Sort Bit Fields in Linked SQL Server Tables

Last updated: 20 Oct 2024

This article discusses why sorting linked SQL Server bit fields gives the exact opposite of the expected results compared to sorting boolean fields in local / linked Access tables. The results can be confusing to your database users. The article also gives several workarounds to prevent this being an issue to end users.


More Details



Speed Comparison Tests

Last updated: 3 Jan 2023

This article describes various tests done to compare different approaches to coding:
1.   Handling nulls: Trim/Len/Nz
2.   CurrentDB vs DBEngine(0)(0)
3.   DoEvents vs DBIdle.RefreshCache
4.   HAVING vs WHERE
5.   Conditional Updates: If..ElseIf..End If/Select Case/Nested IIf/Switch/Lookup Table
6.   Query vs SQL vs QueryDef
7.   Check Record Exists
8.   Optimise Queries
9.   SELECT DISTINCT vs GROUP BY
10. Regex Or Not
11. $ Or No $
12. Finding Unmatched Records
13. WHERE OR vs WHERE IN
14. Loop vs Recursion
15. Grouped Aggregate Queries

Example databases are provided so the tests can be done on your own workstations

More Details



Synchronise Data

Last updated: 14 Mar 2019

This article explores some of the methods that can be used to synchronise data with an external source. The advantages and disadvantages of each method are also discussed.

More Details



The WizHook Object - Hidden & Undocumented

Last updated: 5 Nov 2022

A detailed reference guide to the hidden, undocumented but very useful Access WizHook object and its members

More Details



Timer Comparison Tests

Version 1.6   Last updated: 27 Feb 2019

This article compares 6 different timing methods with a view to determining the reliablility and consistency of each method.

The 6 methods compared are:
Timer / GetSystemTime / GetTickCount / TimeGetTime
Stopwatch class / High Resolution Timer

More Details



Use Column History to show historical data in memo fields

Last updated: 6 Apr 2022

This article explains how the column history property can be used to show a history of changes made to memo / long text fields.

The article also explains how this data is stored in a deep hidden system table and how to deal with issues associated with editing the column history

More Details



Use Name AutoCorrect Successfully

Last updated: 11 Mar 2023

This article explains how the Name AutoCorrect feature works in Access, its limitations and how to use it successfully in your applications

More Details



Using the new VBA Project Signing feature

Last updated: 12 June 2024

This 2-part article explains how to use the new VBA project signing feature in Access. It also compares how this feature works with that of using trusted locations when distributing your Access applications

Updated 12 June 2024 with details of new regulations - code signing certificates must now be stored on 'secure tokens'

More Details



Write Conflict Errors

Last updated: 6 Jan 2019

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

Possible causes include:
a)  Two or more users are trying to edit the same data at the same time
b)  Editing data that uses multiple forms that have the same record source
c)  Boolean fields in linked SQL Server tables with no default value

More Details



Return to Home Page Return to Top