So I decided I’d try and get myself out of 2003 by upgrading my Microsoft Office Professional suite to the 2007 version. However, I really wanted to try it out first before I committed to upgrading. The question is: Where do I find it? Surprisingly enough, Microsoft actually offers a free 60 day trial for the different 2007 Office components. Better still, these trials provide full functionality as opposed to the watered down versions you sometimes get with trial software. I was a little surprised because I don’t remember Microsoft offering free trials of their previous office versions (I could be wrong though, it’s been awhile since I bought one).

Here’s the link to the download page: Microsoft Office 2007 Free Trial. I’m going to download it and give it a test run, I’ll post my thoughts at the end of the trial run.

I thought I’d mention this site, as it’s a great resource for Microsoft Office articles. Basically, it aggregates these articles from multiple sources (with the author’s permission) and provides a little synopsis for each one and a link to the original article. I like this site because it’s quick loading and it provides an easy to use interface. Another good feature is that it’s not littered with ads like a lot of the other aggregation sites out there.

It’s definitely worth perusing if you have a chance, as there articles on a ton of different Microsoft Office related topics. Here’s the link:

Microsoft Office Reporter.

As I have talked about in previous articles, there are many areas where Microsoft Access and Microsoft Excel overlap. Because of this, there exists the ability to quickly interchange data between the two programs. In this article, I’m going to go over a few simple steps to transfer all of the data from a table in an Access database to an Excel spreadsheet.

Read the rest of this entry »

This is the second part in my two part Access Vs. Excel series, where I’m basically trying to differentiate when you would want to use one of these tools over the other for a project. This question comes about because of the many similarities that these two tools share. You can see the first article here: Access: Vs. Excel: When to Use Access.

Here are three example cases where I think you would want to use Excel over Access:

• You need to create a one-time, formatted report - If you’re creating a one-time report based on a set of data, then Excel is really the way to go. On the average, setting up a new report model in Access takes longer than creating a new report model in Excel. This is because in Access, you have to setup the database and other backend structures, while in Excel you can pretty much start plugging away from the get-go. If it’s only a one or two time report, you don’t need to take the time to setup a whole database for it. Plug the data into Excel, throw a format at it, and you should be good to go.
  
  
• The relationships/fields won’t change - This basically means that once you put the report together, the structure of your data won’t change, and neither will the relationships. For example, say you are creating a spreadsheet that shows all of your customers and key parts of their company info. This is pulled from a hidden spreadsheet that contains all the info from each company. This is fine, however, if you want to add new fields to the company info, this could cause cascading cell reference problems. Also, if you have some complex relationships between data fields, it’s much harder to check to make sure that they are still intact and correct. As long as your backend is pretty set (relationships and fields), Excel can be a good tool because of the shorter setup time involved.
  
  
• The report format won’t change - Don’t get me wrong, I think that Excel does have some flexibility when it comes to changing an existing spreadsheet. However, once you get past a certain point of complexity, it gets very difficult to make changes that don’t break other parts of the spreadsheet. For example, if you have a bunch of different formulas with cascading dependencies, changing just one requires you to check in multiple different places to make sure that nothing broke. I prefer Access for these kinds of reports. However, if you don’t have to change your format too drastically, I recommend Excel.

It’s critical to understand that both Access and Excel have their uses. Sometimes these overlap, most of the time you can clearly see which one would be better to use. It may not seem like it now, but as you get more familiar with both tools, you’ll see which one is better to use in certain situations. Also, there are always exceptions to the rules. Maybe your client doesn’t have Access, maybe they don’t like to use Excel. You might be forced to use one, all you can do in that situation is roll with the punches.

To many people, there is some confusion as to what the difference is between Microsoft Access and Microsoft Excel. To try and clear some of the confusion up, I’m starting to write a series on Access Vs. Excel. In this part, I will be examining example cases where it’s better to use Access rather than Excel.

Here are the three example cases:

• You need to store complex dynamic data and report on it - In this case, you have data that is going to change over time and it’s not straightforward (think many interrelationships). Not only that, but you will need to be able to produce reports on this data even after it changes. While this can be done in Excel, it is usually easier to do this in Access. One reason for this is because it is generally easier to dynamically alter/add/subtract data in Access once you have the database setup. Consequently, because your reports in Access will be based off of database queries, you shouldn’t even have to update the reports to get the new data, you should just have to run the queries. In Excel, you will spend hours deleting rows, adding rows, reformatting your report, and all the other fun tasks that follow, and you’ll have to do this each time you change the data.

• You will need to be able to create custom reports on the fly - This is another huge advantage for Access. Once again, you can design new queries easily in order to create a custom report. All you then have to do is run the queries to get your new report. In Excel, this would be a much more arduous process, as you would probably have to create a new spreadsheet, link the cells, transfer the data, format it, etc.

• You need to store your data for a period of time - If you need to store your data for the long run, Access is the hands down choice. It is much easier to take this data later and put it into other databases or manipulate it in your own database rather than digging through an excel workbook trying to cut and paste what you need, or change it.

Even though Access is the better choice in the example cases above, there are times when Excel is the better option. The next article in this series will deal with these example cases.

Many times the name of a column in a table may make perfect sense to you, but to someone not familiar with the structure of your database it could be very confusing. Consequently, when you need to create queries or reports for these people, you can’t use the actual column names. Another example would be where you need to combine the values in multiple columns or perform other operations on them. For the end user, all you want them to see is the result in a single column. This is where the AS statement comes in.

The AS SQL statement allows you to alias a column with another name, or put the result of multiple operations in one result column.

Here’s a simple example. We have a table named CustInfo with a number of columns, and we want to pull the following three columns from the table:

• CustName - The customer’s name.
• CustAddress - The customer’s address.
• CustNum - The customer’s phone number.

CustName and CustAddress seem pretty straightforward, however you will want to rename them for your user into more friendly names. On the other hand, the name CustNum could mean many things to some not familiar with your database structure. We’ll want to rename this to “Customer Phone Number”.

Here’s the query we would use:

SELECT CustName AS [Customer Name], CustAddress AS [Customer Address], CustNum AS [Customer Phone Number] FROM CustInfo

*Note: This syntax is specific to Microsoft Access. The syntax may change depending on what database platform you are using.

After this query is run, the following would be returned:

As you can see, the column titles are much friendlier to an end user than the original column names.

Another example occurs when you want to perform an operation on multiple fields and have it come out as one field. For instance, let’s say we have the table below:

What if we want to total the two values in each record and return only this total in our query? The query to accomplish this is shown below:

SELECT (num1 + num2) AS Total FROM Table1

This is the result of running this query:

The resultant query has returned a clean table back to the end user, without them having to be aware of what’s going on underneath.

Overall, the AS statement is very easy to use, and very powerful. I find it especially useful when you have to create queries where the results can be put directly into a report with little to no manipulation of the result table.

This is coming straight off a problem I helped a co-worker with today. They were running some simple queries in Access, however they couldn’t nail down one problem: How to retrieve only those records from a table where the first part of their query field equaled the value “yes”. Their problem was that they had other information in the field beside the “yes”, but that’s all they wanted to query on.

The solution to this is very simple, and it comes in the form of the LIKE statement. This operator allows you to find all the records in a table where the value in your specified field matches the Regular Expression you provide. The detailed definition of Regular Expression can be found here. In short, it is basically a pattern that Access will match against the value in a field.

For example, the Regular Expression ‘yes*yes’, would match any values where the first three characters are “yes” and the last three characters are “yes”. The “*” specifies that any other characters can be between the two “yes” blocks. Any time you want to specify “any characters” in Access, you can use the “*”.

The syntax for the LIKE statement is as follows:

field LIKE ‘Regular Expression’

To break this down:

• field - The field to which you will be selecting the records on, and which the Regular Expression will be matched to.
• LIKE - The operator.
• Regular Expression - As described above, the pattern you want to evaluate the field on.

As an example, suppose I want to select all of the records in my table where the “CustomerName” field begins with the letter “A”. That statement would look like this:

SELECT * FROM CustomerName LIKE ‘A*’

It is also important to note that LIKE statement when used in Access is case insensitive. Using the above example, any value that began with “A” or “a” would be retrieved.

Those are basics of using the LIKE statement. I think you will find it increasingly useful as your skill with Regular Expressions increases.

Picture this: You’ve just inherited a database from its previous owner. You’re new to the project, but you have substantial database experience. Opening up the database for the first time, you begin to take a look around some of the forms to try to get a feel for how everything works. The first Control you look at is named “Text9″. Trembling, you look at the next one: “Label7″. This is when the horror sets in.

Does this sound familiar? If you’ve ever been in an experience like this, then you know how important it is to take the time to name your Controls. In case you’ve never been in this situation, here are three compelling reasons why you should do this:

• It will make it easier for others to learn how the database works -Descriptive names will allow someone who may have subject matter expertise but no experience with your database to pick up the structure quickly.

• It will make the database more maintainable - This somewhat coincides with the first point, except it also applies to you. If you have worked on a part of the database for a few months and then suddenly you have to fix or change something, meaningful names will allow you to get right back into the swing of things.

• It will keep you sane - This is absolute truth. When you’re knee-deep in development, trying to work with “Text9″ and “Label11″ is going to drive you mad. Do yourself a favor before you go off the deep end.

Note: For our purposes, we’ll define a Control as pretty much any item you can place on a Form. Some examples of this would be a list box, text box, label, etc. Basically, anything that you put on a form should have a meaningful name.

There are two basic parts of naming a Control: Physically naming the control, and naming conventions. These are described below.

How To Name a Control

This is simple. First, right click on the Control you want to name and select Properties (Alternatively, you can double-click on it). The window pictured below should open (in this example I used a Text Box).

The part circled in red is what we are concerned about. This is the Name field, and this is where you will be making your edits. When you’re done making the edit, simply close the dialog box and your changes will be saved.

Naming Conventions

If you have any programming experience, naming a control can be likened to naming variables. If not, don’t worry, it’s a simple concept to grasp. When you name your controls, you want to adopt the same Naming Convention for each control. A Naming Convention is basically the way in which you construct a name for each Control.

One example that I use is to capitalize the first letter in each separate word in the name. For instance, if I want to name a text box where the user enters their name, I might use “UserNameEntry” as the Control name. Alternatively, you could also use an underscore to separate the words, creating “User_Name_Entry”.

It is possible to use spaces in-between words, however I don’t advise this simply because it makes programming in the backend possibly harder since you have to make sure the full name is read when you reference it (if this seems Greek to you, don’t worry, it is more for the intermediate to advanced users). This is why I prefer the first example, since it is no hassle to use.

You can always come up with your own, just remember to be consistent and descriptive across the board.

Overall, the small amount of time it takes to name a Control properly is well worth it when compared the enormous amount of benefits that you’ll see.

How you design your tables can make or break your database. It doesn’t matter what you have working on top of it (i.e. forms, code modules, etc.), if your underlying table structure is not sound you’re in for a lot of headaches. With that being said, designing tables for your database doesn’t have to be torturous, as along as you follow a few basic guidelines.

First, make sure you fully understand the purpose of the database. If you don’t, you’re not going to be able to create all of the right tables and relationships to make it work correctly. Get the requirements from your client (which could just be your supervisor), and make sure that you ask questions about anything you are not sure of. One thing is for certain in any kind of project lifecycle: The farther down the road a problem gets, the more costly it is to fix it.

Next, before you create even a single table you need a Database Blueprint. This is basically the structure for your database. It should include all of the tables that you want to create as well as their associated fields. Also, the Primary Key(s) for each table should be specified. Once this is in place, creating the tables themselves is quite simple.

How do you go about creating this blueprint? First, you need to decide what tool you are going to create the blueprint in. Unless your database is going to be pretty large, you can probably use Microsoft Excel. If you think your database might be relatively large (say 20+ tables), there are a number of commercial software products out there that are specifically designed for database design. These can get expensive, but they are worth the money in the long run.

Let’s say you decide to use Microsoft Excel. I would suggest creating a template to display the table layouts, then creating a separate Excel document based on your template for each planned table. Some of the basic fields in your template should be:

• Table Name
• Field Name
• Field Data Type
• Field Size
• Field Description

Also, you should highlight the Primary Key(s) in some way so that they are easily identifiable. The Field Description is especially important to include, because if somebody who is not familiar with the database needs to change it later on, these descriptions will help them to understand how the database works.

Now that we have our template ready, it’s time to get down to work. Layout all of the information that you think you are going to need to store in the database. For example, if I’m creating a database to store customer information, I’m going to need their names, addresses, payment information etc. After you list all the information you need, group this information into logical sets. You can use the same field more than once if you have to.

Here’s an example containing the following fields:

• Customer Name
• Customer Address
• Customer Payment Type
• Order Number
• Order Description
• Order Due Date

It looks like I’ll want to create two tables out of this: One to hold the first three fields, and one to hold the last three. We’ll call them “Customer Information” and “Order Information”. The initial tables are set, but how will they relate to each other? This is the next step: Determining the table relationships. In the above example, it looks like I’ll want to be able to link an order to a specific customer. To do this, I’ll add the “Customer Name” field to the “Order Information” table. This way, when we are processing an order, we can use the “Customer Name” to do a look up on the “Customer Information” field to get their address and payment information.

*Quick Tip: If you can make your table lookups use number compares instead of text compares, do it. Text compares are generally slow than number compares. In the above example, you could add a unique ID number per customer then use this ID number on the “Order Information” table instead of the “Customer Name”.

Also to note, I used descriptive field names in this example. This is something that you must be aware of every time that you are designing any kind of database. Your field and table names should always be descriptive of what they represent. For example, if you name your tables “Table 1, Table 2,…” you are just going to get confused later on as to what table holds which information. This is especially true in large databases. Not only will you get lost in it, but anybody else who tries to maintain it will be unable to do so as well.

Now decide what should be the Primary Key(s) of each table. You can have more than one, and this is common in complex databases. For our examples, let’s make “Customer Name” the Primary Key of both tables.
Your Primary Key needs to always be a unique identifier for a single row on the table. This is an important decision to make, as it could have major consequences to your design later on if you have to change it.

In summary, we have accomplished the following:

1. Have a full understanding of our database requirements.
2. Decided to use Microsoft Excel to create our Database Blueprint in.
3. Created our template to use when designing our tables.
4. Decided what information we’ll need to store our database.
5. Sorted this information into logical groups (our tables).
6. Defined the relationships between our tables.
7. Completed our design for each table

Our Database Blueprint is now complete. Take another look at it to make sure you didn’t miss anything. It’s also a good idea to have somebody else review it to see if they can add any input.

You’re now ready to actually build your tables. Remember, you can change something if you have to, but make sure to keep your designs updated at all times. This is extremely important because if your designs are not up to date, you have to go into the database each time you or anybody else has a question about a field.

This is a question that I get more often than you might think:  How do you embed a document, such as a Microsoft Word document, into a field in a table in Microsoft Access?  It may seem like the answer to this would be complicated, however it actually is quite a simple process.

To begin, let’s define what you will actually be embedding.  You will be embedding what is called an OLE Object.  OLE stands for “Object Linking and Embedding”.  It is a standard created by Microsoft that allows you to create an object in one application (say a Microsoft Word document) and embed or link it into another application (such as Microsoft Access).  For a slightly more detailed definition from Webopedia, click here.   In our example, I will be using a Microsoft Word document.

First, go to your table in Design View.  You can do this by following steps 1-3 shown below:

1. Click on Tables.
2. Highlight your table.
3. Click on Design View.

You should see a screen like this come up:

 

This table has two fields.  One is the ID field, which is just an auto-number to be our primary key.  The other field is the test field.  This is where we will be embedding our object.  It has been defaulted to Data Type “Text”.  Change this to “OLE Object”.  Close the table and save the changes to it.

Now, let’s embed a Microsoft Word document into a sample row.  Open the table by double clicking on it. You should get a screen like this:

Right click on the empty test field of the first row.  Select Insert Object from the menu.  The following menu will appear:

Here are your options:

1. Create New - You can pick an object to create from the list box and create an empty object of that type into the test field.  You can then double click on that field and that object will open up in the native application.  For example, a Microsoft Word document will open up in Microsoft Word.  You can then save changes and it will be stored in the database. *Note: be careful, if you just close a document while it is open it will save, make sure to undo your changes before closing if you don’t want them to be saved.
2. Create from File - You can copy an existing object into the database, and then edit that copy in the database (like for a new object) without affecting the original object.  If you choose to link it (there is a “Link” checkbox for this option), the original object will be changed when you edit it in the database and vica-versa.

Choose either an existing Microsoft Word document or create a new one and click “Ok”, and there you have it.  Try editing the document for yourself and see the results.  Also, play around with the options a bit, this tutorial is just a quick way to get started with embedding objects. There are also multiple different options for using this in Forms, depending the purpose of your application.