Welcome to Microsoft Access Database Tutorial

Welcome to the Access Database Tutorial website that will show you how to use Microsoft Access Database by learning and managing this powerful application using the most effective techniques and tools available for visitors with very little or no knowledge to get you up and running without the need to learn all the ‘geek’ speak keeping it Jargon free that most trainers and consultants like to impress you with!

If you are new to Access or wish to know what is MS Access, please take a look at An Introduction To Microsoft Access.

Also, this website contains a blog, products on offer and free general tips to help users find out all about  latest news and articles I feel worth mentioning along with my recommendations of videos and books.

As a Thank You for Visiting…

Claim Your Free eBook NOW!

Enjoy and thank you for visiting!

Why You Should Avoid Using Memo Fields for Grouping in MS Access ?

Learn access database

A memo field can store a huge quantity of information, allowing up to 65,536 characters, with different choices. In the most recent release of Microsoft access database, it can store up to 1 GB of characters and enables rich text formatting. However, it is often advised to avoid the use of memo fields for grouping in Access.Microsoft access database

Memo Fields

In the 2009 edition of Microsoft access database and including the recent version, there was a bug in which the memo field would show incorrect and erroneous characters under specific circumstances. If the user used the‘GROUP BY’ query on a memo field or if the query contains a JOIN on un-indexed records, the memo field would display incorrect results. Microsoft Access would just show random characters in place of the contents of the given memo field.

Microsoft access database fundamentally truncates the memo and the most widely known factors that cause the truncation are aggregation, uniqueness, formatting, and union queries.  For instance, a union query that joins the records from various tables and then de-duplicates them. It analyses the memo field which then results in truncation.

Why Memo Fields Are Inefficient

The main reason why Microsoft access database does not allow more than 255 characters is that it would hamper the performance to a great extent, as string operations are disk and processor intensive. Some data sources process strings as bytes while some use Unicode so it becomes incompatible. Though it saves the data to the table, still it becomes inefficient in working with the extra data. Continue reading “Why You Should Avoid Using Memo Fields for Grouping in MS Access ?”

How to Insert Date and Time into the Header and Footer Sections of Your MS Access Report ?


Microsoft Access 2016 lets users design their reports with ease and most advantageous way compared to other dedicated reporting tools.

There are various formatting choices that giveflexibility and make reports more efficient. Microsoft Access 2016 has a report Wizard (as a starter point) that walks you through the process of creating a report. The report look and feel will greatly depends on the user’s requirements. The more complex it gets, the higher the degree of customisation is needed.

The greatest strength of any report is in its structure. If the report is informative, it will surely stand out. You can improve your reports by inserting headers and footers, adding logos, and changing colour combinations. In this blog, you will learn how to insert time and date into the header and footer sections of your MS Access report.

Header and Footer Sections

The header section of a document contains significant information and is displayed in the top margin. Like the header, a document’s footer also contains valuable information. The only difference is that it is positioned at the bottom. The information they contain can be of any kind such as date, time, document name, and page number. They are effective add-ons thatmake your documents more organised and looking professional.

It’s surely a tedious task to write the page number for each and every page you finish, not to mention adding the dates. Some reports require a heading and a subheading on every page. By inserting headers and footers, you can avoid the hassle and make your document easier to read.  Continue reading “How to Insert Date and Time into the Header and Footer Sections of Your MS Access Report ?”

How to Back up Your Split Database in Microsoft Access ?

image about access

A split database is created by splitting two Access database files ending up with a ‘Back-End’ and ‘Front-End’ database files.

A ‘Back-End’ Access database normally contains only the data tables while the ‘Front-End’ comprises the remaining database objects such as queries, forms, and reports.

The data that a user inputs in a database object will be stored in the tables that back-end databases hold. Anybody can go through the information available in the front-end database but in order to access the back-end, the user needs to have administrator permission.

Splitting a database can improve its performance, help secure your data, and enables greater availability.

Since both databases have diverse properties, they are also backed up self-sufficiently. Front-end databases, which containminor types of data, use up less space.

Because of that, it takes a smaller amount of time to create the backups. But that’s the not the case with back-end databases as they hold all the data. For that reason, it is imperative to generate regular backups to avoid incidents of corrupted Access databases.

Creating a backup for Back-end Databases

Before generating the backup, make sure to inform all users on the system about it. Creating a backup requires exclusive access to all the database files. This process restricts users from using the database. Continue reading “How to Back up Your Split Database in Microsoft Access ?”

Aggregate Queries in Microsoft access database

In Microsoft access database, you might want to perform operations on not just one single record, but on a group of records. You can simply create fields that perform operations per row or on every record. But what happens when you perform calculations on a group of records? This is where Aggregate queries come in handy.

Aggregate Query

An Aggregate query lets you carry out calculations on record groups rather than perform individual operations, and because of that, it is also referred to as Summary query. It considers the total, subset, or gross amount of records.

In performing calculations on a group of records, there are numerous operations you can follow. Some of these operations are explained below.

  • Sum– One of the most familiar and simplest ways of doing operations on records is using the Sum function. This adds (or sums) all the values contained in the field.
  • Average– If you need to calculate the mean, you can use the Average function. This calculates the average value for all values in the field.
  • Min– This is used in finding the lowest value in all field.
  • Max– Contrary to Min, this function is used in finding the highest value from the given field.
  • Count– This returns the total number of records in a field.
  • StDev– The standard deviation function is used to evaluate a population sample represented as a set of values in a specified field on a query.
  • Var– This function returns the estimate of the variance for a population sample denoted as a set of values in a designated field on a query.

Continue reading “Aggregate Queries in Microsoft access database”

Access Database Append Query – Dealing with Common Errors When Executing Them

Access Database Append Query

As a follow up on a recent blog post about Microsoft Access database Append Queries, here’s a sub-note article that we will analyse and investigate when errors can happen relating to a failure of appending records in MS access query

A quick recap: An  Append MS access query is used when a user amends a few records to an existing table, typically from different sources.  Append MS access query chooses new records from different sources of data and copies them to the table in database. It is useful in joining numerous records at once and it also enables the user to refine the selection with specific criteria. Users can evaluate the selection before replicating it to the existing table.

Issues with Appending Records

Access typically shows a dialog box when the append MS access query is run, expressing the possible explanations for its failure. Errors might be due to the mismatch of field data types or key violations. Apart from that, Lock and Validation Rule violations could also be the reasons.

Let’s discuss each issue individually and how we can solve these errors.

Type Conversion Failure

This is the most widely recognised error a user-experiences when appending and it happens due to the field data-type mismatch. Access commonly encounters issues if the data is not properly formatted or whenever there are missing field types. For instance, if a user tries to import data in a Numeric field such as date or age, and the data contains records like ‘Unknown’, then Access might display it as type conversion error. The problem could also arise if the date is not in the local regions standardised format (namely USA versus UK/EU dates). Continue reading “Access Database Append Query – Dealing with Common Errors When Executing Them”

Planning Tips To Building A Microsoft Access Database


In my latest eBook on how to build a Microsoft Access database, I cover a section about planning your database before developers should delve in and implement. Before they know it, it’s too late when they realise how off track they are and now left with potentially a reversing and undoing exercise!

So, to get started on the right track, here’s an extract for your reference…

Reverse Engineering? I’m often asked How do you design a good Microsoft Access database? My simple answer, I Reverse engineer it!

It is a technique that I have used many times and it works very well for me. The process to a good

Microsoft Access database design has nothing to do with Microsoft Access or any other database application.

The methodologies I’ve come across over the years leave me with a lot of questions about “Do the methodologies often discussed really justify the end result?” Continue reading “Planning Tips To Building A Microsoft Access Database”

Creating an Append Query in Microsoft Access


This article talks about the importance of the  Append Query and the appropriate way of creating them in Microsoft access database.

In Microsoft access database and all the other database management systems, queries as the heart of the software system which can execute numerous actions to make your database more systematised and functional. Queries prove to be useful for working on various tasks including returning recordsets in an ordered and filtered way, updating values, editing or deleting data, and even making a new table in the Microsoft access database.

Action Queries can be used for adding, changing, or deleting numerous records from a table, record or field at a click of a button.

The additional advantage of an Action Query is that user can preview their query results in Microsoft access database before running it.

Microsoft access database provides the following types of Action Queries:

  • Append
  • Update
  • Delete
  • Make-Table

Note that users cannot undo an action query and therefore, they must create a backup of the data that they want to update using the query. Continue reading “Creating an Append Query in Microsoft Access”

8 Tips to Create Flexible Tables in Microsoft Access

Tables should be responsive, optimised and efficiently designed for your Microsoft access database.

Databases are all about tables and how they hold and then show the information stored in them. It is vital for them to be flexible, dependable and informative. That’s the reason why it is fundamental to create and assign the appropriate properties to such tables.Microsoft access database

The following 8 simple tips will help to plan and build your Microsoft access database tables:

1. Naming of the fields

The name of a field is its identity, so it should give a reasonable idea about the field’s function and data type. Arbitrary and inept name fields make your database pointless and confusing. Microsoft access database allows up to 64 characters for a field name which can contain letters, numbers, and spaces. However, good practice is to not include spaces in the field name as this can cause issues later on for more advanced functionality especially when working with VBA and SQL codes.

2. The use of field properties


Microsoft access database provides you with the option to assign properties to the fields such as format, caption, description, validation rule, and validation text (to name a few). Each property will improve in some cases, performance as well as change the look and feel of data values. This is the first level of changes that can be applied and act as defaults when working with related objects used later on in your design process with the likes of queries, forms and reports. Continue reading “8 Tips to Create Flexible Tables in Microsoft Access”

6 Advantages of Utilising Microsoft Access Database

Access Database is an effective tool that supports businesses in completing complex processes and enhances the way corporations work. It enables them to consolidate hectic organisational responsibilities like saving data,record keeping system, designing user-friendly forms and professional looking Reports.

With the added level of Microsoft Access programming (macros and VBA coding), experts can keep their business records reorganised smoothly and mimic user activities and support Business workflows. This advanced database system has decreased the level of ‘data-disarray’ and settled the issue of data loss significantly. 6 Advantages of Utilising Microsoft Access Database

To help understand the benefits, here are the 6 advantages of utilising Microsoft Access database:

1. Sample Databases.

The Microsoft Access database application includes simple and beneficial samples databases and capture data examples for clients, suppliers and general work processes. These models can be used to study about the real-world forms, reports, queries, tables. Learning these models makes it more effortless for users to make their own powerful database and use the contemporary styles they offer. Continue reading “6 Advantages of Utilising Microsoft Access Database”

MS Access Database Images: How To Correctly Handle Them ?

Microsoft access database was never really designed to handle images the same way other Office applications do and maybe it was deemed as bit of an afterthought.

Different versions lead to different challenges and the latest version (2016) still have issues ending sometimes up with invisible images on those forms and reports. This is further complicated by having a 32-bit version of Access database 2016 (16.0.4229.1024) and perhaps not the 64-bit version installed but that may not be a good enough reason to use the 64-bit version at all (unless of course images are your thing!).

In Access, images should appear properly but some simply do not. The original Images used this application was the BMP file format and they continue to properly show up however, other graphic types such as GIF, JPG and PNG formats may end up as a blank non-starter!

One thing you could check out is the database’s Picture Property Storage Format when the picture was added to the form or report. This can be found under the ‘Access Options’ setting for the Current Database:Access database

There two options are:

  • Preserve source image format (smaller file size)
  • Convert all picture data to bitmaps (compatible with Access 2003 and earlier)

If the image is added when the above option is set to the second option (Convert), the non-BMP graphics do not appear in the 2016 version. Continue reading “MS Access Database Images: How To Correctly Handle Them ?”