Tools for Access Developers - Free Downloads

Written by Claude Berman, 2019: I started out in 1992 knowing nothing about database design. I had grown to love and appreciate the usefulness of software in the course of obtaining a degree in Mechanical Engineering. My late father-in-law, Jack Dudley (also an engineer) led an effort to develop of system for determining the number of people needed to clean an educational facility and I offered to help develop and computerize that system. We started out with spreadsheets and quickly decided to create a database program - and that was a leap of faith since I knew nothing about relational database design and nothing about software development.

One thing lead to another, and after a good 300 hours spinning my wheels, I finally got some traction. Another 300-400 hours later, we released the first, simple, version of the software priced at $99.00. I kept the "day job" until 2012, slowly growing the business and the application. Jack had some wonderful sayings, one of which was to the effect that slow, steady pressure over time was more effective and powerful than a shock or hammer blow. I've come to appreciate that wisdom over the years. In fact, early on I was a kill and eat kind of guy who preferred a quick hit over a well-engineered and complete solution. My personality has changed slowly to the point where I'm much more slow deliberate with careful planning and I shoot for near-perfect execution over the rapid development.

I find it useful at times to blow out something quickly just to test and model a new idea or technique. But once I've decided to develop something for the general public, the situation changes and a more careful approach is taken. I find now, for example, I cannot develop anything without error trapping and the first thing that goes into a new module is the error handling.

Below are some sample database with tools and techniques developed over the years. If you find them useful, consider offering some of your own when the time comes and you become well versed in the subject. Remember, you probably could not have gotten as far as you did without the help of others, and if you can give back a little, it is only fair and the right thing to do.

1. Layout Assistant: sample database showing how controls can be group and moved out of the way during the design phase and then quickly returned to their original positions.

2. Data Mover: sample database with code to move data from one table to another, including attachments without append queries (which don't work with attachment fields, anyway).

3. Data Migration Assistant: sample database showing how to allow users to browse to an older back-end database and "self-upgrade" for easy field upgrades.

4. Excel Automation: Sample database that writes an eight Worksheet Excel Workbook with formatting and formulas (for error checking). The system will append new records and update existing ones from changes made in the Workbook.

Layout Assistant (Sample Database and Documentation)

Click Here to download sample Layout Assistant Database. Click here for PDF documentation

The intent of this tool is to allow one to group controls on a form and move groups out of the way during the design phase, and then easily return them to their original positions when done. This is handy for complex forms with many controls. I have several forms that have many layers of controls that I need to hide and unhide on occasion. Different groups of controls are shown to the user depending on the circumstances.

My application was begun in before we had tab controls so; I had to use the enabled and visible properties often to control the interface.

For example, the Home Page (form Main Switchboard) has been designed with a clean appearance and I don't really want to clutter it with tabs (although I may have to eventually for some of the things I want to do with it). I's extremely complex, with over 300 controls and has three modes:

1. First time the application is run: we show a few controls to record the name of the organization and facility type, then allow the user to finish and get prompted for an internet unlock code (license key).

2. Normal Home Page: shows controls for general user information and some decorative controls for visual organization.

3. Control Panel Mode: hides main layer (normal home page visible) and reveals a number of user-adjustable variables.

My problem as a developer was that the sheer clutter of the form made it nearly impossible to add new controls and/or work with existing controls. My first solution was to do some custom functions that allowed me to hard-code all controls (and their positions) I wanted to move out of the way and then move them back. But this approach proved almost as impossible to maintain as the forms in question.

It occurred to me that I could use the strengths of Access to solve the development issue I'd created for myself, and, after a good 20 hours of work, the results seem useful.

Data Mover (Sample Database and Documentation)

Click Here to download sample Data Mover Database. Click here to view PDF documentation

The intent of this tool is to allow one to move data from one table to another when attachment fields are included. The issue with attachment fields is that Microsoft didn't provide any way to move attachments directly from one table to another with standard append queries. This code solves the problems by providing a powerful workaround.

First, it can be a direct replacement for queries that simply append data from similarly structured table to another as one simply calls the movedata function and supply the two table names (source and target) as strings.

Second, it solves the attachment field issue since it allows one to optionally specify the name of an attachment field and it loops through the source table's attachment fields, saving each attachment to disk and then loading the attachments into the corresponding field in the target table. Thus, it handles any number of multiple attachments per record.

Back-End Field Upgrading (Sample Database and Documentation)

Click Here to download sample Back-End Migration Database. Click here to view PDF documentation

The intent of this tool is to allow developers to have an easy way of field upgrading existing customer database installations without having to retrieve their databases and do it for them. The assumption is that a split architecture system is used, meaning that code and other objects (forms, reports, queries) all reside in one database and that a back end database consisting of tables only is used via linkage of the tables to the front end.

In my experience, the most common upgrade has been to distribute a new front end but that as the pace of development increases, more often than not, major upgrades (as opposed to bug fixes) require changing the table structure of the back end (adding new tables, fields and relationships). Until now, the process of upgrading our customers required us to ask the customer to:

1. Return their existing back end to us.

2. Wait until we could migrate (move) their data into the newer version of the application by moving their data from the older back end into the newer one.

3. Install and license a new front-end/back-end pair.

Our sample database, Data Migration Assistant, demonstrates a solution to this problem that reduces the time and complexity of the standard upgrade process considerably. It is provided free of charge as a courtesy to developers and our way of thanking the large number of contributor's to Access help forums who help others with no repayment. As I look back, I realize that without the answers and help I obtained over the years, there is no way I could have hoped to do the things I did.

This database demonstrates our newest approach to upgrades and that approach reduces the process to a simple, 2-step process:

1. Rename/Move: User (or IT personnel) installs and licenses a new front-end/back-end pair after moving or renaming their current pair. We do not change the names of these files from version to version since that requires downloading and running very large installer (setup.exe).

2. Migrate Data: User activates a function in our software that allows browsing to the older back-end file and selecting it. Once this is done, the system moves all the data from the older back-end to the new-back-end.

For an even more powerful system that can also add tables, modify table structure and run code, download Peter D. Hibbs's UBE Utility and view his documentation here. This elements the need to migrate data into a new-version back-end since it just modifies it as needed. The advantage to this approach is that one doesn't have to move any data, the tables are simply modified in-situ. I use it in one of my applications to perform over 500 changes to the back end. Note that it requires a field in a back-end table to denote the version number. I figured out a way to do an initial bootstrap outside of Peter's System. If you need help with that, and you might if you have a lot of back ends in the field, contact me for consultation.

Excel Automation (Sample 2-Way Communications Database)

Click Here to download sample Excel Synchronization Database

This sample will read and write (from scratch), an Excel workbook with eight worksheet tabs. It will created the needed formulas in the worksheets to provide simple error checking by the Excel user and will then allow transfer of the data from Excel into the appropriate tables and can handle both appending new records and updating existing ones. Have fun, this is a distillation of about four weeks of hard work Note there is a code reference to Office 2010 and requires it to open an instance of Excel. You may be able to change that to Office 12 object libraries, but I haven't tested it.

One of my commercial applications handles large amounts of data. For example, a typical facility may have 3000 rooms per million square feet and we have many facilities sized in excess of five million square feet. Often our customers have this data in a spreadsheet file, or another application that can output data via a csv file. We also have distributors working in the field who need to collect data. People seem to prefer the speed of Excel over database work when it comes to getting the data set up.

For years, I've had some semi-automated, internal processes include code and queries, but with our move to encompass building service contractors and more distribution channels, the time came to simply trump all other competitors with simply the best tools of this nature on the market. It took me about four weeks to perfect the first iteration used for space inventory (buildings, floors, rooms, etc) in total an 11 worksheet tab production. The sample is based on a simpler data structure for our inspection system.

Additional Resources for Access/VBA Developers

Peter's Software: I found the Shrinker-Stretcher tool for sizing the application to the screen a wonderful addition to the application, it opened up a whole new world to my eyes. Peter has a number of reasonably priced bolt-on modules, as well as many useful free modules.

Albert Kallal: Mentioned in the code in some of the tools above, you can't find many developers with his ability to consult or help you out of a jam.

Point Limited: Need multi-language versions of Access applications and on-the-fly dynamic translation ability, look no further - it's a brilliant piece of code and very reasonably priced. Based in Moscow, this site doesn't come up readily in Google here in the U.S., perhaps this will help (after the crawlers take there sweet time).

Ribbon Creator: The best ribbon management tool I've found comes out of Germany. It shows you what the ribbon will look like as you create it and then allows you to automatically update your application (Access, Work, Excel). Thanks Gunter.

Allen Browne: The single best site I've found for useful tips, code and sample databases I've found comes out of Australia. His "Copy SQL Statement From Query to VBA" code allows one to save time by first creating a query, and then converting that to code-compatible SQL.

JKP Application Development: This developer, based in Holland, showed me how easy it is to pop out beautiful Excel pivot table graphs. I also love the all-code tree view control he and some colleagues developed for Access and Excel. Microsoft doesn't provide a working tree view control now and it was never Access-native. Jan is fun to work with and very reasonable

Developer Consultation

The above samples are free of charge. You may download them and incorporate any or all code contained therein into your own applications. If you need assistance upgrading them to a later version of access, making the code 64-Bit Office compatible, or simply figuring out how to use the code, normal, reasonable rates apply.