Tools for Access Developers - Free Downloads

Written By Claude Berman 6/23/2019
  I started out 20 years ago 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. Below you will find:
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.

5, Simple Licensing System: to control use of the software on client's machines. It allows a 10-day trial and then requires you to send them a licensing file to place in the application folder.

Layout Assistant (Sample Database and Documentation)

Click Here to download sample Layout Assistant Database. Click here for PDF documentation
  1. Managing Director
Clicking the Expand button in the design tool above moves controls out of the way. Below are two views of the same form. Clicking the Collapse button reverses the process and return the controls to their original position. Below is an example of this effect. Two views of the same form are shown. Click on an image to expand it
  1. Managing Director
  2. Managing Director

Data Mover (Sample Database and Documentation)

Click Here to download sample Data Mover Database. Click here to view PDF documentation
  1. Managing Director
  2. Managing Director

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 is the last release, version 1.3 and I can't find it available anywhere else. The advantage to this approach is that one doesn't have to move any data, the tables are simply modified in-situ. Currently, 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 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. I'd guess we could get it done in an hour or so for a fee of $70. Below an illustration of my updating utility.

  1. Managing Director

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. If you're a competitor and reading this, fine, grab it and use it, I'll be on to other things and I won't post them all for you to grab. Otherwise, if you're a fellow developer - I think this will save you quite a bit of time. It took me about four weeks to perfect the first iteration used for space inventory (buildings, floors, rooms, etc) in total an 11 worksheet production. The sample is based on a simpler data structure for our inspection system since I assume we'll be getting a lot of data thrown our way (checklists, deficiencies, comment lists, that sort of thing) and I want to be prepared. Click on the graphic to zoom.
  1. Managing Director

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.