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

Record Level Auditing in Access

We just released our newest versions of UI Builder for Access. It was a huge effort over the past few months, but we're really excited about some of the new features. We've been playing with Access 2007 for some time now, and put in some nice features specific to 2007. But perhaps most important of all, our 3.0 release includes a great new wizard to help people set up record-level audit history tracking in their forms without having to create any VBA code. Now on to the next release...the work never ends!

Not Ready for Showtime...Yet

I posted a few weeks ago on Trackvia, which promised to be a more usable, online database. I played around with it for several days, and this reviewer's take is "not ready for showtime yet." While Trackvia has some very nice features, it is effectively a list-management tool akin to Excel. Without the ability to store information in a normalized fashion, it's difficult to buy their claim that it is a database.

Databases enable you to store information in efficient ways such that you minimize the amount of replication (within the database) for any given data element. Trackvia, similar to Excel, makes you repeat the same information with each record you might want to store. For example, if you have a contact database, you must repeat the same information (e.g., customer name, address) for each related contact.

The leap from where Trackvia is to where they need to go isn't that far, but I'd hold off any serious purchase decisions until they mature. And by then....Google or Microsoft can help them enhance the software after the acquisition.

Macros or Visual Basic in Access?

If you are starting out with a database project and just learning how to create Microsoft Access solutions, one key question is: Should I use macros or Visual Basic (VBA)? We'll just go over some pro's and con's, you make the right choice for your situation.

Using Macros
Pros

• Easy to learn
• Support for conditional statements (if this is true, then perform this action, otherwise do this other thing)
• Easy for users to launch directly from the database window

Cons

• Unable to support advanced functions (writing to files, reading files, manipulating form objects, complex conditional statements, recordset manipulation)
• Cumbersome to migrate to VB if you choose to eventually using VB instead of macros (converting to VB is initially a pain, and so is maintaining both macros and VB simultaneously in many cases)
  

Using Visual Basic
Pros

• Ability to offer many powerful features in your forms and database (including access to Windows APIs for things like retrieving the NT user name, manipulating window appearance, position, etc)
  
• Ability to create extensive error handling to reduce user frustration and handle exceptions (e.g, when a user can't access a given network file share, taking an alternate course action instead of just stopping your processing)
• Manipulate strings and other variables (e.g., change a name like "Johnson, William" to "William Johnson")
• Easier to debug by inserting breakpoints, seeing what the current value of a variable is, and adding complex conditional statements (if...then)
  

Cons

• More upfront time required to learn how to use VB
• If you don't get formal training, more upfront frustration until you master the language

Certainly there are additional items that could be added to each of the categories above (feel free to comment!), but this condensed list is all I could muster, and should give you a decent foundation for making the right decision.