Mendip Data Systems

Database applications for businesses and schools

LogoTransparent
* Required

Access Articles & Guidance                        

iStock_000002894402Medium iStock_000005251211Large-LOW-RES american-coffee-colleagues-1376871 education tools

To provide feedback on any of the articles, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

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

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

           

More Details ...

Updated 12/10/2018

Speed Comparison Tests          

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

 

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

           

More Details ...

Updated 04/03/2019

If you found this site useful, please consider giving a donation to help offset the costs in keeping it running. Thanks.

1 2 3 Return To Top
Page 1 of 3

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.

Purpose of System Tables    

Updated 16/10/2018

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 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's 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 ShowPlan feature for which little documentation is available online

           

More Details ...

Show Plan - Run Faster

Updated 03/11/2018

Return to Top

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

           

More Details ...

Improve Security in Access Databases

Updated 20/03/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

           

More Details ...

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

Updated 13/11/2018

Issues with Lookup Fields in Tables

Updated 06/01/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 of the reasons against their use in The Evils of Lookup Fields in Tables 

 

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

           

More Details ...

SECTION BELOW NOT CURRENTLY IN USE ...

Write Conflict Errors

Updated 06/01/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 ...

Synchronise Data

Updated 14/03/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 ...

Query Join Types

Updated 17/01/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 ...

Use Column History to show historical data in memo fields

Updated 30/01/2019

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

How Access Stores Queries - the MSysQueries table

Updated 03/02/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.

 

More Details ...

Relationships & Referential Integrity

Updated 27/03/2019

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

Accurately Move Forms & Controls

Version 7.3      Updated 22/02/2019            

 

The example application demonstrates some complex processes including:

•      Moving a popup form such as a customised zoom box to a precise position over

      another form irrespective of form settings

•      Highlighting and 'selecting' a listbox record 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!

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

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

Timer Comparison Tests

Version 1.6   Updated 27/02/2019

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

 

More Details ...

ResizeForm Me - An Automatic Form Resizing Tutorial

Version 2.6   Updated 14/03/2019

This article discusses 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 ...

Securing Your Data - Encrypted Split No Strings Database

Version 2.0   Updated 14/03/2019