MS Access for Professionals

Myths about Visualization

2011-10-04T15:22:00.001-07:00

Since much of people's work in Access and Excel ultimately leads to trying to visualize your data in some way to understand your business or research better, this post is really insightful:

http://flowingdata.com/2011/09/23/5-misconceptions-about-visualization/

Google Docs - The start of a database offering?

2011-10-01T09:31:00.000-07:00

Who knows these days what Google is serious about, versus a passing fancy. But Google Fusion Tables may just be a new competitor to Zoho and Access 2010 SharePoint Services if they keep adding features to it.

Excel Resources

2011-09-19T13:14:00.000-07:00

Of course this is a blog mostly about Excel, but frankly, Excel and Access are like close cousins that have their own special strengths. Here's a resource that can help with how to choose between using Excel or Word tables, and what is Excel used for.

Making Excel Easier

2011-07-12T12:46:00.000-07:00

After years of working with Excel, you start to just "live" with some of it's shortcomings related to usability. How often have you had to toggle between sheets to see what the value of an important cell is after making a change? How often have you wished you could go straight to a chart without wading through your sheet tabs to find where it is?

Suffer no more! Give this Microsoft Excel tool a test drive and you'll be hooked....

Publishing Access Dashboards to the Web

2011-05-27T11:54:00.000-07:00

Very exciting news! Have you always felt like your Access data was trapped on the desktop? We've just completed and released our newest addition to the Dashboard Builder family - Web Edition. The Web Edition lets you create rich, interactive HTML charts and graphs on the Internet from your Access database!

Imagine seeing your Access data anywhere, any time, on any web-enabled device! Without installing any special apps on the device.

You can download an evaluation from our site here:
MS Access Dashboards on the Web

Access Query Tips

2011-04-23T20:03:00.000-07:00

New article available on Access Query design and use to summarize and analyze your Access data.

What are those gray "MSys..." tables!?!?

2011-04-06T08:10:00.000-07:00

Over the years I've talked to users that panic because they see grayed out tables starting with "MSys" in their database. Virus? Trojan? Spyware? An add-in that hasn't uninstalled?

Fear not, it's a normal part of Microsoft Access. At the very least you can hide them. And they can even make your work easier in some cases. Learn more about Microsoft Access System Tables here.

How to Package and Deliver Your Access Database

2011-02-24T16:07:00.000-08:00

Many people using Access need to deploy their databases in such a way that they can upgrade their forms/reports/queries/code/macros without disrupting users or losing data. This article can help: Delivering Your Access Database - Best Practices

Access Forms - 10 Ways to Improve Your Forms

2011-01-31T23:45:00.001-08:00

New article available on 10 ways to create more dynamic user experiences with Access Forms

Moving from Excel to Access

2011-01-12T12:44:00.001-08:00

New resource available for anyone that is new to Access and looking for a high-level process to move from Excel to Access.

Microsoft Access reports just got a whole lot easier

2010-11-30T23:15:00.000-08:00

We've just launched Report Builder for Microsoft Access, a way to create an Access report in far less time, and make it look much more polished than doing it all by hand! Thanks to all the dedicated beta testers who gave such excellent feedback!

Here's the direct link:
http://www.opengatesw.net/products/Access-Report-Builder/Report-Builder-for-Access.htm

New Access 2010 Resources from Microsoft

2010-11-23T11:05:00.001-08:00

http://technet.microsoft.com/en-us/office/gg406142.aspx

Mainstream Access 2010 Hosting

2010-10-19T22:49:00.000-07:00

Microsoft is making Access 2010 web hosting more mainstream:
http://blogs.msdn.com/b/access/archive/2010/10/19/office-365-and-access-web-databases.aspx

While another Access 2010 hosting solution exists already, clearly people look to Microsoft first to find the end-to-end solution. Whether the pricing will be comparable, that's going to be interesting.

Transitioning your Database

2010-09-27T22:21:00.000-07:00

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!).

Possible Game Changer from Microsoft

2010-08-19T07:01:00.000-07:00

Creating an interface where normal business users can build an application without learning to program is the golden goose. Microsoft seemed to think Access was that product many years ago, here's their next evolution of it (I think). Beta is available August 23rd. Can't wait to take a test drive!

http://www.microsoft.com/visualstudio/en-us/lightswitch

Uploading Access to SQL Azure

2010-08-12T12:23:00.000-07:00

Microsoft has just released an update to the SQL Server Migration Assistant (SSMA) that allows you to load your Access database into the Azure cloud. Haven't tried it yet, but please post a reply if you have. Would love to see what your experience has been. And specifically, how have you front-ended the database? With ASP.NET, or just continue to use the Access client with linked tables.

http://blogs.msdn.com/b/access/archive/2010/08/12/easily-migrate-access-data-to-sql-azure.aspx

Office 2010 - Time to Upgrade

2010-06-23T18:33:00.000-07:00

We've got quite a few Access customers still on 2003. Yesterday one asked if they should upgrade to 2007. The answer, of course, was "no." Naturally the conversation was directed to whether to upgrade to 2010 instead. You can find highlights for Access 2010 here. In the end, it's probably time for most users to upgrade to Office 2010 if you have 2003. The fact is 2003 was a legacy of Windows 95-era desktop apps, while 20007 and 2010 are more representative of Windows Vista/7 user experience design patterns and security. Not only will you find it easier (after some adjustment of course) to work in Office 2010, but security is also a key element to consider.

Access 2003 introduced a minimal security prompt, whereas the Office 2007/2010 Trust Center treats security in Office with the consideration it deserves. Office 2010 includes file-level trust, something definitely missing in 2007.

Lastly, if you are considering upgrading to Office 2010, you can reconsider whether all users require the full version of Access, or whether you can save money with the free Access runtime edition.

Weekly Status Reports Online

2010-06-19T09:22:00.000-07:00

A new online service from OpenGate Software! The free public beta period has started, sign up today for up to 10 users in your organization. Collect weekly status reports from your employees online, and submit to your manager in minutes. No more need to worry about status report templates, formatting, and reminders! Simple Status takes care of it all for you. Give it a try for free!

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

2010-06-04T12:44:00.000-07:00

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

The Client App Lives!

2010-04-08T16:11:00.000-07:00

These days everything new and interesting seems to be web-enabled. From email to ERP, everyone is going to the web. So it's interesting to note that a company like Google, heavily centered in the online world, offers a client-based application to manage pay-per-click (PPC) advertising campaigns. While rich internet applications continue to evolve and replace traditional desktop apps, it's good to know even Google recognizes the value (and necessity) of client-side apps.

For that very reason, Access 2010 will hold value where low- to mid-level complexity tasks are accomplished in a web client, and high-complexity (or processor intensive) activities are handled by power-user client applications.

Comparing Access 2000 through Access 2010

2010-03-25T08:10:00.000-07:00

New resource available:
Access 2000 to Access 2010 comparison

Time for Spring Cleaning? Revisiting Your Access Database

2010-03-02T19:53:00.000-08:00

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?

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.
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.
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

2010-01-05T12:01:00.000-08:00

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

2009-12-02T09:16:00.001-08:00

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.

Microsoft Access 2010: Publish Access Data/Forms to the Web

2009-10-19T23:33:00.000-07:00

Microsoft announces a major new feature in MS Access 2010: the ability to publish Access data and specialized forms to SharePoint. See the video here:

http://ecn.channel9.msdn.com/o9/ch9/3/0/1/0/0/5/TheAccessShow2010Demo_ch9.wmv

Microsoft calls it "Access Services." We've been working with the feature since November 2008 when we visited Redmond for a few days for an alpha-preview. It is a vast improvement over Access 2000/2003 Data Access Pages in terms of usability. The new feature is limited to SharePoint, something that will be a downside for anyone hoping to finally publish Access forms to the web. We've also been participating in the SharePoint Live tech preview, and publishing to SharePoint Live is not supported. But for any local intranet application that requires the scalability of SharePoint, and some nice web forms, Access 2010 is a fantastic leap forward.

There will be an Office Beta 2 in November, which will be publicly available. A great opportunity to try it out. Note that you'll need SharePoint 2010 (now called Office Server). And a word of warning: don't publish any production data in the beta and expect it to migrate it to the full version. We found quite a few issues with publishing data and databases that Microsoft will surely fix when Access 2010 goes public.

Estimating the Complexity of Your MS Access Database

2009-09-30T23:40:00.000-07:00

We've just published a new white paper to help Access beginners to help estimate the complexity of a new Microsoft Access database project. The paper offers a scoring system to help decide how complex your database project may be, and the level of experience that is likely required.

Download the paper here, along with other Microsoft Access tips.

Upgrading Windows XP to Windows 7

2009-09-18T21:19:00.000-07:00

A little bit off the typical topic, but since we've gone through it several times, thought this might be of service to some. If you don't want to replace your XP machine because it's fairly new and has the horsepower to run Windows 7, here's how to "upgrade" it without reformatting your hard drive:

Upgrading Windows XP to Windows 7

Five Things to Love about Access 2007

2009-08-24T21:53:00.000-07:00

With Microsoft Access 2007 hovering at around $89 for a copy of the upgrade version, the price is now one of the smallest considerations when deciding to upgrade. Access 2010 is in the next calendar year, and so you may want to wait to upgrade if you haven't already. But working in Access 2007 quite a bit lately to create a new Microsoft Access Template for employee recruiting, I didn't want some of the less publicized, but incredibly useful features, to go unnoticed. In no particular order:

Alternating section backgrounds in forms and reports. Getting that "greenbar" effect where every other row in a form or report had a different background was once the thing of clever coding or Conditional Formatting. Access 2007 has a native "Alternate Back Color" property in the Detail Section properties. You can set it to be dynamic to the user's system color scheme, or specify a color to use.
Date picker. Similar to the greenbar effect, you once had to trouble with ActiveX controls to offer users a nice date pick control when they were in a date field. Updating to the current century, the Access team wisely made it an embedded control that automatically pops up when the user enters a date field.
Layout view. This enhancement has gotten more press than the prior two, but it can't get enough. I'll admit I staunchly stuck with the "Design view" when Access 2007 came out, shunning the "Layout view" as a silly tool. How wrong I was. The Layout View in Access 2007 will make you so much more efficient creating reports and forms. Neither mode is perfect, but use the Layout view to touch up your reports and forms by seeing what records will actually look like to the user. You'll notice text boxes that need to be wider, mis-aligned controls, and whether you're using space and screen real-estate effectively for your users.
Report design. Overall, Microsoft's improvements to the Report Design mode are fantastic and far more intuitive. It's fair to say that 50-70% of the people that use Access would consider themselves novices, and the old report design interface did few favors to help. The Group & Sort, Totals, and Layout view simplify some of the most confusing elements of report design in prior versions of Access.
Ribbon. I'll caveat this by saying anyone who has worked with any MS Office product for years is going to hand you a few reams of complaints about the Office Ribbon, and the Access Ribbon in particular. The Ribbon in Access is problematic for designing an application for end users, as it takes away quite a bit of programmatic control developers were accustomed to. But for a beginner, the Ribbon is genius. If you are not a database designer or developer, you need to know what possible tasks and options exist for what you are looking at on screen. And the Ribbon does just that.

A few myths and truths about MS Access Security

2009-08-20T20:12:00.000-07:00

It seems at least once a month, I see some trade magazine or blogger snipe that Microsoft Access is the least secure place to store your data, or that Access is a hacker's dream. An article in InformationWeek this month offers some empirical data to combat the myth that MS Access databases are a wide open vault door:

(source: InformationWeek "Database Servers: Candy For Hackers")

There is no denying that the "Workstation" and "Laptop" categories would largely pertain to Microsoft Access and FileMaker. Nevertheless, database servers clearly hold the largest share of breaches and records stolen.

Truth: Microsoft Access database files can be hacked
Microsoft designed Access to be secure when the appropriate measures are taken. But even taking those measures, there are publicly available utilities to unlock MS Access passwords, and read the raw database files.

Myth: Access databases are easy to hack
This myth is only a myth if you take the right steps to secure your database in the first place, and I won't go into them here. You'll find a wealth of them available online, including this excellent article on MSDN. But if you take the appropriate measures, you'll be safe from everyone except the interesting characters that sit in front of dark terminals search for vulnerabilities and go by monikers like 'fuN1B0yyy'.

Final Thoughts
If you choose to store confidential and sensitive information in an Access database, take the appropriate measures to limit access to the database. That means securing the database file(s), but also locking down the directory, and machine, where the sensitive information exists. But above all, when you hear a well-meaning colleague scoff "you're storing that in Access...you might as well just print it out and pass out copies on the street!!!" you can respond with empirical facts (database servers are more commonly breached) and the steps you've taken to appropriately secure your sensitive data.

Create Microsoft Access Forms

2009-08-14T08:59:00.000-07:00

We've just released our newest version of Designer for Microsoft Access. Version 3.0 features automatic MS Access forms creation.

After the success of our first product, UI Builder, we found that most Access users never intend to become database architects, and would prefer to just get on with using their database. Thus, we created Designer to make it far easier to build Access database tables and relationships using a plain-English Q&A style wizard. Over and over, Designer customers would tell us, "you're product is great, but how do I create data entry forms that will show all the information in one place." So we went back to work on Designer to solve that problem, too.

The new Designer creates tables and relationships automatically, and then creates forms with a "header" section that displays many records as a list, and then a bottom section that contains related records from other tables. For example, if you have a "Patients" form that displays a list of all patients in your database, the bottom section of the form Designer creates can display related appointments, emergency contacts, prescriptions, visits, and notes.

What Microsoft Access problems are you trying to solve? We love a challenge, especially if many Access users will benefit!

Access Triggers are Here!

2009-08-13T23:42:00.000-07:00

Anyone following the Microsoft Access 2010 teasers or participating in the Tech Preview knows by know that Access 2010 will have a new feature called Data Macros. They are very similar to their more robust cousin-database platforms' Table Triggers concept. Read more here:

Access Team Blog: Access 2010 Data Macros

Microsoft Access 2010 Preview: Ribbon Customization

2009-08-04T23:33:00.000-07:00

One of the key criticisms of Access 2007 was the new ribbon. Gone was the familiar command bar, and with it, many thousands of hours work by the Access developers that came to rely on the command bar as the main user menu.

Microsoft has invested in making life a bit easier for the everyday user that wants to customize the ribbon without custom code or the nice (free no less) utility Claton Hendricks created.

As you can see in the screenshot from the Office 2010 Tech Preview, you can create custom ribbon tabs and groups. In the preview, it doesn't appear that you'll be able to change any of the packaged ribbon tabs and groups, though.

Stay tuned for more on Access 2010 Web Form design and other enhancements...

What Access 2010 Web Deployment Will Mean

2009-07-22T23:38:00.000-07:00

The world's most popular desktop database is going to the web. That's what is fairly clear now from the Access Team Blog and several other public sources. The actual deployment details are still a fairly close held secret (including yours truly), but what is certain:

1) Microsoft is renewing their commitment to MS Access. You could argue that Access 2007 sets the stage for Access 2010, but most agree Access 2007 wasn't a huge leap forward.

2) MS Access is back in the competitive game. Zoho, OpenOffice, TrackVia, Alpha5 and others have been nibbling at Access' proverbial toes for a some time now. Not with any significant success...yet.

3) Access development will change significantly, for the better. Access 2010 will most assuredly not be a repeat of the Data Access Pages debacle. Even taking web functionality out of the picture, the ability to set data macros like SQL Server triggers will be a huge step forward for any Access solutions that need higher levels of data integrity and validation.

There are more interesting points to make, but Microsoft isn't releasing the details yet, and we don't want to risk violating non-disclosure!

New Microsoft Access 2010 Video

2009-07-20T08:26:00.000-07:00

Found an online video with more previews of MS Access 2010 on YouTube.

Note the new "Blank Web Database" in the new database "Available Templates" dialog, and the "Client Forms" in the Create Ribbon Tab. Microsoft Access on the Web?

Quick Microsoft Access Overview

2009-07-09T23:49:00.000-07:00

What Is MS Access Used For overview for any of you new to Access databases

Is Microsoft Access Still Relevant?

2009-06-28T21:24:00.000-07:00

With a new wave of online "databases" (which are arguably not fully functional databases, but spreadsheets with fantastic Ajax entry screens), I wondered today whether Access remains as relevant as other office productivity tools.

You might argue that Access has always been an under-utilized asset in most companies, with Excel spreadsheets being stretched to the limits to become Access-like with extensive vlookup and hlookup references. Imagine my surprise to learn that Access actually receives more searches on Google generally (click here for the trend graph source)

Note that the blue line is Access, red is Excel, and orange is (not surprisingly) Word.

Access is probably more relevant than ever, if not a bit outdated in it's delivery method (desktop only). The personal micro-databases that are quick to create, inexpensive, and don't require a computer science degree is vital to many businesses in a difficult economy where hiring consultants isn't an option, and efficiency can help you stay in business. From what I've seen so far, Access 2010 will go a long way in making Access even more relevant as a micro-database for the masses.

z69cuhx7f4

Creating Dependent Dropdowns

2009-05-26T23:03:00.001-07:00

Here is a great tip and example from the MS Access team on how to create dropdown boxes that drive what a user can select from additional drop downs. It is also sometimes referred to as cascading combo-boxes (since the first choice 'cascades' to the next list of things a user can choose).

Get the template here

Free Access Sample - Web 2.0 Effects in Access

2009-03-13T22:49:00.000-07:00

Add cool looking Web 2.0 visual effects into your Microsoft Access database with a few simple steps. You don't have to know AJAX or Java to impress your users with advanced visual effects. Follow the link below for the free download:

OpenGate Library Sample - MS Access Web 2.0 Effects

The first shows you how to dynamically expand and contract a subform to save screen real estate and avoid overwhelming users with information they might not need.

The second example uses a single line of Visual Basic to fade one or more controls in and out with the click of a button or label.

You Learn Something New Every Day...

2009-01-17T23:00:00.000-08:00

Need to convert your Macros to Visual Basic? I never imagined Microsoft had a tool like this, but come to find out, it's been in Access since 1997.

http://www22.brinkster.com/accessory/macros/004.shtml

Database Design and Abstract Thought

2008-12-29T23:40:00.000-08:00

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:

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.
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).
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"

2008-11-13T09:07:00.000-08:00

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

2008-09-15T21:58:00.000-07:00

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...

2008-06-30T21:59:00.000-07:00

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

2008-06-02T22:26:00.000-07:00

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

2008-05-14T21:43:00.000-07:00

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

2008-05-06T23:02:00.000-07:00

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

2008-04-04T00:19:00.000-07:00

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

2008-03-03T23:50:00.001-08:00

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

2008-03-03T23:40:00.000-08:00

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

2008-01-09T23:26:00.000-08:00

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

2007-12-09T23:56:00.000-08:00

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

2007-11-20T22:25:00.000-08:00

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?

2007-10-22T23:26:00.000-07:00

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.

A new twist on databases?

2007-10-03T23:31:00.000-07:00

I've been waiting for this to happen, I just expected it to come from Google rather than a startup. But here it is, hosted databases similar to what we're seeing come out of Google and Microsoft for their respective documents/spreadsheet apps online.

I've just started playing around with it, I'll post more as I come to conclusions.

www.trackvia.com

First reactions:

-great concept

-offline access/synchronization is a big need

-security will be an interesting issue...databases generally hold more important information than just term papers

Creating Application Frameworks for Multiple Projects

2007-09-23T22:34:00.000-07:00

Once you create one Access database project, you're likely to find another reason to build one. And with each one you create, you'll likely evolve your techniques and refine each new database. A few tips that have helped me in the past, that you don't often find in the MS Access books or help sites:

1) Create a reusable code library. Create an empty database that will serve as your code library. Create a VBA reference to that database in your new project database (open the VBA Editor, click on Tools...References, then locate your file using the Browse method). Then, for each generic function you create that might be used in more than one project, put it in your code library database. Why is this helpful? For one thing, as you improve the VBA functions in your code library, it becomes easier for each of the separate database projects you've created to use that improved code without updating each database. This is especially true if you deploy front-end databases to multiple users. You can place the code library database on a central file server, then reduce the amount of maintenance you need to do on each separate front-end copy.

2) Create a reusable application framework. That is, as you begin to build each latest and greatest database, you'll likely find they have commonalities you can begin to standardize. Whether it's menu design or a popup reminder form, you'll have items that you use over and over again. Those are the ones you'll want to put into a framework template (for lack of a better name). Then when you go to create your newest project, you've got n% of the project ready to go.

3) If you are creating a database for multiple users where each user has a separate front-end, consider placing important configuration data in the back-end database. Some configuration information you might store in a local table (user-level preferences as an example), whereas you will want to put configuration data in a remote linked table if you may need to change it remotely or want the configuration to be the same for all users. For example, the output location of reports, or a parameter that determines whether you want logging turned on or off on the front-end databases.

It was only after creating about 20 different database projects that I came to use this approach, and it saved a lot of time maintaining front-end database copies and starting a new database project. The lessons really served as the foundation for UI Builder, which is intended to give Access developers a ready-made framework to create their own projects so they can focus on important things like creating user-friendly forms and sound database architectures. But whether you use something like UI Builder or create one for yourself, creating a reusable framework is a must if you're going to develop lots of Access databases.

We'd love to promote Janet...but who's going to keep up that database?

2007-09-05T21:45:00.000-07:00

Being an innovative member in your org can be a double-edged sword. Watch out for the edge you might not anticipate. Namely, where being indispensable because of that neat time-saver of an application you created turns into the reason people hesitate to promote you. You probably have a plus sign in the column labeled "Helps Drive Efficiency" or "Increases Revenue,", but unless you have someone capable of carrying on your work, you might get a question mark in the "Able to Backfill" column. If you're an IT worker, probably not as much of an issue. But if the standard expectations of your core role don't include any sort of MS Access knowledge, your employer may be in a tough spot.

Here's why. Your standard job role (that is, what your peers are expected to do) doesn't include a requirement for MS Access expertise, VBA knowledge, etc. So the same reason they are thinking of promoting you (you have capabilities beyond your current position) is also the challenge in finding your replacement. Namely, do they have to modify the job posting to indicate the person needs to know MS Access? And if so, do they pay them more or give them a title like "Business Analyst"?

Another potential challenge, albeit a better problem to have, will be that you do get that promotion, but still have to maintain your application. It might sound like a good compromise at first, but as soon as your new job takes hold of you, those little enhancement requests and bug fixes start to lose their luster.

So what can you do?
1) Remember that what you create needs to live on without you. The less "administrator" intervention required for it to continue operating when you've moved on, the better. That means solid error handling and messages, automation, and following best practices (naming conventions, VBA conventions, commenting your code, etc) so someone else can pick up your work and have a chance of being able to fix bugs without calling you.

2) Start cross-training. Find one or two people in your org that are sharp enough to start picking up what you've learned. That might mean giving them a crash course in VBA, table design, etc. While it might seem like it will make you less indispensable, it probably won't. It will secure your future, and let you take long vacations without worry.

3) Consider replacement. Is your solution available off-the-shelf for a reasonable price? Do you have a professional development org that can take up what you've done and replace it with a more robust solution? If so, think about building a business case to show the benefit of replacing your solution. Why? It shows you have business savvy beyond just increasing efficiency with an MS Access database, gives you an opportunity to dive into financial business cases, and may save you future headaches if you're stuck maintaining a database you created seven years ago and four promotions back (I've been there, it gets uglier and uglier to explain to a VP why you're doing VBA instead of your real job).

As always, happy developing!

Excel, Access, SQL, oh my!

2007-08-27T23:18:00.000-07:00

As a professional who's full time job isn't creating software, you're likely faced with the question of whether to use Excel, Access, or something else for that project you're beginning or mulling over in your head. There is no simple answer, but the table below is intended to help compare and contrast your options.

Use	Spreadsheet (Excel)	Desktop Database (Access)	Server Database (SQL Server, MySQL) with Access/VB user interface
Validate basic user input (e.g., for a single field/cell	Yes	Yes	Yes (probably overkill by itself)
Complex user input validation or events (if field X = Y, and field Z is empty, perform some action)	Challenging to accomplish	Yes	Yes
Data arranged in a few columns without much repetition	Yes	Yes	Yes (probably overkill by itself)
More than 65,535 rows/records	No	Yes	Yes
Simple cross-references	Yes	Yes	Yes
Complex data analysis/queries	Limited	Yes	Yes
Events based on user actions (when user exits a field/cell do X, when they mouse over a field/cell do Y)	No	Yes	Yes
Multiple users, but generally accessing data at different times	Yes (but conflicts can occur)	Yes	Yes
Multiple users, accessing data at the same time	No	Yes (generally slow)	Yes
Frequent mass-update operations	Challenging to accomplish	Yes (can be slow if resource intensive)	Yes
Multiple user forms	No	Yes	Yes
Mail Merge with Word	Yes	Yes	Yes
Reporting	Challenging to accomplish	Yes	Yes
Automated backups	Challenging to accomplish	Challenging to accomplish	Yes
Record-level auditing	No	Challenging to accomplish	Yes
Ability to hide the inner workings of your project from the user	Challenging to accomplish	Yes (MDE files)	Yes
Level of Effort/Difficulty	Low	Moderate-to-High	High

User Level Access Menus

2007-08-14T23:03:00.000-07:00

So after years of reading that people want Microsoft to provide user-level Switchboards, and seeing that Microsoft hasn't done so in 2007, my company decided to release one. This is really exciting news for anyone that has, or will have to, create a database that will be used by more than one user, and where those users have different needs in terms of forms and data access. It was a lot of fun to work on the project and we've gotten some great feedback so far from our customers (that's always the most rewarding part, isn't it!).

Free online tutorials for Access

2007-08-14T21:47:00.000-07:00

Here is a site with some fantastic free tutorials on Microsoft Access. I'm a largely visual person, so these videos hit the spot when it comes to picking up techniques to make better Access applications.

http://www.datapigtechnologies.com/AccessMain.htm

Are you a candidate for outsourcing?

2007-08-07T10:58:00.000-07:00

There comes a point where you may reach your limit in the time you're willing to learn. And whether you reached that point being self-taught or through courses, you may have to ask yourself whether it's time to get a professional to help. Now let me begin by saying I don't offer these sorts of services, so my viewpoint is "relatively" unbiased. I've done a little freelancing in the past, and the lessons I've learned from that experience, and generally being on the requesting end of development work, are hopefully informative. I'll assume you, the reader, may never have been involved in a "professional" development project except to provide user feedback. In my opinion there are a number of things to consider about outsourcing.

Consideration #1: Cost/Benefit
This is key for any undertaking at work, and here it is especially important to weight the costs of your options versus the net benefit you expect. A back of the napkin calculation should suffice:

Option A: Do-It-Yourself

Cost = Time to learn necessary skills plus time to do the actual work times your hourly rate

Benefit = Savings/Sales Increase from new bells and whistles in your database application + future anticipated savings/sales increases for other anticipated projects where you can apply your newfound skills.

Option B: Contract Development

Cost = Time to define project requirements, find a contractor, negotiate a fee, monitor project, clarify questions through project, test final product, and any reiterations to perfect it times your hourly rate.

Benefit = Savings/Sales Increase from new bells and whistles in your database application

There may be no clear winner, and it will also depend on how many great new gadgets you plan to add to your database app. If it's just a few simple features, Option A may do just fine. If you are planning to do some fairly comprehensive automation, make use of the Windows APIs in many ways, or even need to depart from Access given the complexity, B may be a better option. But at least you can make a rational decision with this being one of the data points.

Consideration #2: Commitment

This is a big one, whether you do it yourself or outsource. The question of commitment is, in many cases, an underlying indicator of the benefit you expect when the project is complete, but also speaks to how willing you are to go to bat for your project if things start to take longer or get more expensive than you expected. If you do choose to outsource the work, how committed will you be to hound the developer, if needed, to get it done right and on time? Unlike a do-it-yourself project where you can generally just walk away if things get too hectic or difficult, with a contractor, you're making a commitment to pay even if you get tired of the project. And believe me, no matter how simple the project seems, they generally never are, and it will take a good deal of guidance on your part to get the right end product, even if you can get an all-start contract developer (and there are sure to be some out there). So assess your level of commitment one way or another.

Consideration #3: Who Do You Call?

You have a lot of options out there, fortunately, when it comes to picking a contract developer for Microsoft Access. I won't recommend any since I don't keep abreast of that sort of thing. But you can easily do a search and find several sites that can take you through the process of creating your project scope, requesting bids, etc. There are many, many freelance contract developers out there, and their quality will run the spectrum from downright awful fly-by-night sort of operations to really world-class folks. Some of the better contract developer clearinghouse websites will help you evaluate prospective bids by letting you look at past comments and ratings of people who are bidding on your projects. Referrals should definitely be treated seriously, just like picking someone to paint your house or help with the birth of your child!

Recommendations

So what is a recommended process to acquire, manage, and complete a contract development project? Here is my recommendation, comments on other, better methods also welcome!

1) Make sure you've considered #1 through #3 above.

2) Define your requirements. Focus on exactly what you expect the final product to do from a functional perspective. That means "what the application should do." If you start talking about how you need a VB function to get data from a new table XYZ and put that in a query to produce a report, you're focusing on the wrong level. Document (definitely document!) your requirements thoroughly, and focus on what you expect to be able to do when the project is complete. I'll be posting an example or two in the near future. The more complete and clear your requirements are, the less time you'll spend during the project taking advil as you go back and forth on what something should do or what something means. For those of you that are Seinfield fans, remember the episode where Jerry gets his kitchen cabinets replaced and the contractor asks him a question every five seconds? And when Jerry says "you figure it out!" he ends up with a completely different result than what he wants. Don't let your contractor be that guy, and don't be Jerry.

3) Get multiple bids. Check references. Confirm the scope of the project, the billing rate or fixed fee, and sign a contract! Make sure the contract includes the design, development, testing, progress updates, and some period of post-release support or warranty.

4) Monitor the contractor's work closely, if you don't include it in the contract in the beginning, ask for regular progress updates and samples of what their doing. It ensures you don't fall victim to "throw-it-over-the-fence" contract work where the end product is vastly different than what you wanted because you assumed your contract knew what you meant, and your contract assumed the end product is what you wanted.

5) Avoid scope creep...on both sides. Don't start asking the contractor to add X,Y, and Z if they weren't in your original scope/requirements, unless you're willing to pay for the extra work. And don't let the contractor expand scope without verifying it's all right with you, and agreed upon a fee (if any). You don't want to be on one of those long cab rides from the airport where you're sure there is a shorter way.

6) Make sure the end product is what you asked for, and what you and the contract agreed to during the course of the project. If not, many contract clearinghouses have ways for you to dispute billing.

7) Recommend your contractor, if they were good. Otherwise, let other's know you weren't satisfied, and why.

If you choose to use a contractor, you can often get a better, cleaner end product than what you might have created with the "learn-as-you-go" method. But it takes more effort than just finding the contractor and paying them for the final software! In any case, good luck!

The Importance of Auditing

2007-08-01T21:58:00.000-07:00

In many cases, the Access databases we create a innocuous tools to make life easier by automating menial tasks or creating better reports from large data sets. But in cases where your application is going to be processing important information, and by important I mean anything that might impact your customers, revenue, or have a potential for litigation, I can't stress this enough: audit your data.

If you don't have the money to buy an off-the-shelf product that contains the right tools to ensure user activity and application processing is tracked and timestamped, take the effort to incorporate similar functionality into your own applications. A little story for your entertainment:

About eight years ago I took on a position at a local business, a temporary position until I went to grad school (well that never happened, but that's another story). My first full day was a training on how to process service order records that were sent on to telephone carriers on the Eastern seaboard. My primary role would be to spend eight hours a day performing this laborious twenty-five plus step process over and over again to take raw data, put it into Excel, then output it into other formats for processing. I spent some time with Microsoft Access and got that process down to a two click activity, relying on user input only where it was critical and couldn't be readily automated. These records, by the way, effected the way 9-1-1 calls were routed, and emergency services were dispatched. So you can be sure I had every single step audited: timestamp, user, machine, what was happening to the data, before/after snapshots, etc. To my knowledge, that little database is still processing about 10-20% of the 9-1-1 records on the Eastern seaboard. Scary, but I'm confident it's completely error free.

My point to you, the reader, is that whether it's life critical or business critical data, be sure you've covered your bases, and your behind, when you decide to create Access databases that will do more than just store addresses and names. I won't go into great depth on how to do it, but some general guidance:

1) Make sure you get the handy functions available from Dev Ashish's MVP site: API to retrieve a user's network username, API to retrieve the local machine name,API to retrieve the user's operating system name.

2) Create a function that you can call from forms and other VB functions that will handle the logging events for you. I typically write to either a local table, linked table, or file depending on whether it's a multi-user Access application or sitting on someone's PC. Alternatively, you can use a logging API like what comes in UI Builder for Access, instead of creating your own.

3) Insert the logging function wherever you need to track user behaviour, errors, or other system events. This could be as granular as when the user logs in, changes a record, etc. Or it can be just for errors.

No only is it a great way to protect yourself from potential liability, help debug your apps, it also ensures your solution has a greater level of legitimacy and value to your organization.

The advice you'll get on design

2007-07-28T00:19:00.000-07:00

Let me start by saying there are a lot of very smart experts out there on MS Access. But if there is one piece of advice that gets me going, it's the statement "newbies to Access like to start with what they can see (forms/reports) but this is the last thing you should consider." What I take issue with about statements like this is as follows: if you think like a developer, your application will be made for developers. That means your table structures, code modules, and queries will all be very efficient and (hopefully) architecturally sound. The flip side is, you aren't giving appropriate attention to the end product, and you're more likely to end up with a data-driven design. The end product isn't the tables, functions, and queries that make up your application. It's the menus, forms, reports, and other elements that will comprise the user experience. So what process would I advocate?

1) Determine your requirements. What is it that the end product needs to do to answer your particular problem or need? To help think through the process, you can either spend a lot of time pondering your requirements in your head, or you can whip up a few form/report prototypes and see if you've thought of everything you'll need to meet your needs.

2) Create your design. What is it that your application will need to do in the background to meet your requirements. If you've done a good enough job of thinking through what your requirements are to solve your problem, your design will show that. For example, if you know you'll need to be able to create an extensive number of reports based on dates or other data attributes, your design will be sure to take that into consideration. Similarly, if your requirement is that your application needs to be readily deployed to multiple users without a lot of time copying databases to each person's PC, you're probably going to design your application differently than if you just start creating tables and functions without a thought for the end product.

3) Start creating. Seems simple, but this is where you'll get the opportunity to test your commitment to some of the items you thought of in (1). That is, you may find that actually creating your solution to meet your requirements is harder than it seems, and you'd prefer to sacrifice a few of your requirements to make it easier to develop or maintain.

4) Test. Preferably use someone other than yourself, that may have familiarity with the problem space and desired solution, but hasn't been deeply involved in the development. Why? Because the second you started designing and creating your solution, you begin to think like a developer, not a user. You'll know intimate quirks and their workarounds, something an uninitiated user won't. Thorough testing, especially where your tester purposely tries to "break" things, will ensure you aren't inadvertently signed up to do a great deal of recurring training.

5) Repeat 1-4. The process never ends, does it!

Don’t rely on that Access wizard that creates a form based on your table…

2007-07-22T23:02:00.000-07:00

It may be the easiest, neatest little way to get going quickly. Using the “AutoForm” wizard may seem like the best way to create a form, but it is generally going to lead to the worst user experience. Sure, you can take the resulting AutoForm and tweak it to be a little more usable, but it’s what we call “data driven design.” A philosophy that dominated (by default, really) user interface design for decades, and is only now departing the world. Data driven design means taking the data you want to store, and throwing an interface for user input over it. User driven design, which I hinted at in a previous post, is the way to create happy users and ensure people are more productive.

Rather than putting a form over a table, user driven design will help you to look at the world from a user-process perspective. You are more likely to discover you need to "merge" multiple data sources the user can view or update in a much more efficient manner. For example, while creating new orders (one table), perhaps you need to let the user quickly view key customer information (from a different table) on the same screen, and be able to make changes to your contact history with that customer (yet another table) from the same screen. All if this is missed if you just rely on the Form Wizard.

And while we’re on the topic, I’d suggest avoiding the Access Switchboard too. Use something a bit more friendly for users.

Microsoft Releases Free Access 2007 Runtime!

2007-07-21T21:46:00.000-07:00

Wow! After all these years, the Access team releases the runtime version of Access for free. For those new to Access, why should you care? Well, most importantly, because the runtime version of Access lets you deploy your database application to other users in your business WITHOUT BUYING THE FULL VERSION OF ACCESS! That can mean huge savings to your organization, or mean your project can continue (where it might otherwise have halted because you couldn't deploy to other users given the license costs).
Here is the link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en

A few notes:

The runtime version of Access is terrific, but you need to be much more diligent in your error handling (at least that was the case in previous versions, I'm sure it's the same with 2007). If your application encounters an unhandled error, it will terminate.
The database window will never appear to your users, so you have to be creative in how you expose reports, queries, and data to your users.
The nice toolbars for sort, filter, etc aren't visible to the user. Again, I haven't had time to check out the 2007 version, but that was the case in earlier releases. So it means you have to create form buttons to allow your users to perform the same actions.

What Kind of App Are You?

2007-07-20T12:50:00.000-07:00

Great humor, and also thought provoking when it comes to deciding what sort of user experience you want to offer:

http://headrush.typepad.com/creating_passionate_users/2007/03/is_your_app_an_.html

Kathy Sierra's blog (I'd suggest looking at all of it!) is a fantastic place to consider your user experience. It's a tragedy that she's ceased posting since she received some vicious threatening messages.

Decision #2: How much do you invest?

2007-07-18T20:58:00.000-07:00

When I say “invest,” of course I mean time and energy. The first answer is, as much as you feel like. The second answer is, as much as makes sense. This advice holds true for any business professional, but is particularly useful if you are engaged in a project like creating a useful database application that can save time: be sure the cost/benefit works out in the right direction. If you are making $60K per year as a program manager, and you begin to focus more on creating a database to help your team save time, make sure you’re still giving your company $60K worth of effort. If you can justify the solution you are creating into the $60K, and many times you can, then proceed, my friend. But if you find yourself spending more and more time learning VB or SQL, and less time successfully delivering on Job Requirements A through Z, no one will benefit.

I’d recommend you do the following, really for any professional project you undertake, but especially one that doesn’t fit your “core responsibilities”:

Get your manager’s buy-in
Realistically evaluate what it may take to get to the end product. As a beginning, this will be hard to do without a solid amount of experience, so figure in between two to six months to be pessimistically generous.
Realistically evaluate what benefit you and your organization will get from the end product. If (3) is less than (2), you’d best do the project on your own time.
Sell the value upwards. Make sure what you create, including a return on investment figure, is publicized to your manager. Don’t assume your manager will see the value without some help, smart as they may be.

The upshot is, make sure you focus on your core responsibilities, and don’t let a project take on a life of it’s own to the detriment of your career. Do you have experiences related to this to share?

Design…on the fly, or up front?

2007-07-15T22:40:00.000-07:00

Whether you’re starting afresh, you’ve already started and have a good functioning solution, or you’re contemplating your next project, don’t sacrifice the wisdom of good design in order to start having fun creating forms and reports. Top 3 reasons why:

1) Good Form. If you’ve ever played piano, golf, or even used a pencil, you know it’s important to practice good form…it reduces future physical or mental pain.

2) Minimize re-work. If you start a project without enough consideration for design, you’re likely to pay for it later in re-work or lack of functionality.

3) Create happy users, avoid extensive training. If your application will be used by anyone other than yourself, the last thing you want to do is spend a great deal of time training each new users on the idiosyncrasies of your solution. From making sure the field names are well labeled, creating intuitive forms with field arrangements that map to how a user thinks, to capturing all the data needed for someone to carry out their duties, doing a bit of design up front can save time, energy, and frustration in the long-term.

So what is the secret to good design? Well, I won’t pretend to be the expert, there are hundreds of books written on the topic. But for me, it is the same as creating a good presentation or letter…think like your audience. In this case, not you as the user, but someone who will wasn’t involved in the thought process for the design, but needs to understand how to use your application nonetheless.

How much time you actually spend designing is very subjective and dependent on how complex your project is going to be. But do put the horse before the cart when it comes to design.

You're Not A Professional Programmer

2007-07-10T23:23:00.000-07:00

Don't let the fact you're not a professional programmer dissuade you from learning to do even light coding. Once you master things like macros, queries, designing simple forms, and creating tables, you're ready to begin creating truly rich user experiences. You know, validating what the user entered, launching a hyperlink when they double click on a field, filtering subforms based a button selected, etc. To do so, you need to learn about Visual Basic for Applications (VBA).

Think of learning VBA in Access as the equivalent to learning a new spoken language. Sure, you can't order a sandwich and bottled water from a sidewalk cafe with VBA, but it is a great way to challenge yourself and keep your mind nimble. And don't give in to elitism when it comes to programming. Some might call what you create "scripts" or "macros," but make no mistake, when you learn to code in Access, you're doing more than just recording macros. You'll find that a good majority of Access is exposed for you to command from VBA, and with some extra learning, you can find out how to command Windows interfaces to create even more comprehensive solutions.

Like I mentioned in my last post, one of the easiest ways to learn is by example. When you start, it will look very scary. Spend some time taking baby steps (yes, the dreaded "hello world" examples). Try recording macros and then look at the code to see what it took in VBA to automate what you did using the keyboard and mouse. Then move on to learn about the general framework for how things get done using VBA (the object model in tech-speak). Curiosity and a few hours messing around can get you quite far if you are patient with yourself. The same would hold true if you had a few hours to sit with someone that speaks another language...

Learn By Example

2007-07-06T23:56:00.000-07:00

Here's an important tip, especially if you are starting out like I did, with absolutely no knowledge of SQL, Visual Basic, macros, etc. Learn by example. Find any examples you can, dissect them, find out how they tick, and then apply that to your own project. Microsoft put out some terrific example databases for Access. The great thing about them is that they demonstrate a specific feature you might want to add to your own solution, like sorting a report dynamically, or closing a report automatically if it has no data.

Report sample database

Query sample database

Northwind Traders sample database

Decision #1 - When NOT to D-I-Y

2007-07-06T23:19:00.001-07:00

In most cases, this decision will be easy for you. Surely creating your own database is better than what you're starting with, right? In the vast majority of cases, probably very true. But there are some very good reasons to contemplate other solutions. A few things to consider below:

1. What will the database be used for?
If you're thinking of creating your own database to store orders, key customer information, or any other sort of "transactional" or business-critical information, think about it carefully. Now this will largely depend on the size of your organization, but if you have any sort of development or IT resources, or adequate money, consider leveraging your development staff to create the solution you need, or buy one off the shelf. If you work in a small company, creating your own database is probably the best, and appropriate, option you have. If your database is going to be used to store data for what I'll call "secondary use," like analysis or data crunching, creating your own solution is a great way to go. You don't need to worry about multiple simultaneous users, audit trails, etc.

2. Who will be using it?
I'm reminded of a project I undertook years ago when I was in sales. Our customer relationship management (CRM) implementation was a disaster, and my VP wanted something that our group of eight customer-facing staff could use to track activities and opportunities. Simple enough, I had created plenty of databases at that point. I failed to realize just how slow a desktop database application like Access performs when it's sitting on a shared file server. When more than one user was in it, it was abysmal. My point is this, if it's just you and a few others working in the database infrequently, Access is a good choice. If it's you and ten+ colleagues looking to maintain important information on a daily basis, you're probably better suited for a more robust solution. That might come in the form of purchased software, your own development group creating a more scalable application, or putting in a SQL Server or MySQL database with an Access front-end.

3. Where do you want to go?
After years commuting sixty miles, I took a job with one of the largest software companies in the world, much closer to home. One of the things we tell customers when they are evaluating our software versus their own in-house solution is this...do you want to continue focusing your energy and time developing software that is already available and evolving over time? To be clear, this blog has nothing to do with my primary employer or what they sell, but I'll pepper my comments with experience from there. My point is this, if what you're doing is considered to be fairly "mainstream" in terms of enterprise software, it's important to think about whether ten hours here and ten hours there may add up to a whole lot of time just to create something that already exists (time-value of money). In many cases, you're probably creating something very specialized for your business needs that just doesn't exist in any pre-packaged software. But be sure to do the research. If something already exists, is it light-years ahead of what you're doing and in your budget ballpark? You'd be amazed at what is out there. If something does exist, but it's out of your price point or doesn't quite fit your needs, D-I-Y on with confidence.

4. Will it get you praise
If you happen to report to someone (i.e., you're not your own boss), test the waters with what you're planning to do. In most cases, you're no where near a software developer much less in a development/IT role. So if your manager isn't keen on you becoming one on the side, find out early. You may be able to bring them around ("we can save X hours a week if you let me"), or get them on your side to push for the "right" people to do it. Either way, taking on projects like this can get your promoted, or stall your career if your management thinks you're not focusing on what's really important.

There Must Be A Better Way

2007-07-06T22:53:00.000-07:00

About You
You're a professional out in the wide world who one day discovered their set of Excel spreadsheets was getting out of hand. You were curious about this item on your start menu, Microsoft Access, and decided to see what it was all about. Once you got past the deceptive key icon, which hints that Access is some sort of security application, you started to see potential to take that spreadsheet with ten tabs and thousands of rows into something a little more useful for you and your office mates. A few months later, and a few hours of self-learning under your belt, and you've got a fairly slick database that saves you time and money. Now you're wondering....what else could I do to make it better...should we migrate to SQL server...how can I make it easier to change forms...how do I handle multiple users...do I turn this over to our development group...do I buy off the shelf software...and so on.

About Me
I'm you, well, not exactly. But I can say I've been there myself, faced many of the same decision paths, asked many of the same questions, and found it incredibly fulfilling to easily create my own applications with an interface that is just the way it should be for my business. I started out trying to reduce an awful, ugly 28 step manual process of taking machine data, putting it into Excel, changing it, and then dumping it back into a machine readable format or pretty fax report. Interestingly enough, that ugly manual process was used to manage about 20% of all 9-1-1 emergency address records on the Eastern seaboard until 2000. So when they hired me fresh out of college, I sat through the training on this nasty little error-prone process and thought....there must be a better way.