Access Design Services

Issues Our Clients Encounter Maintaining And Upgrading Access Databases

Many Microsoft Access database applications come to us as antiques. Often developed years ago by amateurs they are often painful to look at and more painful to maintain. Corporate IT is trying its best to reduce Access usage or get rid of it altogether. And, the main reason is that when the original "developer" leaves, they're handed a mess and expected to fix it as it's now mission critical. With no documentation in code, poor design antiquated code techniques, it can be a nightmare to work on.

I'd compare it to something like house wiring. Let's say an untrained handyman rewired your house. Nothing was done professionally and nothing meets code. But, it works, so you think it's fine. Until you go to sell your house. Now, an inspector comes in and notices the problem. In order to sell your house, you're stuck with a complete, professional rewiring. That's what developer's are up against when we are asked to work on these types of databases.

Other things can happen, let's say you have to upgrade from an older version of Microsoft Access. In the newer version, everything collapses since newer versions of Access can't use some of the code. Sometimes it works using its original compiled state, but as soon as something is changed in the code, even insignificantly, the code fails as it cannot recompile.

I used to work for Fram filter as a mechanical engineer and remember the old slogan they used on TV, which showed a mechanic deep into an engine repair who said "Pay me now, or pay me later". The message was that either pay for maintenance of getting your oil filter changed regularly, or end up paying much more to repair the engine. If you're interested, here is one of the original commercials from 1972.

For many of the old Access databases I see, someone developed the application in the 90's originally, and maybe someone ported it to work with Access 2000 (which generally worked through Access 2003). But after that, over the last 20 years or so, nobody paid for those regular oil and filter changes. Access 2007 came (a big one) and then 2010, 2013, 2016. So now we're dealing not only with bad wiring (it's original amateur state) but also the material used is out of the date, the insulation's frayed, the fuses need replacing with circuit breakers. A professional electrician probably would refuse to "patch" it since he/she would have liability issues if anything else failed, and fail it would. You would be told, you have to replace everything to bring the house up to code. Once that is done, in the future, any licensed electrician would know how to work on the system since it would meet standards.

That's the way it is in software development. We work with "standards" created by the community as a whole and anything built to those standards is understandable by any other professional. The standards aren't in any single "book" for Microsoft Access since there is no governing body. But exist they do. The exist for small things like naming conventions, and they exist for important things like error trapping. Just as important, the professional community has learned, often the hard way, what things to avoid, and many of those things sit like bombs in old applications.

​

Solutions to Issues

​

Quotation Process: At no charge, I'll analyze your application and give you a quote. If it's compatible with newer versions of Access, and/or an extremely simple system with few objects (forms, tables, queries, etc.) it's one thing. If it's a complete mess, as described in the paragraph above, it's another thing. Like an electrician quoting on work on your house, I've got to look are what you have and I send you an upload link. If I need more information, I'll spend some time with you online where screen sharing can help understanding. With your permission, we record the meeting so I can go back and refer to it as we develop a plan and quote.

Quotes are "free" in the upfront sense, but built into the pricing, of course. That's one of the reasons small jobs cost more on an hourly basis. We add the assessment time to out quote (sometimes it can take hours).

Quote Evaluation: Once you get a quote, part of the business consideration on your part relates to how useful the application has been, are there any off-the-shelf solutions you could use instead, how many users. I would expect you might want several quotes for comparison.

Package Quote: If the system is very important, you may opt for a complete upgrade and modernization package. This can include some of the items listed below. Note that many of the items involve known solutions in my VBA code "toolbox" that I can just drop in, but since the implementation often involves a variable number of  objects, so there is no fixed price for the modernization and upgrade package. And, most elements are optional. Here is a full list of things possible and recommendations depend on future considerations:

1. Super-Decompilation: Every MS Access Database I've seen (even my own) gets some elements of corruption. It creeps in unnoticed over time, but like a crack in a bridge beam, it's bound to cause issues. To really decompile completely, I create a new file and import all the objects (forms, code, queries, etec.) into the new database container. This forces a complete decompile and when a recompile is done, every issue is exposed. There are some other, well-known techniques to fix them such as the built-in MS Access command "Compact and Repair". But we've learned, and so has the professional community, that this doesn't fix deep-rooted compilation issues. There is also a trick whereby the application is opened with the command line switch to decompile is used. And, until I learned better, I assumed this decompile/decompile method would fix and uncover all issues, but it doesn't. I got a clue from Microsoft back in 1995 when I called support (back when Microsoft still offered free, US-Based support). I had a corruption issue and the engineer told me to try moving all objects into a new database container. Ever since then, when I got stuck, and the decompile/compile technique didn't help, I'd try this and sure enough, I'd find out why things weren't working as expected. The other thing this trick does is force a super-decompilation and it uncovers deep problems, the fixing of which demands following a very strict syntax usage for the latest version of Access. Sometimes the old legacy code will work, until one has to modify it and then it breaks. This lets me find and fix everything in one fell swoop. It doesn't sound sexy and it doesn't actually change anything obvious. Consider it a filter and lube change.

2. Error Trapping and Protect Users from unintelligible error messages: Every professional developer understands the importance of error trapping in code. Otherwise, weird messages pop-up to users. Worse, in many cases the system will just crash and Access will close right in the middle of whatever you were doing. Personally, I use it in development to debug issues so error messages never happen, a circular thing. There are other usages of error trapping such as handling expected errors and I use that when needed. But at a minimum, my philosophy is to insert at least basic error handling into the code for every procedure and function. While recommended, many developers don't do it. But I do it since everything I've overhauled should be bulletproof. It doesn't take long, the time dependent on the amount of code, and it doesn't hurt anything if unneeded. Consider it analogous making sure the filter and drain bolt are tightened to the correct torque.

3. Convert all macros to code: Macros were a simple way to program things to happen and used by beginners extensively. The problem with them is that they aren't searchable and not easily configured or changed as compared with true VBA (Visual Basic-Access) Code familiar to all developers. We developers absolutely hate them. When they're used, they can be embedded or called directly. I've developed my own code to find where both types exist and that allows me to "groom" your application by replacing them with code. Consider this like cleaning the engine. It makes all further work easier and helps identify leaks.

4. Splitting the database: Professionals split databases into two separate Access databases. The application you open is called the "Front End" and it contains all the objects you need (Forms, Reports, etc.). It reads data from the tables which are located in the other access database that normally resides on a network share. This second Access database file contains only tables. This allows efficient sharing of data, and also provides the benefit of network backups. We can easily replace your front end with no harm lost, but your dead if you lose your data. This is a normal practice and par for the course in the industry. But I take it a few steps further with automatic linkage code and also, in many cases, adding a back end updater utility that can automatically add needed tables to the back end when new features require them. Optionally, I can provide a mechanism to easily browse to a second back end if you desire to do any testing work without changing your main data file (back end). This is like having insurance on your car as well as an engine tune-up.

5. User Interface Overhaul: This can involve many options, and costs, and we can discuss. The most straightforward is to redesign your forms to take advantage of larger monitors available today and this lets one see much more data at a glance. Personally, I'm a stickler for a well designed interface with clearly labeled captions and a modern look and feel. All controls have to lined up properly in my opinion and sized to handle the data they contain. Beyond that, if your old application contained toolbar menus that all get moved to the "Add-Ins" ribbon, I can create custom office ribbons that look like the standard ribbons in Access, Word, Excel and Outlook, except they have your commands on them. Going further, we can get rid of all the "switchboards" those forms that have rows of command buttons that often open other switchboards. This old navigation system is hard to navigate and considered obsolete. Obviously, this is like giving your old car a paint job and redoing the interior.

6. Security: Often you have people with different roles and its best to keep their navigation options limited. That problem is solved with user logins and code to control what actions they can and cannot do. Beyond that, if needed and or desired, logins can be automatic on a PC once we "teach" the system to look at who's logged onto a computer. This is like having a regular key and a valet key.

7. New Features: Most of the above work is either boring to tedious for me (except the interface overhaul). Developers live for the opportunity to develop new features. Working on functionality requires the most in terms of my 25 years of experience, and since I know many tricks (although I'll never say I know every trick in the book) I can do it quickly. This is the most cost-effective part of hiring someone like me.  But to do it most efficiently, it requires some of the steps above such as error trapping, getting rid of macros, super-decompilation. Like the mechanic said, "Pay me now, or pay me later".

Most clients don't want or need or can't afford some of the options above. But if they do, they take enough time to justify the price of purchasing work in blocks of 10 hours as mentioned above. If extensive enough, we can discuss other pricing options to keep costs under control. But just remember, "inexpensive" doesn't mean "cheaply made". It just means we're not greedy.