Is Referential Integrity important?

The short answer, yes. The arguments for implementing referential integrity are numerous and it is a key aspect of any database development, whether using Microsoft Access or another type of Database Management System. Although using referential integrity can be frustrating in some cases, as it restricts your ability to enter foreign keys before a corresponding primary key exists in the related. This applies even if you plan on adding the same data to the primary key seconds later. However, the benefits brought by referential integrity far outweigh the slight inconveniences that can occur, and in the end a major problem caused by not having referential integrity is far worse than having to enter the data in a specific order.

By now, I’m sure some are asking what problems referential integrity prevents. Well without referential integrity, you can enter foreign keys that do not match the corresponding primary key in the related table, depending on the type of database this could cause a lot of problems such as mismatched customer data and mismatched transaction records. Obviously this type of mistake could cause a huge loss of money and time if the mistake is not found in time.

Luckily for developers using the MS Access Office program, referential integrity is quite easily turned on and is an important feature in Microsoft Access. Simply select the “Design” tab and then “Relationships”. From there click and drag the two fields you wish to connect, you will be prompted with the following dialogue box where you just need to check the “Enforce Referential Integrity” and hit “Create”.

Microsoft Access Basics

To the new user Microsoft Access can be a bit confusing, although it’s similar layout to the rest of the MS Office Suite provides a sense of familiarity, it might not be enough. There are some basic things that everyone should know about when it comes to MS Access, most importantly what tables, forms, reports, and queries are. First and foremost are tables, the heart of any database, basically tables are where the data is stored. You can have many tables for a single database and they consist of records. In most cases one row is one record, and a record would contain information under individual columns, for example a database storing customer data would have one column for each piece of data, in most cases, the custome name, address, phone number and so on.

Another integral part of Microsoft Access are forms, forms are the best method of both displaying data and allowing a user to input data to a table. They can be customised to only show the data you want, and to only allow certain pieces of data to be entered, their appearance can be heavily customised making them the easiest and most asethetically appealing method of allowing clients or users to interact with the database in a meaningful way. One of the best methods of outputting meaningful data into an easy to read format is through reports, reports are simple to make especiallyy with the wizards provided by Microsoft Access but are invaluable to any business.

Lastly we have queries, again made simple by MS Access queries are your best friend when looking for specific data or for mathematical calculations on data. Queries enable you to display records that met a certain criteria, sort it and much, much more. Not utilising queries is a massive waste of an extremely valuable tool. Although there is a lot more to Microsoft Access than these four aspects, they are a great start to learning the intricacies of MS Access and are incredibly important to databases in general.

Why would you use Microsoft Access?

It’s an interesting question, and generally asked by kids at school who look at the Microsoft Office Suite briefly during school. Many of these kids are of course now adults who never originally were answered when they asked “What is Microsoft Access?” Most schools quickly cover the other programs in the Microsoft Office Suite, all kids know that Word is for typing up assignments, Powerpoint is for making slideshows, and Excel is for maths and data manipulation. But most schools wont teach a student what MS Access does, unless they take Year 11 or 12 IT courses.

What most people never really find out is how powerful a tool MS Access really is, as a Database Management System it is extremely easy to use. Not only that, but due to most people’s familiarity with the Microsoft Office Suite of programs, they are hopefully by now proficient at using the ribbon design and the standard MS Office User Interface. Most users, however will most likely never create a database, despite databases being one of the most important aspects of modern living. Bank records, Government records, all are stored in electronic databases and without them 21st century living would be far more time consuming and expensive. Unfortunately, this lack of knowledge about databases can often be the downfall of small businesses, many businesses think that they can hold all their records in filing cabinets or other extremely inefficient systems, when most have Access(pun intended) to a simple to use database management system that can easily fulfill their needs.

Admittedly Microsoft Access does have a slightly higher learning curve than the rest of the MS Office Suite, and this is perhaps the reason why most people tend to shy away from it. This in itself is fine, not everyone is cut out for the complexities involved in creating a database, fortunutaly there are many IT businesses that are extremely proficient at creating powerful and invaluable databases using MS Access, providing businesses with the tools they need to succeed.

Of course advanced database creators could point to MS SQL Server and a variety of other Database Management System programs as being far better, but the point is that MS Access has a large degree of familiarity due to it’s MS Office roots and is relatively simple in comparison making it far easier for both unskilled users trying to make their own database, and for IT companies creating them for clients who will be instantly familar with the UI.