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 a couple of years ago, 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, A 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.
Access Developer's Tools - from Informed Software
Layout Assistant (Sample Database and 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.
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 Here to download sample Layout Assistant database
Data Mover (Sample Database and 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 allow any way to move attachments directly from one table to another with standard append queries.
This code solves the problems and fixes the issue.
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 supplies 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.
Other functionality and demonstrations:
In order to determine the names of the fields in the source table, we create a table of field definitions (modified from code supplied and accredited in our code).
Clicking the Move Data button in the design tool above moves data from the source table (shown on the left) to the Target Table (shown on the right and populated in the lower graphic).
Back-End Field Upgrading (Sample Data Migration Database and 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.
Below are some links you may find useful:
- Sagekey.com: I've been working with them for years - they have off-the-shelf tools for packaging an installation as well as doing custom development work. I recommend them highly.
- 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 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.
Excel Automation (Sample 2-Way Communications Database):
.We deal with very 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.
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.
Click Here to download the sample Access 2010 accdb file
The purpose of this sample code is to show how a simple text file can be used to license permissions for users. The text file (LicReg.txt) is set up using a hash of the hard drive’s serial number in conjunction with a spreadsheet to provide a unique license number that takes into account two developer options, the expiration date, and hard drive serial.
The sample is a stripped-down version of iSpec, our inspection software used in conjunction with our core facility management software. Inspections are collected with this application running on a tablet. The Save/Refresh button will synchronize the data to the core (datafile.accdb) in two-way fashion. Inspection records are transmitted to the core, and if this is successful, then the user is offered the ability to do a complete refresh of all data on the tablet (buildings, floors, rooms, employees, work assignments, etc.). This demonstrates that process including the ability to move attachment field items (graphics, used to show inspection problems via the tablet’s camera) from one table to another.
There are many other functions demonstrated as well, including:
1.All-code treeview control on the home page which was developed by and available from here: http://www.jkp-ads.com/articles/treeview.asp This general-purpose control is used to generate a three-level tree allowing one to pick a room to inspect. 2.Capturing a picture from the tablet’s camera and appending to a record (an inspection record). 3.A simple messaging system internal to the application. 4.A PDF help system (table helptblfrontend and the various (?) help button’s on_click events. Click Here to download the sample Access 2013 accdb file zipped with documentation
Copyright 2017: Informed Corporation, Inc. Informed Environmental Services Management Software. All Rights Reserved.
Ankeny, IA 50021