Introduction to Microsoft Access

Or, how to begin irritating your IT department

Microsoft Access is a powerful self contained database tool that can be used to create and manage real relational databases.  It’s designed to be easy enough to use so anyone can manage a database, without any prior database experience or programming skills.  This is both a blessing and a curse for Access, since many a poorly written database application has been integrated into daily business in many companies, typically without IT knowledge or support.  This leads to an application becoming important to do business, but once the creator of the system leaves, so does all support.

The goal of this article is to give the uninitiated enough information to understand what they’re playing with and hopefully some pointers to create supportable and stable applications.

Database Vs Spreadsheet

All too many companies rely on spreadsheets for daily logging of information or tracking work.  These tools can be really handy for calculations, analysis, and charting of data, but once you’re just using them to keep track of data, you’ve begun using a spreadsheet as a database.  There are a few problems with this approach:

• Data validation is non-existent.  Have a column that’s supposed to be a date?  Excel doesn’t care and will let you fat finger all kinds of errors in your dates.
• Excel allows only one user at a time.  Excel does have sharing capabilities, but in practice it’s a bit difficult and can cause more trouble than it solves.
• Searching and sorting is unnecessarily difficult, particularly if you’ve got separated data on multiple tabs.

Moving your data tracking to a real database can solve all of these issues.

Navigation Pane

In Access 2007 and later, Microsoft has organized all the objects in an Access file on the left side of the screen in what they call the Navigation Pane.  Using a drop down at the top, you can organize them by type or how they relate together.

However they’re organized, you have 5 basic types of objects to deal with:

• Tables
• Queries
• Forms
• Reports
• Modules

Tables

Tables define what data is going to be tracked and stored.  They have a defined set of columns with associated data types and a virtually unlimited number of rows that can be added.  Before you begin with a database design, the very first thing you must know is what you’re storing.  Access gives you a handy design view interface that allows you to control exactly what will be stored.

You’ll be defining these items:

• Field Name – This is the heading of the column.  Access will let you name it whatever you want, though a real database is more efficient with short names with no spaces or special characters.
• Data Type – This defines what will be stored in that column.  Once you set this, attempting to enter another data type will fail.  Here are a few of the important types to deal with:
  • Text – The basic type that will allow whatever character you throw in to be saved.  This can be up to 255 characters wide.  Set the field size to the largest value you can expect so minimize the file size
  • Memo – This is just like a text field, but allows a huge amount of text to be stored.  It can also store rich formatted text (starting with Access 2007).  This does use a relatively large amount of storage space, and cannot be used as a related field between tables or queries.  Additionally, these can be less stable and more prone to corruption in an Access file so use these sparingly.
  • Number – Numerical data only!  These can be used for calculations later and formatted however you like.  There are several types of numbers to choose from in the properties.
  • Autonumber – This is a simple integer, but it automatically increments every time a new row is added.  This is super handy for primary keys or other unique identifiers.
  • Currency – This is a special case for a number field that automatically enforces the number of decimal points and the formatting of the content.
  • Date/Time – This is used for dates and uses Microsoft’s serial date format for storage.  This is really important to use, since it allows you to do all sorts of date manipulations later.
  • Yes/No – This is a simple check box field, like a single digital bit it can either be 1 or 0.  Use this for option selections to keep it simple and use little space.
  • Attachment – Starting with Access 2007, this special field was added to store files directly in the database.  This is also more prone to corruption and will hit a 2GB file size limit if use too heavily.
  • Description – This is a simple note for you and your users to understand what is supposed to be entered in the database.  It will automatically display on the status bar when  a form is later created and tied to that field, but otherwise it’s just for your reference.

Once you have the data type set, the properties section at the bottom of the design windows allows you to further control the table.  This section changes based on the data type selected, but there are a couple key items to consider

• Indexed – Adding an index to a field will greatly speed up searching and using the field as a relation to other data.  This comes at the expense of storage space and can be used to force a field to have no duplicates.
• Default Value – Setting a value here causes all new records to contain that value until it is overwritten.
• Required – Set this to Yes to force a user to enter a value before the record can be saved
• Format – This sets a default display format for the data, But can be overrode in reports and forms anyway
• Input Mask – This can be used to force a particular input to the field, handy for phone numbers or items that require a particular format.

What’s a primary Key

Creating a Primary Key in a table identifies the field that will always be the unique identifier for that record or row.  This is required for setting up relationships and is generally good practice.  Only apply the primary key to a field that is definitely going to be 100% unique, or add a new autonumber field for that purpose.

Relationships and Normalization

This is where the real database work begins and could probably spawn a whole article itself, so we’ll keep it brief here.  The idea of relationships is that one table contains additional information that adds to an item in another table.  For instance, perhaps you have a customer table that contains names and contact information.  Then say you want to log contacts, sales, or items owned by that customer.  To do that, you could create a second table that logs the sales data, and relate it to the customers using their customer ID or whatever you happened to call the primary key for your customer table.

Doing this cuts down on the number of columns required per table and allows more flexibility than using one big fat and wide table with a bunch of extra columns waiting for data.  This is also handy when some of the fields will need duplicating, like the customer address.  There’s no need to keep repeating the address every time that customer has a sale, so it’s kept in its own table as a single record.

Eliminating duplication of data and minimizing columns is what is generally referred to as Normalizing a database.

External Data

One of the more powerful and handy functions of Access is its ability to aggregate data sources from pretty much anywhere and use them through queries, forms and reports.  This can be done by either  importing or linking to other data sources

Importing – Brings in the data to the database file as a new table of data.  This is useful for working with the data, but it does separate it from the source.  If you make changes, they will not be reflected back to the original.

Linking – This creates a virtual table that simply points to the original source data.  This is where the real power lies, since when the source is updated, the database sees the changes immediately.  In many cases, the link may be used to actually change the original source data as well.

Access can link to many types of external data:

• Other Access files – This is the simplest and most straightforward.  They appear just like tables, except the data is in another file.
• Excel Spreadsheets – This is handy, but often problematic.  The link will not function when the spreadsheet is open and being worked.  Also, Access tends to decide for itself what type of data is in each column based on the contents of the first few rows.  This can lead to data type problems, especially since Excel really doesn’t care what type of data you put in its cells.  Linked spreadsheet cannot be changed though Access (read only).
• Text Files – Any delimited text file can be linked as if it’s a table, with limitations similar to the Excel links.
• ODBC – This is a system driver data source model that can be used to connect to real database servers like Microsoft SQL server, Oracle , MySQL, PostgreSQL, and many others.  The downside is that each computer using the database requires a driver installed, and either a system DNS added through the administrative tools or some code work to link.  The administrator of the source database may have to assist in getting these up and running.

Queries

Here’s where you work on pulling data back out of your tables.  These can do all kinds of fancy things you can’t do in a spreadsheet, like relating the data to other tables, reformatting and sorting the outputs, or grouping, counting and subtotaling data.

Access has a nifty visual design tool to create and maintain queries.  These can pretty easily be abused to create confusing and poorly organized messes of queries, and learning how to avoid this generally takes practice.

A basic query starts with one source table.  From this table, you may pick any number of fields and display them in the result.  In the field lists, you may specify your sorting and apply a filter.  In addition to simply displaying the fields, you may rename them by applying an alias, this is accomplished by typing what you want displayed, followed by a colon, then the field name.  In a similar fashion, any function may be applied to a field, or you can specify a static output field.  For example:

• Alias: [Field] – displays the field called “Field” and names the column “Alias” for output
• Alias: [First Name] & “ “ & [Last Name] – will display the full name of a person by putting a space in between the first and last name fields (the “&” is a special character that joins data together)
• Alias: [Pay Rate]*[Hours] – This will multiply the pay rate by the hours in the query and display the result.
• Alias: Function([Field]) – This will apply the specified function to the field and display the result.  This will work for any built in Access function or even ones you create yourself in a Visual Basic module.

Joining Tables

The real work of queries is when you relate multiple tables together.  By dragging one field on top of another in a different table, Access creates a join on that field.  By default, they must be exact matches, so the data will only be displayed if the exact same data is in that field in both tables.

A right or left join will be shown with an arrow on one side, that means that the table with the arrow pointing to it will only be shown if the field matches, but ALL data will be shown from the other table.  When there is no match, the fields from the joined table will be displayed as a special blank character called a NULL.  Nulls are finicky buggers, they’re not 0, they’re not text, they’re not anything.  For that reason, as soon as you have a null in a math formula, the result will always be null.

Using Queries inside of Queries

Queries work just as well with a query in place of a table, in this way you can build up more complicated analysis tasks by taking them in small steps and building up.  Be careful though, this can lead to a nasty organization problem and a whole headache when one of these nested queries doesn’t work.  Your best bet is to use as few as possible in a nested query, and be careful when you save and name them.  For example, keep a particular prefix on a group of related queries to make it easier to find them later.

Forms

This is where you build a user interface.  Access has many little wizards and shortcuts that can help build a useful form.  Of course, to an experienced programmer, these wizards are silly and can produce terrible Visual Basic code, but if you’re reading this, you’ll want to start with a wizard.

Forms will let you organize and display your data and allow users to easily enter and maintain the data in your tables.  The design of the user interface is a big topic in and of itself, so that will be detailed in another article.

Reports

The primary purpose of the reports is to turn your ugly and unformatted spreadsheet table style queries into pretty outputs that can be printed hard copy or sent along in a format like PDF.  These can get pretty powerful if you’re patient and play with them.

Your best start on working with reports is to simply work through the creation wizards that Access provides, then simply open them up in design view and tweak away until it shows you what you like.  Add lines for visually differentiating data section, add images for logo’s etc.  Basically, anything you could create with Word, can be created with an Access report with a little work.  When it’s done, you’ll have a pretty looking word like document with content driven from your database instead of typed in yourself.

Modules

These little buggers are where any custom Visual Basic code can be stored and maintained.  This also deserves its own topic, so we’ll call it beyond the scope of this introduction.

Irritating IT departments

Typically, Access applications spring up and grow legs in business when users upgrade spreadsheets into database for tracking tasks they perform or making measurements on various data sources.  A well meaning user will simply start an Access file, do some neat tricks and show it to their fellow worker bees as a time saving or collaboration tool.  Then gradually, these databases grow and become an ever increasing important part of people’s jobs.

That’s where the problems happen.  A complex Access database blurs the line between a working file, and a full blown application.  In order to avoid causing problems with your IT department you should treat these as any software application and follow a few guidelines

• Keep your ad hoc Access applications to yourself.  If you’re just playing with queries and generating one time use reports or analysis, keep these to yourself and avoid publishing them to other users.  That way they don’t become critical and require some sort of emergency support headache for yourself or your IT department.
• Let your IT department know. When you do publish an application for others to use as part of their job, keep the application simple.  The main problem will be support, and you will be a big part of that as the application creator.  There’s an excellent chance a user will go cry to IT when the database doesn’t behave, and IT will be a bit miffed if they’re unaware of it and don’t know who created it.  Reverse engineering and fixing these things takes more time than a typical IT help ticket resolution has available.
• Document your database.  Use the description fields and properties to explain what your table fields are for, and what your queries do.  Use comment in your Visual Basic code so someone else can get a clue what you’re trying to do.  This is time consuming, but worth it later.
• Control revisions and backups.  Always back up the current version file, and keep prior versions archived in case something goes awry. A dedicated table for change tracking is a slick way to keep version information, but simply adding dates to your filenames as you archive them will do just fine.  Make sure wherever you store these things, they’re backed up on multiple drives, tapes, CD’s… whatever.
• Make sure users know who to contact when something happens.  And on top of that, make sure you’re ready to be responsive and correct problems promptly when someone runs into a bug.  If you can’t handle that, you shouldn’t be publishing and application for other users or you better have an alternate plan for their job that doesn’t involve your Access tool.

Follow those rules and you may be able to pull off some really nice efficiency gains on the job by utilizing a real database to capture, track, and report on whatever it is you need.  All that while NOT annoying your IT staff and getting on the corporate radar for all the wrong reasons.