This page contains links to some longer articles discussing specific issues in Access in greater detail.
It is intended that the number of articles will be significantly increased in the coming months so please check back again regularly.
Full List of Access Articles
A Trip Down Memory Lane - Access Past ... And Future
Last updated: 2 May 2022
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
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
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
Annotate Google Maps in Access
Last updated: 11 May 2022
This will be a series of 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.
Automatic Form Resizing Tutorial - ResizeForm Me
Version 3.7 Last updated: 16 Jan 2022
This article discusses 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 also includes code to zoom forms in/out according to user preference.
This may be particularly useful for those whose eyesight is less than perfect
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.
Centre Form On Screen
Last updated: 3 Dec 2020
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
How Access Stores Queries - the MSysQueries table
Last updated: 3 Feb 2019
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.
Improve Security in Access Databases
Last updated: 12 Mar 2022
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.
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
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
Multivalued fields . . . and why you really shouldn't use them
Last updated: 7 Mar 2019
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
Purpose of System Tables
Last updated: 28 Oct 2020
System tables are 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 this article is to summarise known information about Access system tables.
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
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
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
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
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.
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
Speed Comparison Tests
Last updated: 8 Mar 2022
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 $
Example databases are provided so the tests can be done on your own workstations
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.
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
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
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
Return to Home Page
Return to Top