Microsoft Access and Conditional Formatting

A useful tool in Microsoft Access is conditional formatting, where certain formatting is used when certain conditions are met, like a field containing a particular value or set of values, or when the field has focus. This helps MS Access Developers and Consultants tailor specific elements in reports or forms to a client’s needs, for example you can create a report which uses standard accounting practices like using black text to denote a profit or red text to show a loss.

Although not the only use for conditional formatting, it can also be used to highlight specific fields to bring attention to certain values or fields when required. Overall, conditional formatting is yet another tool provided by Microsoft Access to aid Developers and Consultants in delivering powerful and valuable databases to their clients.

Buttons and Microsoft Access

On this blog, we’ve been talking a lot about the powerful tools available to MS Access Database Developers and Consultants, so far we haven’t talked about buttons yet. The versatility of buttons in Access is amazing and contributes significantly to a developer’s ability to create functional and engaging menus, forms and reports. At their simplest buttons can be used to navigate through all the records in a table when displayed through a form, or they can run macros filled with a several commands. Most users are familiar with buttons, so adding them into menus makes it a lot easier to create functional UI’s.

The button command wizard is great for creating buttons that perform actions, such as opening reports, printing reports and a variety of other tasks. Although advanced database developers and consultants can configure the effects of button through the properties tab and this allows them to do even more with them. Without a functional UI most databases would be clunky and difficult to use for new users and even some intermediate users, and one of the most important aspects of a functional UI is appropriate use of buttons and not something any good MS Access Database Developer or Consultant should forget.

Importing Excel Spread-sheets to Access

As some people may have noticed, comparisons between single tables in Access and Excel spread-sheets have been made several times, both on the Internet and in this blog. This might have even convinced a few people to recreate some of their spread-sheets in Access, luckily for them and Database Developers and Consultants, Microsoft has helpfully included the ability to import a variety of file formats into MS Access with only minimal configuring on the part of the user. This can save both regular people and developer’s lots of time as they or their clients’ needs may change to require a database rather than a spread sheet or similar file.

It’s really simple, just select the “External Data” tab in Microsoft Access, then select the “Excel” button and follow the prompts to import and configure your spread sheets into tables.

Macros in Microsoft Access

Macros in Access are in essence a programming language, however, simplified by a large margin to reduce the necessity of VBA in Access. This can help new Microsoft Access Database Developer or Consultants in creating powerful databases that take advantage of this ability, it can also help seasoned Developers or Consultants as for simple tasks like making a button that uses a macro to open a report is simpler than creating a button that uses VBA to open a report.

This ease of use does have its downsides however; macros are an excellent way of embedding viruses into Databases, or essentially any program that uses macros which range from MS Word to MS Excel. Luckily, most anti-virus software is designed to detect macros; it’s still in ones best interest to only enable macros in documents from reputable sources.

To create a macro, first select the create tab in MS Access. From there click on the “Macro” button to bring up the macro editor.

Selecting the arrow will bring up an list of commands that the macro is capable of, you can also add multiple actions to the macro making them even more useful.

For example if we select the “OpenReport” option, it will bring up an easy to use menu that will allow you to select which report it opens up and a variety of other options.

Creating a macro is easy as that, just save it and you can run it through a variety of ways, you can press the “run” button or create a button on a form and linking it to the macro. With creating and running macros as easy as that, there really is no excuse from Developers or Consultants to not use them.

Relationships in Microsoft Access

Relationships are an integral part of any database, not just Microsoft Access, without it all you really have is a series of spread sheets, and at the point you may as well just use MS Excel or some other spread sheet program. Relationships are your best tool for preventing data redundancy and storing the data in your database as efficiently as possible. Relationships allow you to refer to data in other tables without having to duplicate it yourself, and if referential integrity is enabled it ensures the data is synced correctly. Not needing to duplicate data is useful as it prevents having to enter in data multiple times, meaning more time saved and less chance of human error. Even if there is a typo or other similar mistake in the original table, if referenced in multiple tables, you will only need to change the record once rather than how many times it is referenced, which in some databases could be in the hundreds of thousands.

Any Microsoft Access Database Developer or Consultant worth their salt would take full advantage of the power afforded by the relationships in Microsoft Access. On the surface, relationships probably seem relatively simple, but just like in the real world, relationships can be quite complex, although I haven’t heard of a clingy or cheating database yet. In Microsoft Access, there are three types of relationships, one-to-one (or monogamous if you want to continue the joke.), many-to-many and one-to-many. Essentially in a one-to-one relationship only one record in table A can reference one record in table B, in a many-to-many relationship you can have one record in table A refer to many records in table B and vice versa. Although many-to-many relationships need a third table containing the primary keys of table A and table B as its foreign key to work in MS Access. Finally a many-to one-relationship are where one record in table A refers to many in table B. At the end of the day, Microsoft Access Database Developers or Consultants have a very powerful tool in their arsenal, and they’d be foolish to not take advantage of the flexibility provided to them by it.

 

Why Is Data Redundancy Bad?

Any good MS Access Database Developer or Consultant would know that database redundancy is a big problem for any database, large or small. It might not be immediately obvious to laypeople as to why exactly data redundancy is so bad, there’s a bunch of reasons ranging from the obvious to the not so obvious.

Arguably the worst part of data redundancy is the fact that the database are unnecessarily larger this might not seem like such a problem these days with our 2TB HDD, but it was a huge problem not too long ago when space was an important commodity. But hand in hand with larger file sizes is slower speeds and that is still a concern for people these days, particularly clients who wouldn’t be too happy knowing that a poor database developer or consultant allowed a easily fixable problem that affects profits get past them.

There is also the issue that the database has to be updated in more than one place; just a simple mistake could have two different values for the same record, which could cause catastrophic failures in the database, and depending on the use of the database could cause thousands of dollars of damage or even worse, could put lives at risk through something easily avoided with through planning.

The Importance of a Properly Constructed Database

A properly constructed database as the title suggests is, of course very important. But I’m sure you’d be hard pressed to find someone who believes a poorly constructed database is good, so it’s probably best to delve into why it’s important and the consequences for not creating a database properly in the beginning. Every (good) Microsoft Access Developer or Consultant should be able to give you a myriad of reasons for creating a database correctly from the beginning, a good MS Access Database Developer should also follow some basic steps whenever they create a database to ensure it is being created properly.

To go into more detail, no good database ever started without a basic plan being formed beforehand. The best first step is to decide what exactly the database is going to do in the beginning, it might seem obvious but many people want to jump straight into making the database and forget to sit down and really think about what the purpose of the database actually is, although enthusiasm should be applauded when it comes to making databases as it can be a frustrating experience, it’s misdirected energy if there is no set plan or purpose. Making up a database as you go along becomes the database equivalent of the Titanic sinking, and benefits no one. A cohesive plan also prevents a lot of bad database design decisions and mistakes, like accidental or preventable data redundancy, and the associated negatives that come with redundancy like bigger and slower databases. One of the marks of an excellent MS Access Database Developer is a lack of data redundancies in their databases and it really shows in comparison to a database bloated through redundancies.

The proper use of primary keys is another sign of good database design; a primary key has to be a unique identifier for each record, for example, although at first, a last and first name might be a good indicator for uniqueness but the chances of someone having the same first and last name is too great, think of all the John Smiths out there which would clearly break the database. So some thought has to go into assigning or creating a primary key for each table. Of course that is second nature to any good MS Access database developer or consultant, but to the uninitiated among us considerations such as these might be overlooked, leading to frustration, broken databases and depending on how much stock you put into the database, broken dreams.

The Power Of Validation Rules in MS Access

One of the most powerful tools in database development, Microsoft Access or otherwise is validation rules. For the uninitiated, validation rules allow you to set up what data can be entered into specific fields. For example, in a date of birth field you would only want a date entered, so you would set validation rules to only accept dates. On top of that, you could also set an acceptable date range, a vast, vast majority of people will never make it past 110, so you’d make sure no one could set it to older than that.

There are many possible uses of validation rules, you can use them to reduce the amount of characters a field gets, reducing the overall size of the database, this would also help stop people from abusing the system by putting excessive amounts of characters in a field. Over all, without validation rules the data integrity of a database can be destroyed by accident or purposely very quickly.

To create validation rules in MS Access is very easy, here’s how to force a field to only accept 8 characters. Enter the following string without the outside quotation marks “Is Null OR Like “????????”” in the Validation Rule section as circled below.