Category Archives: Database Design

Online Course – Managing Your Data with Microsoft Access

My new Udemy course, Managing Your Data with Microsoft Access is now live! Please take some time to check out the free sample videos. If you sign up early, you’ll get lifetime access to all video content and resources PLUS all future content as I continue to add to the course for the low introductory price.

One of the things I hear about Microsoft Access is that it’s simply too complicated for many people to work with. This is because Access is designed for building database applications that can be used to automate and manage processes. Too few resources approach it from that perspective. This leaves users and students without the understanding they need to effectively use the software.

You can watch a few of the lesson videos on YouTube or on
Udemy.com as a free preview of what this course has to offer.

Managing Your Data with Microsoft Access demonstrates from start to finish how to organize your data and build an application that will enable you to manage it as needed.  The course will show you the clear and logical steps involved in modeling your data, designing tables around it and then creating forms and reports based on it.

The demo database for this course is the Collier Public Library Database, a database application used to manage a fictional lending library. This application will include multiple tables, forms and reports intended to manage the processes involved in a library including  checkouts and customer registration.  The Job Search Plus application also serves as an example of a finished, distribution-ready application and what can be accomplished with Access.

Managing Your Data with Microsoft Access also includes previously published bonus content demonstrating specific tasks and concepts related to working with Access. This content will help you gain an additional perspective on some of the things that are possible with Microsoft’s premier database software.

If you’ve been wanting to learn about Microsoft Access or database design in general, this course will provide you with the start that you need. The initial course is available with 22 lessons and over 4 hours of video to guide you through the steps in creating an application with Microsoft Access. In the coming weeks, I’ll be adding more material to demonstrate more aspects of database design. Sign up now to get the low introductory price which will give you a discount on all future content!

Deploying MS-Access Table Design Changes to Remote Files

A New Challenge for Access

I designed a Microsoft Access database for one of my clients and it’s split into two files – a front-end file with all the forms, reports and code and a back-end file with the tables. That’s pretty standard.

During the development process, I needed to make adjustments to the table design – new indexes, fields need to be added and removed, relationships changed. That’s usually no problem if I have remote access to the client’s system and can login and make the changes. In this case, I don’t. Even to deploy a new program version, I have to send it to the client and let them unpack the files to their desired locations.

It’s not the ideal situation but it’s what I have to work with. When it comes to making design changes in the back-end tables, I could try to guide the client rep through the process but I’m not risking that. I’d rather have a routine that will automate it and ensure that everything is done as painlessly as possible.

(Article continues below.)

Want to learn more about Microsoft Access? Check out our online course Managing Your Data with Microsoft Access on Udemy.com. You can learn how to build and manage your own applications and work with your data more efficiently!
Continue reading →

New Video Series – Microsoft Access: The Nickel Tour

My new series of YouTube videos is an introduction to Microsoft Access for absolute beginners who have never used Access before. If you are in the position of needing to learn Access fast or are curious about what you can do with Microsoft’s desktop database software, this series will provide you with a great introduction to organization of data into related tables, creating user-friendly data entry forms and designing reports to present your data as needed.

The idea behind this series is to demonstrate Microsoft Access by showing the design of a new database application from start to finish. This includes the occasional mistakes and bugs that happen from time to time and need to be resolved. In these videos, you’ll get to see the thought process behind the design of an order entry application and learn about the different questions that need to be asked when planning a new solution that will be used by other people.

Continue reading →

How to Perform a Basic MySQL Installation in Windows

One of the nice features of MySQL as a database software is its noinstall option in which you download the files themselves and perform a manual installation that takes just under a gigabyte of space on your drive.  If you’re a developer or a student, this means you can quickly create a new self-contained instance wherever you need it and then blow it away if you need to without cluttering up your registry.

This does mean working on the command line so you’ll want to have a certain comfort level with that.  You will also need to be working with Administrative privileges.

Continue reading →

What is Referential Integrity?

One of the fundamental differences between an Excel spreadsheet and a Microsoft Access or SQL Server database is the database’s ability to group data by subject into tables and create links between that information.  This type of organization enables the user to store large amounts of data and retrieve specific information quickly by writing queries that will search on the various fields. Referential Integrity is actually a simple but essential concept to understanding how databases like Access or SQL Server work.  Once you understand it, you will be able to use these tools to better organize and report on your data.

To demonstrate this concept, I’m going to use a few tables in a Microsoft Access database.  Access is a great environment to start learning about databases as it provides a user-friendly interface and is widely available through Office365.

Watch the companion video for this article on YouTube or continue article below …

(Article continues below.)

Want to learn more about Microsoft Access? Check out our online course Managing Your Data with Microsoft Access on Udemy.com. You can learn how to build and manage your own applications and work with your data more efficiently!
Continue reading →

Performing a Basic Install of MySQL or MariaDB in Windows

One of the great things about MySQL and MariaDB are their versatility of installation. They can be setup as a Windows services and trimmed out with some extra utilities or it can be installed on a flash drive as a portable server.

The easiest installation is certainly the Windows Installer with a wizard for MySQL or the MariaDB MSI that will guide you through the installation of the server and all the options you want.  Myself, I still like self-contained programs that won’t interfere with anything else on my system and can be easily removed without leaving hidden traces. I also don’t like adding a lot of stuff to my Windows startup.

If you have a basic comfort with using the Windows command line and control panel, doing a minimal installation of either MariaDB or MySQL is not difficult once you understand a few steps. The MySQL full documentation and the MariaDB Knowedgebase are available for reference on their respective sites but here are the essentials to get you started.

Continue reading →

Calculated Fields in CiviCRM

One of CiviCRM’s strengths is the ability to add custom fields to hold specific information about your contacts. One thing it doesn’t offer (yet) is a calculated field type that will present the results of calculations of other fields.  While calculated fields are generally discouraged in relational database design, they are sometimes necessary within a user interface. One suggested method is to add custom code hooks within CiviCRM’s PHP code but as a database guy, I decided on a back-end solution.

Continue reading →

MySQL User Defined Function Example: Data Cleanup

(Updated June 9, 2016)

The Challenge

One of my current projects involves migrating a large amount of data away from an old custom Borland Paradox application into a new CiviCRM system. As with too many quickly-constructed apps, this old Paradox database wasn’t especially well designed and, among other the other challenges in salvaging the data, there were no restrictions on how dates could be entered. This means that, in multiple fields within each of the 20,000+ database records, I might see any of the following:

03-14-96
5/20/1975
10/02/83
09051975
07.19.1995
19830211121500

While CiviCRM does have a utility for importing data from CSV and other SQL tables, it was having quite a time with this collection and many of the dates were being mishandled. That’s if the records weren’t rejected entirely for other reasons. Data migration doesn’t often happen with just a few settings adjustments and a click of the Import button.

Continue reading →

Installing MySQL on Mac OS X El Capitan

A few months ago, I wrote about how to install MySQL on Windows with IIS. Now I want to show you how to create an AMP web programming stack (Apache, MySQL & PHP) on Mac OS X. As with Windows, you could just go with a ready-made development environment like MAMP but, if you’re going to be doing real database and web design work with MySQL, it’s best to know how everything works.

Background

For this demonstration, I’m going to be using Mac OS X El Capitan (v.10.11), the latest version as of this writing.  My installation environment is a hosted Mac server with 1 GB of RAM and 40 GB of disk space. I will be demonstrating the process using the Terminal commands.

Unlike Windows, OS X already includes the Apache web server and PHP language pre-installed so it’s a much simpler process to ensure that everything is working together. You will need root access in order to install and configure the components.

Continue reading →

“Microsoft Access has stopped working” – fixing corrupt files

Summary

Occasionally, when you’re making a lot of design changes to a Microsoft Access database, Access might start crashing when you’re working with that specific database file or when trying to enter the VBA environment with ALT-F11. This leaves you unable to investigate the issue or make further changes. It can be a sign that there are errors within the project’s compiled VBA code.  One solution is to use the /decompile switch to remove the compiled VBA code from the project and then recompile the source code.

"Microsoft Access has stopped working" error dialog
Like other Office applications, Microsoft Access can stop working for various reasons. Sometimes, it’s due to a kind of file corruption that’s unique to Access.

Detail

If you’re doing ongoing design work with Access, I recommend creating a new shortcut to the MSACCESS.EXE file on your system with the decompile switch. You would use this only when trying to recover a file. The rest of the time, you should use the normal menu shortcut.

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

You can also enter this with the Run command on the Start menu and include the path to your database file after the /decompile switch.

The first file that you open after running Microsoft Access with this switch will be decompiled. This will not delete the source code behind your forms or reports but it will remove the compiled version of the code and might enable you to get back into the VBA environment.

VBA Compile Database command
The Debug >> Compile Database command compiles all of the VBA code and alerts you to potentially fatal errors.

Once you’re in VBA, use the Debug >> Compile Database command to recompile the database project. This will enable you to find and correct any problems within the code.

As always, be sure to make regular backups of your files as you’re coding in case this doesn’t work and you have to recover. Also be sure to make a backup of the file before you try to decompile it in case something goes wrong.

If this doesn’t work, then the last resort is to rebuild your database by importing all of the objects within it into a new database file. This is time consuming, however, although it does seem to remove a lot of temporary data that Access stores in the file, reducing the database size substantially in some cases.

While you’re here, check out some of the other articles on Microsoft Access right here on ComeauSoftware.com.

(Article continues below.)

Want to learn more about Microsoft Access? Check out our online course Managing Your Data with Microsoft Access on Udemy.com. You can learn how to build and manage your own applications and work with your data more efficiently!