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

 

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

           

More Details ...

Updated 26/11/2018

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.

SECTION currently not in use

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

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

           

More Details ...

Improve Security in Access Databases

Updated 05/11/2018

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