Access Consulting -

After many years focusing solely on building Access tools, we've decided to extend our offering to Access consulting.  We are taking a limited number of engagements, but please check out the list of services:
OpenGate Access Consulting

Whether you have a an aging database that needs renovation, or a new project of any size, give us a call or drop us an email to see if we can help!

Transitioning your Database

Some time ago I posted about the risk you face in creating a database that becomes essential for your group's daily operations.  By no means should you think of NOT creating or pursuing the database.  It is a double-edged sword: creating an innovative and useful database will increase your value and the success of your group, while it can also add to your workload and cause your management a bit of fear when they think of promoting you or your departure.

Step 1: Set Expectations
Set clear expectations with your management about what your Access database can do functionally, and what it can do with and without you.  It may continue to work perfectly even if you are gone for weeks.  Or it might need daily care and feeding.  Either way, make sure you've communicated what it takes for your database to keep working, and what skill level is needed if you are gone.  Ideally your manager will be an ally, and make certain to facilitate finding a prospective replacement.  It also helps them to understand how much hard work you've put in to saving your group time and money.

Step 2: Find a Replacement
Look for someone in your group to begin sharing the work with.  If your application has been visible to others in your group, you may have no trouble finding someone willing to start taking some of the "hero" spotlight. And you don't need to tell them you're hoping for a promotion, just that you'd like someone to help cover questions and issues if you're out of town, on vacation, or sick.

It's likely that no-one will have your level of expertise in Access.  If so, you can try to convince your manager to fund an online Access class, or a book or two to help cross-train your colleague.

Step 3: The Transition
I've been fortunate to move to new positions several times in my career.  At least three included a departmental Access database that needed someone to take care of when I moved on.  Try setting up a series of short meetings to transition your responsibility.  Breaking your transition into several sessions gives some breathing time for you to remember details you might want to pass on, and the new person can assemble questions and absorb the information for the next session.    You may be tempted to apologize for poor code quality, imperfect architecture, and ineffecient steps.  Rest assured the person you are transitioning to will likely not recognize these facts, and will have an opportunity to improve upon them in due course (if they have the interest!).

Simplify development, testing, and troubleshooting multiple versions of Access with Windows 7 Virtual PC

http://blogs.msdn.com/b/access/archive/2010/06/04/power-tip-simplify-development-testing-and-troubleshooting-multiple-versions-of-access-with-windows-7-virtual-pc.aspx

Time for Spring Cleaning? Revisiting Your Access Database

It's fair to say few people I know involved with MS Access ever said to themselves, "I want to learn to be a database guru," or, "I want to specialize in Microsoft Access."  If you are like most MS Access users, it fell upon you as you searched your Start Menu for what your company had installed on your PC.  Or you bought Access thinking it might be useful some day.  At the same time, your very first database may also be the most important one you use, the reason you started using Access, and the little application that makes your day go faster.

Unlike a Word document or a web page, the thought of doing a major rewrite of your database to run more smoothly can make you cringe.  Your first few Access databases probably  remind you of when you were learning volumes of new concepts while trying to put them into practice that same day.  They are unwieldy and difficult to maintain.  Fortunately, you are wiser now.   You know that a single macro or line of VB can help you avoid creating 10 different copies of the same query just so they show information for 10 different products.  You know that you can accomplish the same task with a few simple modular VB functions instead of the gnarly 200-line monster that your database relies on. Or you know now that you don't need three separate tables (Prospects, Proposals, and Customers) that all store essentially the same information, when a simple "Account Type" field can help you differentiate between them.

Whatever ugly solutions lurk in your database, as Spring rolls around and the economy starts to tick back up, it may be time to start eliminating the vestiges of your create-as-you-learn-the-basics work.

The Benefits
Before you set out to rewrite or rework part of your database, it's sensible to do a back of the napkin cost/benefit analysis.  What will revising your Access database do for you?

1. Less costly maintenance.  Rewriting or reworking some of the most difficult-to-maintain areas of your database can save you time when you need to fix a bug or make a change needed to keep your business running.  Instead of changing your code in 5 different places and making sure it all works, revising your database can mean you only have to make the change in one location, and test it once.  
2. Fewer bugs.  If you simplify your Access database, and that is usually what you can do armed with greater knowledge of Access database design and development, you can reduce the number of failure points.  And because your database accomplishes the same tasks with fewer steps, queries, macros, and/or lines of code, you can spend more time testing and adding error checks into those steps.
3. Smaller database size.  If you have your database on a shared network drive, then the size of your database will matter when users open it each time.  Interestingly, deleting several redundant forms or optimizing your tables and indexes can make a big difference in the amount of space your database takes up.  That in turn can mean faster loading times.
   

Spring Cleaning Tips
So what can you do to clean up your database?

• Revisit your indexes.  If speed and size are important, make sure you have Access table indexes on the right fields.  Access will often automatically add indexes to some numeric fields (especially if you created that table by importing from Excel), even if you don't often run queries against those fields.  Conversely, you may find that Last Name or Email Address is very commonly used to search your database.  You may want to add an index to that field to make your queries run faster.
• Use the Access "WhereCondition" argument for DoCmd.OpenReport and DoCmd.OpenForm (for macros, it is just Open Form and Open Report).  In so many cases, I see people copying the same form, Access query, or report so that Report 1 is for Jane's sales numbers, and Report 2 is for Fernando's.  The fields are all the same, everything is the same except for the sales rep.  And each time you need to make a change to the report, you have to do it in 2 or more.  Instead, use the WhereCondition argument to control whether the report should display Jane or Fernando's data.
• Compact, Repair, and Decompile.  Compact and Repair are easily done.  In Access 2000 and 2003, click on "Tools>>Database Tools..>>Compact and Repair."  In Access 2007, select the Office icon, then Manage>>Compact and Repair.  Decompling your Access database is something you should do if you find your application is behaving oddly, such as ignoring when you explicity set a variable, or crashing when you edit one of your Access forms.  One of the Access MVPs has a fine page on the subject.
• Create lookup tables to improve consistency.  If you have a field that should contain a finite number of values, but you find your users enter all sorts of junk, it may be time to create a lookup field and table.  First, create the lookup table with the values you want to limit users to select.  Second, you'll need to create a new lookup field in your main table.  You can set the data type to "Lookup..." and Access will guide you through the process of pointing to your new lookup table.  Last, you have to update existing records to set the new lookup field equal to the corresponding lookup key in the lookup table.  To do that, create a new query that joins the lookup value (not the key, the value) in the lookup table to the "old" value field in your main table.  Then, make the query an update query and say you want to set the main table's new lookup field equal to the lookup table's key field value.
• Simplify your code and macros.  Whether you rely solely on macros, or you use VB heavily, simplification is always the goal.  Try to break out our macros or VB into small pieces of single-purpose elements.  If you have five macros that all update customer records, you may be able to create a single "master" macro that performs the update, and then all five simple point that that master macro to do the common work.  Similarly, if you have the same VB code on each form that validates your required fields are populated, but that code is identical, become familiar with the "Me" form object reference and create a single VB function that sits in it's own module, and each form's code simply calls that main function and passes the "Me" object reference to say which form needs to be used when running the code.

Have more Spring cleaning tips?  Would love to hear about them in the comments!

Getting Ready for Microsoft Access 2010

Several pages with overview information about Microsoft Access 2010, including an overview of the new Access Services and Access Services System Requirements

How to Be a Self Taught Access Database Programmer

So you don't plan on making programming a career, but for some reason (need, fun) you want to make your database really sizzle. Maybe your database is your pet project, a guilty pleasure during downtime. Or you hope it will increase your value to your company (and often it can). But the most you've ever done that closely relates to programming is hitting the "Record" button in Excel to record a macro. Let's talk about ways you can learn to do some basic Access programming without taking a hammer to your PC after a few hours.

1) Very Small Bites

Programming can be as challenging as learning a foreign language, and then some. Set some very, very simple goals. Like, "I'm going to make a message box popup that says 'Hello World'." There is good reason why most books you pick up about programming make this the first step. The proverbial "Hello World" is a small, achievable milestone that you can check off as a success. You'll want to have lots of these to keep you going, to offset the frustration when you just can't understand why something doesn't work.

2) Learning by Example

Microsoft Access has been around for over 15 years now. The wealth of examples, samples, and Access templates available on the internet is astonishing. Familiarize yourself with what is out there, and then pick apart a template or example you like. Find out how it ticks, and adopt the useful material for yourself. Unlike writing a college paper, code samples publicly available on the Internet are generally there to be copied. Respect any copyright notices that you may need to include in your code, but otherwise, take it easy on yourself. Don't recreate the wheel when there are so many samples and tips available. Click for a collection of Access tips and resources.

3) Engage the Experts

After you've spent time learning the basics, and you've hit a wall, you'll find an amazing group of people willing to help. The UtterAccess.com message boards are full of experts always willing to help out. A few tips to have a good experience:

a) Always do a search on Google, and search the posts, for any solutions to your problem. The experts are patient, but I've noticed they get (understandably) touchy when a well-known solution to a common problem is brought up by a new user. They prefer to help those that have tried to help themselves (as opposed to posting questions without doing any work to find a solution first).

b) Explain the problem thoroughly. Something like "I'm getting an error in my code when the user clicks a button" is about as helpful as when someone in elementary math would raise their hand to say "I ended up with 9.5 on this problem." The teacher hasn't seen the work, and so has no idea where the problem may have occurred. Similarly with programming, give as much information as possible. For example: "I'm encountering error 999 'Object Does Not Exist' when I tried to open a recordset."

4) Celebrate Successes

An extension of #1 above, make sure you allow yourself little "wins." If you've got a complex set of steps you're trying to accomplish, break them into little pieces that you can check off. If you get very frustrated, sleep on it.

5) It doesn't have to be pretty

If you are learning to program, your first many attempts won't be pretty. And unless you plan to sell your solution, or you are really looking forward to a career in software programming, don't worry about how pretty the code looks. It needs to work, of course. But it needs to work for your purpose. You'll find a lot of purists that may chuckle if you don't use (or know about) class modules instead of normal modules. You may know that your monolithic, 500 line subroutine isn't the optimal way to handle something, but what matters is that it works reliably. If you have time later, you can rework it to remove redundant code, move some parts to separate functions, etc.

6) Enjoy!

Learning to program is like anything else that comes with satisfaction, it is earned through a good deal of challenge and frustration. But in the end, you have tangible (your application) and intangible (the new knowledge you possess) accomplishments you can admire.