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

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!