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.
LEAD DEVELOPER EXPERIENCE
Roles & Responsibilities
MANAGING MEMBER, IMANAGE INFORMED LLC
June 1992 - Present
Develops process engineering software tools for environmental service departments for analysis and optimization or workflow and quality. Sectors supported include healthcare and higher education.
Experienced with SQL Server backend and mixed native/SQL table management.
Conducts training and consulting remotely and on-site for groups ranging in size from 1 – 30 users.
Creates all supporting documentation including instruction manuals and installation instructions.
Proficient at upgrading older databases and enhancing with current version capabilities.
Coordinates with IT departments to perform implementations and troubleshoots technical issues.
Manages out-sourced development teams in India, Canada and the Soviet Union.
Ported software through all versions from Access 1.1 (1993) to Access 2016. Proficient with older version Microsoft Access security model and associated MDW files. Develops newer version-compatible replacement code to ensure user level security and privileges.
Wrote and maintains over 200,000 lines of VBA code in core application.
Strong SQL skills, including complex SQL embedded in code and thousands of canned queries
Designed and maintains user-friendly interface with logical organization and ascetically appealing design.
Enabled multiple user concurrency via split architecture.
Developed and maintains multiple user levels with dynamic ribbon (menu) adjustment per user level login.
Designed and built Microsoft Office™ interoperability system for data migration and manipulation via input/output to multi-tab Excel workbook (2-way synchronization between spreadsheet and database).
Developed sophisticated reporting system to include output to Excel pivot-table charts and ad hoc queries.
Utilizes and modifies various 3rd party code and, including class modules, significantly upgrading them.
Developed PC licensing system with control over user functions and database characteristics.
Developed companion applications including a quality assurance system with remote off-line operation and on-line synchronization when connection available. Synchronization includes graphic files as stored attachments, probably the only such Microsoft Access-based system in existence.
Migrating Access Databases to SQL-Server (back end migrations) and connecting to MS Access.
June 1990 - October 2011
Design/R&D Engineer (Mechanical) specializing in new product development with an outstanding track record of managing the entire development cycle from inception/feasibility to release. Combines passion, and imagination with solid mathematical analysis to generate optimal solutions. Expert at winnowing poor ideas in feasibility phase and troubleshooting problems during development. Effective team member who combines individual contributor skills with compassionate support of all colleagues. Develops mathematical models by deriving governing equations from first principles. Expert in feasibility analysis and benchmarking.
Conceived and patented a waste transfer system based on positive pressure differential that offers significant advantages over all current technology. US Patent 6,804,840
Conceived and patented a minimally invasive hybrid jet injector for drug delivery. Device operates at 10% of normal pressures allowing subcutaneous injection with glass pressure vessel. Device patented and currently in production by Antares Pharma. US Patents 6,746,429 | 6,682,504 | 6,565,553 | 6,428,528 | 6,309,371
Conceived and developed FRAM's SureGrip product leading to approximately $40 million (10%) increased annual sales as company’s entire automotive filtration line now utilizes the concept.
Developed new coolant filters using engineering polymers (Polysulfone and Polyarylsulfone) and patented replaceable, environmentally friendly inner cartridges. US Patents 5,549,821 | 5,556,542
Invented laminar flow, thin channel centrifuge, including mathematical model. Characterized by internal energy recovery and non-turbulent flow, the device operates with minimal parasitic losses.
Developed process engineering software tools for environmental services departments including QA, analysis of labor, time studies, consumables and equipment. Sales in excess of $1.5M and over 600 copies sold. Manages all aspects of distribution from coding to training & marketing. Works well in a group environment as well as working on his own with little to no supervision. Mostly as a sideline, generated over $1.5M in sales over the past ten years.
2003 – November 2011 Micromold Products, Inc. Yonkers, New York
Manager of Engineering PTFE (Teflon™) component manufacturer
Managed engineering efforts in small, specialty manufacturing operation supporting all aspects of design and manufacturing
Manage all aspects of PTFE product line engineering including specifications, design and development. Ensure compliance with relevant ASTM, NSF and other standards
Generate all marketing collateral and engineering documentation for major product lines
Manage design and development projects for new product development including classified development projects for a major defense firm
1999 – 2003 Thetford Corporation Ann Arbor, Michigan
Senior Engineer Recreational vehicle accessories manufacturer
Responsible for engineering and mechanism design in RV product lines from early conception phase through production release. Supported almost all major projects when engineering problems arose, utilizing analysis and testing to ensure teams met project goals
Invented and prototyped new, positive pressure waste transfer system after benchmarking competitors’ systems. System included plastic pressure vessel, electronic controls, sensors, and flow control devices. US Patent 6,804,840
1996 – 1999 Medi-Ject Corporation Plymouth, Minnesota
Product Engineer Medical device manufacturer
Led research efforts to minimize power requirements for new needle-free jet-injector including development of test equipment and procedures, mathematical analysis and field-testing on cadavers
Prototyped and proved minimally invasive injector concept using a combination of analysis, laboratory research and cadaver testing. Performed patentability analysis and worked with legal consul to ensure broad patent protection. US Patents 6,746,429 6,682,504 6,565,553 6,428,528 6,309,371
1995 – 1996 AlliedSignal Filters & Spark Plugs (FRAM™) Perrysburg, Ohio
Project Engineer Automotive filtration manufacturer
Lead engineer for coolant filtration product lines managing new product design, and maintenance of existing product lines
Introduced new concepts to marketing and sales including the SureGRIPtm filter coating that strongly impacted the DIY market leading to approximately $40 million (10%) increased annual sales as company’s entire automotive filtration line now utilizes the concept
1991 – 1995 Fleetguard, Inc. (sub of Cummins Engine) Cookeville, Tennessee
Project Engineer Heavy duty filtration manufacturer
Lead engineer for coolant filtration product lines managing new product design, and maintenance of existing product lines. Introduced blow-molded coolant filter and finalized production
First team member of Advanced New Product Development Group and major contributor to all new product concept and development efforts. First engineer in company trained to use ProEngineer solid modeling package and responsible for training and guidance of peers in using the tool. Performed FEA on selected components for cost/weight/strength optimization
CAD: ProEngineer (5 years’ experience), SolidWorks (10 years’ experience), AutoCAD (2 years’ experience) Most recent SolidWorks 2016
Analysis Software: FEA (Mechanica), CFD and other CAE packages as appropriate.
Communication: Excellent writer and speaker who enjoys training others and presenting data.
Product Classes: Injection Molded, Blow-Molded, Sheet Metal, Machined, Cast, Extruded, Stamped
QA Background: Six-Sigma/Black Belt, Geometric Dimensioning & Tolerancing, Statistical Quality Control
Technical Writing: Proficient writing user guides, technical reference works, and marketing collateral
General Software: Expert-Level Access, Excel, Word, Outlook, MS Project and Basic Visio skills.
Other Software: Google Analytics, SEO, Adobe Products, ERP
BS in Mechanical Engineering 1990
University of Minnesota Minneapolis, Minnesota G.P.A. 3.67 of 4.0
Undergraduate concentration in thermal and mechanical design.
Two years graduate work in particle technology and filtration.
Awarded: 3M Scholarship for high achievement.
Sealy Corporation Award for analysis in design as an undergraduate.