Microsoft Access 2016 Will Soon Support Large Number (BigInt) Data Type

Microsoft Access 2016 Will Soon Support Large Number (BigInt) Data Type

Coming soon… Microsoft Access 2016 will gain a new data type called ‘Large Number‘ which will be an extension to the Number at a type or more commonly referred as BigInt (Big Integer).

Large Number (or BigInt) is a new supported data type that will help developers and users with added analytical capability. This extended support means that Access can finally read and write data to and from external data sources which make also makes use of BigInt.

This has been a limitation when importing data into an Access database from larger more robust database applications and this feature will be able to switched on or off (I guess to help keep the application optimised and not slow down too much).

As announced, “When creating new local tables or editing existing ones, Access now allows users to add fields that store BigInt numbers.” The upgrade, which adds a ‘Large Number‘ option to the ‘Click to Add’ menu when adding a new field to local tables, opens up new possibilities, allowing users to work with SQL data and other external data sources that use BigInt.

microsoft-access-2016-will-soon-support-large-number-bigint-data-type

If not already available (as installed), as an Office 365 subscriber you will need to run an update to get the new data type. Other variants of Microsoft Access 2016 will shortly also see this feature be available to download and install.

Watch out for  more information on how this new data type will be practically used in Access and more details will soon be available and reveal the scope of this new field type.

Microsoft Access Database: Should We Be Using Calculated Table Fields

Microsoft Access Database: Should We Be Using Calculated Table Fields

With the introduction of Microsoft Access 2010, a new data type field appeared in your table design view which provided a basic way to take fields in the same table and create new expressions as a new dedicated field sitting at the ‘top of hierarchy’ database!

However, should we really be using this new data type and practice at all?

microsoft-access-database-should-we-be-using-calculated-table-fields

Purists will frown upon this technique and say you should stick with Excel to store real values and expressions together. By doing so, you are breaking the rules of database normalisation and also cause a maintenance headache once your database has been deployed.

There are some considerations to think about that may Continue reading “Microsoft Access Database: Should We Be Using Calculated Table Fields”

Microsoft Access Table Relationships

Microsoft Access Table Relationships

An ideal database is free of redundant or duplicate data. To achieve that, you must split your data into many subject-based tables so each subject is presented only once. To do that in Microsoft Access, you place common fields in tables that are related. But before you do that, you must need to understand the relationships between your tables, then you can specify these relationships in your Access 2013 database.

microsoft access table relationships

In this article, I will elaborate to you the ten things you need to know about table relationships in Microsoft Access.

1. A one-to-many relationship is when one record in the parent table connects to many records in the child table.

To exemplify this, let’s take for example two tables: one for doctors and the other is for patients. A doctor can have several patients. In this case, you will Continue reading “Microsoft Access Table Relationships”

Microsoft Access Validation Rules In Tables

Microsoft Access Validation Rules In Tables

A validation rule restricts or controls what a user can input in a table field. You can think of it as a set of layers which ensures that your users input data properly.

Here’s a quick video tutorial to get you going and very easy to apply.

Microsoft Access provides several ways to validate data: data types, field sizes, table properties, and input masks. Among these, table properties provide specific types of data validation.

One example is setting the required property to YES which results to forcing the users to enter a value in that specific field.

Another technique is by using the Validation Rule property to require specific values, and the Validation Text property to alert your users to any errors. For example, putting a rule such as Continue reading “Microsoft Access Validation Rules In Tables”

Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Microsoft Access Tutorial: Creating A Many-To-Many Relationship

Let’s revisit a popular aspect to your Microsoft Access database design; Creating a many-to-many relationship.

Firstly, watch this quick video which explain the key components and the starting steps to getting to the many-to-many state for your Access database.

In order to create this type of relationship, it’s now clear we need an interceding or junction table to handle the two-way joins of the outer tables with Continue reading “Microsoft Access Tutorial: Creating A Many-To-Many Relationship”

Microsoft Access Database: Creating Database Field Type Templates

Microsoft Access Database: Creating Database Field Type Templates

Microsoft Access database applications just gets richer and richer as time goes by and from version 2010, you can now create field types or in effect user defined fields stored via the ‘Save Selection as New Data Type‘ command which is located from the context sensitive fluent ribbon bar Fields tab (Table Tools).

Take a look at a demonstration of this is set up and used in the video tutorial below (less than 4 minutes).

Microsoft Access Database (version 2010 onwards) has a new feature which allows you to preserve existing columns that you can use Continue reading “Microsoft Access Database: Creating Database Field Type Templates”

Microsoft Access Tutorial – How to use Input Masks

Microsoft Access Tutorial: How to use Input Masks

Microsoft Access databases provide a wealth of tools and utilities to help control design layouts and data integrity. Working with the latter, you can reduce and even dismiss any programming at all to control data entry to a field in a table or via a form using the Access Input Mask feature.

Take a quick look at this useful Microsoft Access tutorial (using version 2010) on how to set up an input mask.

Very easy to create indeed and the video covers the pre-set options which to be honest is based on the US style conventions for Continue reading “Microsoft Access Tutorial – How to use Input Masks”

Microsoft Access Database – Working With Two Digit Hour Time Formats

Microsoft Access Tutorial – Working With Two Digit Hour Time Formats

Here’s a quick Microsoft Access tutorial video I found about applying formatting to the date/time data type control concentrating on the two digit hour time format that doesn’t always show up in two digit form.

Even if you create a custom date/time format within Microsoft Access, the two-digit hour which is supported using the convention of “hh” via the format property doesn’t always work. It will show a single digit hour even though you’ve specified the right code. Take a look at the video below.

Microsoft Access Tutorial – Working With Two Digit Hour Time Formats

In the video tutorial, it mentioned one of three ways to Continue reading “Microsoft Access Database – Working With Two Digit Hour Time Formats”

How to Use Smart Tags In Microsoft Access 2010

How to Use Smart Tags In Microsoft Access 2010

How to use smart tags in Microsoft Access will only be applicable to 3 versions (2003, 2007 and 2010). Forget about the earlier versions and now even with the latest Access 2013, this too has been dropped!

Since most users are still engaged with the main three releases mentioned, I thought it may be worth a re-visit to understand how to apply a smart tags for your fields in a table.

how to use smart tags in ms access 2010

The key decision to consider when a smart tag should be added will be to identify a benefit for a user-friendly list of actions for certain type of information (or value) being stored allowing users to simple choose from a pre-set list of actions.

For example, a contact name field in a table (or form) could be best served to assist with Continue reading “How to Use Smart Tags In Microsoft Access 2010”

How To Create A Database In MS Access And Link To MySQL Database

How To Create A Database In MS Access And Link To MySQL Database

Check out and follow these easy steps with this video tutorial (less than 2 minutes) to learn how to create a database in MS Access and link it an MySQL database using the ever popular ODBC connection.

The acronym ‘OBDC’ stands for ‘Object Database Connectivity’ and has been around for a very long time now which is now considered the standard for creating a gateway to you MS Access link.

As the video shows, you first establish the database source driver, create an established connection (including a quick test) and then Continue reading “How To Create A Database In MS Access And Link To MySQL Database”