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!

Planning Tips To Building A Microsoft Access Database

image1

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

image6

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

In Microsoft Access 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 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 before running it.

Microsoft Access 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.8 Tips to Create Flexible Tables in Microsoft Access

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, 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 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:https://www.accessdatabasetutorial.com/

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 ?”

Access Queries: Date Criteria May Not Always Work with MS Access Query

Learning about ms access query  is the key to a good database management system as it is the heart of any database application.

There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay dividend and avoid silly mistakes, illogical record set results and even errors.

One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and it’s conventions.

Take a look at the ms access query below showing orders before the year 2016 and the design of the

query which suggested a date range from 1st January 2016 to 31st December 2016.https://www.accessdatabasetutorial.com/https://www.accessdatabasetutorial.com/

The criteria for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range). Continue reading “Access Queries: Date Criteria May Not Always Work with MS Access Query”

How to create an embedded macro?

image13An embedded macro is a macro that is not displayed in the Navigation Pane under Macros; it is stored in the event properties of forms, reports, or controls. This can make your access database easier to manage because you don’t need to monitor separate macro objects that contain macros for a form or a report. To create an embedded macro, follow the steps below:

  1. Open the form or report that will contain the macro in Design view or Layout view. To open a form or report, right-click it in the Navigation Pane, and then click Design View or Layout View.image7
  2. If the property sheet is not yet displayed, press F4 to display it. Continue reading “How to create an embedded macro?”

Create a standalone macro– Part 2

As a reminder, Please review Create a standalone macro – Part 1image2

A standalone macro is a macro that is displayed under the Macro in the Navigation Pane.

To create a standalone macro, take the following steps:

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. You should be able to see the Macro Designer by now. To add an action, type in the macro action or click on the drop-down menu to display the list from which you choose the action that you want to use.
  4. To add more actions to the macro, move to another action row, and then repeat the previous step. Access carries out the actions in the order in which you list them.
  5. Click Save when your done.

Creating a macro group

Create a macro group for several related macros.

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. Once the designer is displayed, select Group from the drop-down list. Continue reading “Create a standalone macro– Part 2”

What’s the new Macro Features from the release of Access database 2010?

image2

In previous versions of Access database, many usually used functions could not be executed without writing VBA code. With the release of Access database 2010, new features and macro actions have been added to help remove the need for code. This makes it less demanding to add functionality to your database and helps make it more secure.

  • Embedded macros: You can now embed macros in any of the events given by a form, report, or control. An embedded macro is not displayed in the Navigation Pane; it becomes part of the form, report, or control in which it was created. If you make a duplicate of a form, report, or control that has embedded macros, the macros are also present in the duplicate.

Continue reading “What’s the new Macro Features from the release of Access database 2010?”

What is a macro?

image1

In this article, you will learn all about macros — what they are and how they automate tasks for users to save time. It examines the essentials of making and using macros.

A macro is a tool that lets you automate tasks and integrate functionality into your forms, reports, and controls. For instance, if you incorporate a command button to a form, you link the button’s OnClick event to a macro. The macro will have the commands that you want the button to perform every time it is ticked.

In Access, it is useful to consider macros as a simplified programming language that you compose by building a list of actions to perform. When you create a macro, you choose every action from a drop-down list then fill in the needed information for every action. Macros allow you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros give a subset of the commands that are accessible in VBA, and most people find it simpler to construct a macro than to write VBA code. Continue reading “What is a macro?”