Database Design and Abstract Thought

We're hard at work with a new product to be released very soon, and some interesting things have come up in user testing. One of the most common challenges we see for Microsoft Access users is the database design itself. And by design, I mean the tables and how they relate to one another. When you are working in Access, the form and report design are addictive tools because they are largely what-you-see-is-what-you-get (wysiwyg). But of course, without the underlying tables to store and present the data, your forms and reports aren't going to get you too far.

In the end, database design is an exercise in the abstract. Its taking very concrete concepts like:

Product X can be blue, red, or yellow
Product Y can be orange, gray, white

And then figuring out how to create a database that can help users select from the available options. To do so, you need to move from concrete to the abstract. Otherwise, you get an ugly database where you have a table "tblProduct" with check box fields for each possible color, and bunch of awkward code to make it work. But by thinking of the abstract concepts, which isn't always an easy feat, it's clear you want to actually have a separate table for color. Then, because each product may have more than one possible color, you have to create another table where you can marry them together.

So what's the tip for the day? When thinking of how to architect your database:

1. If you think of the "thing" in the plural, it's probably a table unto itself. Products, customers, contacts, notes, colors, etc. In some cases it's just a simple lookup table.
2. If you are naming the field and you label it in the plural, you may be describing a many-to-many relationship as opposed to just an attribute of the thing your table contains (as in the example we started with where you're tracking Products, if you started to name a field "Possible Colors," it probably deserves some reconsideration as just a field in your Products table).
3. Try to avoid creating separate tables just to differentiate the state of something. For example, if you want to track Customers and Prospects, and all the fields are the same, it's highly recommended that you keep them all in a single table, and have a field that refers to each records' state (customer or prospect). You can then filter your forms, queries, and reports if you need to show only one. Plus, if someone changes from a Prospect to a Customer, it's a single field value that changes. No messy transfer of data from one table to another to signify the change of status.

There are many, many resources to help you design database tables. They all require time to read and practice naturally. But we hope a few of the tips above might help you in your work.

Happy New Year!!!!

Access "14"

After returning from a brief session with Microsoft on some of the new features of Access "14", I thought many would be pleased to know that they are letting us confirm that VBA will live on in the next release of Access.

I can't share any other details due to confidentiality agreements, but I'm sure they won't mind me saying the Access community will be thrilled with the next release.

So cheers to the incredibly hard working folks at Microsoft that continue to make Access the leading desktop database, and to the promising future.

eBook Topics - Your chance to provide input

We're considering creating a free eBook on Microsoft Access. Yes, in part a selfish way to create awareness about our products. But equally important, as a way to help the many Access users that find the phone-book sized "how-to" guides available at bookstores too expensive and too unwieldy. We've got a general theme already, but this is your chance to weigh in. What would you want to see in a free resource for Microsoft Access? What are the questions you have about how to create really powerful Access databases?

That's a Nice Little Macro You Have There...

On occasion I run into comments regarding Microsoft Access that range from simple derision to downright vehement hatred. Seriously. But by and large, you'll hear things from "serious" developers like "oh, it's just a few macros in an Access database." When in reality, you may have a fully respectable business application built using Visual Basic. Why the hostility from professional software developers and IT? I'll do my best to explain as I see it.

The first reason is that, quite frankly, Access isn't everything that a C++, Java, or .NET application can become. In the eyes of some developers with a BS in computer science, Access ranks right along side Microsoft Excel (thus the reference to "macros") in terms of capability, reliability, and extensibility. Moreover, Visual Basic is the descendant of BASIC, which is sort of like training wheels on a bicycle. Most professional developers (I'm speaking in the sense of C++/Java/.NET developers. There are plenty of VB professional developers, too) think in terms of how deployable, extensible, flexible, reliable, and robust a software application can be, which is generally a function of the programming language. A Microsoft Access database can be all of those things, but not typically to the degree as, say, a J2EE-based server based application that can easily handle hundreds of users simultaneously and change your oil, too. Since life is all about perspective, that's often their frame of reference. And rightly so, that's what they get paid for. But that doesn't explain the hostility.

The more interesting reason, in my mind, for a strong distaste for Microsoft Access is that Access solutions can be perceived to be a threat to a full-time developer's position of importance and purpose. Now, no one will ever come out and say as much. But let's face it, in many cases you can churn out a quick VB-based application to solve a business pain in a matter of a few hours or days. Will it be a full fledged application in the same sense as a C++ or Java application that goes through a development/test cycle? Possibly not, but it can get the job done in the timeframe needed to meet a business problem. And that is a key source of irritation to those who believe Access applications are mere toys.

This is not to say that a C++/Java/RoR/whatchamacallit programmer can't also turn out a fast solution to a problem too, but an Access-based solution may be just what the doctor ordered at the time it was needed, for the number of users, scope of functionality, etc. And that can serve as a direct threat to a full-time programmer's role. The reaction? What appears to be an elitist snuff at Visual Basic and Microsoft Access. But I can guarantee you that I've also seen (and built) Microsoft Access applications that look and function far better than applications built in more powerful programming languages.

The last cause for scorn, and this is often fully warranted, is that Visual Basic and MS Access applications are often created as a learning experience. The result? Spaghetti code, bad error handling, you name it. And while I can't say I agree with what is often a mocking tone from a higher order programming language, I do agree that you have to draw limits to what sorts of Access applications you create if you aren't an expert. It's the classic "know enough to be dangerous" scenario where you may be able to create a Visual Basic application that runs your business, but if it's handling critical tasks and you're still a novice, it may be time to look for a professional. That professional may still create your application in Visual Basic, but that's really a matter of what your underlying needs and long term growth expecations are.

So for all you Access developers out there, novice or lifers, hold your heads high. For all you higher order programming language professionals out there, give them a break. You'll never be obsoleted by suddenly pervasive MS Access applications, and besides, it gives you something to do when the user/customer/business needs outgrow a desktop database application.

The Kind of Thing That Can Ruin a Day

If you're using Access 2007, be sure to read the Access Team's blog on an issue that can wipe out your database when you run the "Compact and Repair" utility.

Click here

Microsoft Access Video Tutorials: Tips and tricks, news, links, downloads on Microsoft Access

Helpful resource with many videos for Access 2007...

Alex & Access: Microsoft Access Video Tutorials: Tips and tricks, news, links, downloads on Microsoft Access

Overcoming Obstacles

A common frustration seen over the years is this: You create a great Access-based utility for personal reasons that might be valuable to others in your organization, but no one else has an Access license. Throwing down another $150 to $300 for an Access license, or Office Professional license, may not be an option.

The solution? Take advantage of the free Access 2007 Runtime edition offered by Microsoft. The Access 2007 runtime can be installed on any Windows machine, whether it has Office installed or not. A user can open an Access database, work in the forms, update data, view reports, and run queries. There are some limitations, of course, and you need to be sure your application is fairly rock solid in terms of menus and error handling.

We've put together a little whitepaper on some of the considerations and benefits to using the Access runtime edition here:

http://www.opengatesw.net/documents/Become A Cost Hero Datasheet.pdf

And here is the link to the download of the runtime version of Access 2007

Making use of Continous Forms

For those of you that may be new to Access, here is an important tip to create more effective database forms: take advantage of the "Continuous Forms" feature extensively. Especially if you or your users are coming from an Excel-based world.

Basic Use

In the most basic approach, you would simply create a form that has the form property View" to "Continuous Forms." This will give you the multi-row view you and your users are "Default accustomed to.


And to make sure you're using the most of your screen real estate, just put the most important fields in the Detail section of the form. If you have another set of fields that is less important, put in the "Footer" section of the form. In the example below, you'll notice there are only five columns in the main section.

The rest (in this case it's just a big notes field) can be put down at the bottom, and will change automatically based on the record you've selected. To display the Header/Footer, if it doesn't show up already, you'll need to click "View>>Header/Footer" in older versions of Access. In Access 2007, you'll need to look for an icon in the "Arrange" tab of the Ribbon that looks like this:

(yes, I know, not the most obvious looking icon, one of the many rough adjustments to the new ribbon UI)

Advanced Approach
The best way to approach interface design is often to aggregate data from multiple data sources in a single place for the user. This can best be accomplished by placing a subform in the main Form Footer section. And even better, making that subform a continous form. For example, you have Customers in your main form, and in the Form Footer subform, you'd like to see all the Orders for the selected Customer. Create your Orders form. Then in the Form Footer section of the main form, insert a subform (look for the icon on the design toolbox). Now when you select your new Orders form, you'll get an error message (at least in pre-2007 versions) telling you that Access will need to set the main form to a Single Form view instead of Continuous Forms. Ignore that error and proceed. Now that you have your subform in the Footer, feel free to change your main form back to a Continuous Form. Everything will work just fine, and now you can work more efficiently with your data all in single place. Here's a good example from our Assets template:

Note that you can see the asset, and all related maintenance records, in a single place.

Dashboards in Microsoft Access

For all the helpful posts on this blog, we trust you won't mind a few shameless adverts as well. We just released Dashboard Builder for Microsoft Access. If you've ever had to mess with numerous queries just to get a count of customers, a total of revenue, or other key business metrics, Dashboard Builder is for you!


This was a treat to develop. It can take data from any Access table, linked MySQL Server table, or Microsoft SQL Server table, and quickly sum, count, or average the resulting data set.

And since it's compatible with Access 2000-2007 runtime editions, you can give the boss visibility into KPIs without making them pay for the full version of Access.

A free 10-day evaluation copy is available for download on our site:

http://www.opengatesw.net/products/Dashboard%20Builder/DashboardBuilder.htm

10+ things you should do before building a custom Access database

A good post from TechRepublic blog 10+ Things about steps to take before you create a custom Access database:

http://blogs.techrepublic.com.com/10things/?p=317

http://blogs.techrepublic.com.com/10things/wp-trackback.php?p=317

Securing Your Access Database

This post is particularly for any of you that use Access in a multi-user environment.

Selecting the Right Security
When developing in Access to serve multiple users, it is important to consider what level of security you need before you get too far down the path. If you need comprehensive, air-tight security, Access is not the right way to go. Think of Access as a small safe where you keep valuables. You can lock it, potentially bolt it down to the floor, but a skilled thief will simply pry it from the floor, take it home, and figure out how to crack it open with enough time. So if you are highly concerned about data security, look to a more heavy duty platform like MySQL, SQL Server, or Oracle on a centralized database server. If you are satisfied that your employees are not likely to (a) attempt to compromise the data in your database, or (b) take the time to become savvy enough to overcome some basic security measures, Access is just fine.

Securing Your Access Database
Since there are many resources to help you with the necessary tasks of securing your database, I'll just hit the highlights before listing further reading.

A) Limit User Navigation

Be sure to give user's access only to the forms, queries, and reports that are essential to their duties. Hide the database window on startup. And if you choose not to complete (D) below, make sure to eliminate the option to open forms in Design View. Pre-2007 Access formats will allow you to establish workgroup security, which you can then use to limit users to specific objects in the database. If you plan to take advantage of the ACCDB or ACCDE formats in Access 2007, you'll need to think of clever ways to achieve the same result.

B) Lock Down Special Keys

Next eliminate the ability for someone to use Special Keys at startup (Startup>>Options on pre-2007 versions). This will ensure they cannot hit Ctrl-G, as an example, to enter the VB Editor and create some malicious SQL routine.

C) Password Protect

Password protect the database, and if needed, put it in a file server directory that is also protected based on the user's Windows login. This is a key step to prevent someone from accessing your database via an ODBC connection.

D) Convert to MDE

This final step can mean you need to be more careful about error handling, and also adds some steps to deploying updates. But converting to an MDE file means users will be unable to delete/add/change forms and reports, or expose your Visual Basic code.

Further Reading

There are quite a few resources out there on the details of securing an Access 2007 database. The two that really give you a full picture are:
How I Use Microsoft Access User-Level Security by Jack MacDonald
Real World Microsoft Access Database Protection and Security by Garry Robinson