I work for a public health study, and I have been asked to help manage the workflow for an upcoming project. We will be choosing subjects, tracking our contact with them, interviewing them, placing them in one of several interventions, etc. Previously the staff used a large number of Excel spreadsheets, but I was asked to help reduce data duplication and help automate processes.

My initial thought was to use MS Access to develop a custom application. But I worry that the staff will dislike being asked to switch from Excel, where everything is flexible, to Access, where everything is structured.

For example, one way they kept track of subject status was to color rows in Excel. This wasn't an ideal situation - other users didn't know what row colors meant - but it was very quick and easy. Or, as a second example, a certain staff member makes many phone calls to subjects. When a subject calls back, she must quickly find that subject's name. This was easy in Excel - she simply pressed Cntl-F to find the number on her caller ID - but in Access even small things like this must be manually coded.

I considered trying to make Excel function as a database, but decided it would be too unwieldy. Another consideration is that I'm only here temporarily, so I may not be able to support the application in the future.

Do you know of any form components, design techniques, standard practices, or ideas for helping to smooth the transition? How can I have both structured, automated processes, and a usable interface?

  • As an aside, I considered trying to make Excel function as a database, but decided it would be too unwieldy.
    – Patrick
    Aug 17, 2011 at 2:30
  • 4
    You are right, Excel as a database is too unwieldy and Access as a replacement can be a lot of work and may not be maintainable when you are gone. Why not marry the two? Excel is perfectly capable to work with MS Access as a "data provider", MS Access is perfectly suited to avoid data duplication and your users get to keep an interface that they know... Aug 17, 2011 at 6:26
  • Do you mean to use Excel tables as "linked" tables in Access, running the logic through Access, and exporting the data to other Excel tables?
    – Patrick
    Aug 17, 2011 at 15:24
  • No, I mean using Access as the database and using Excel as the front end to that database. Aug 17, 2011 at 15:34
  • I've looked into that, but there seem to be many people advising against it. Do you know of any examples of applications developed this way?
    – Patrick
    Aug 17, 2011 at 15:47

2 Answers 2


...but I was asked to help reduce data duplication and help automate processes.

Well, if that is your job description, then the staff simply has to deal with a new system. Of course, assuming you will do a good job.

And finally, this is a temporary job, so I won't be around to support the application permanently, so take that into consideration.

For now, it is just an open question whether there is support needed in future. First, analyse how the functionality of the present Excel sheets have grown in the past. If not so much, or not at all, then there is a huge probability that a new Access application might suffice for years.

Although things are structured underneath in Access, what your user interface will present is something completely different and could become as flexible as you like. For instance: it is perfectly possible to offer all editable fields in a formattable way like RichEdit controls. Though it requires some above average VBA skills and probably considerable implementation time.

A much more easy approach is to present the forms in data view so they have the exact same appearance as they have now in Excel. You could also provide an option to switch between views. That would provide some comfort for the users after the transition. The in-between solution is to design the forms in table figuration which results in the same appearance, but in more control over the behavior. But a columned layout should be preferred; the users just have to become familiar with it.

About your two examples:

  • Offer and present a legend for the possible row colors. Use a listbox or toolbar to add or delete the row color of the current record(s). Offer customization for the colors and color descriptions with a separate form.
  • CTRL-F works just fine for forms. You may want to present a custom find dialog, but the default dialog offers enough capabilities without being too complicated. But preferably there would be a search box at the top of your form, or even at the top of every column, to eliminate the need for that keyboard shortcut.

There is just so much more possible with Access...

Finally, consider suppressing all default Access commands and menu-bars, because it is very easy to screw up the whole database and/or application.

  • 1
    You can always add an export to Excel command, might there be an emergency when you are gone.
    – NGLN
    Aug 17, 2011 at 19:14
  • This is an excellent answer - thank you for the thought you put in. After fiddling around with various methods for combining Excel and Access today and finding them all unwieldy and problematic, I think I will move forward with a full on Access implementation. Thanks!
    – Patrick
    Aug 17, 2011 at 23:38

Apart from what NGLN has already described. You could create input forms in Access to have the input part 'fixed', and then you can create Queries in Excel to access that data and have all the flexibility you want! I'd keep these queries read-only though.

A large number of users? Are they all on-site? Access is not a good choice for multi-site data access. For a serious application, I'd use SQL-Server, but that has nothing to do with UX. The possibility to create Excel Queries remains the same with SQL-Server.

  • Thankfully it will be used by a small on-site team of around five people. I will look into when querying Excel would be useful, but I'll be pursuing an Access-only solution.
    – Patrick
    Aug 18, 2011 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.