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!

Installing MySQL and MariaDB (Videos)

Awhile back, I wrote up some instructions for doing a basic install of MySQL on Windows, including the steps to secure it. Now that I’m working on another MySQL book, I decided to start putting together some videos to go with it and I’ve created a couple on MySQL / MariaDB installation.

These videos include both the basic manual and the MSI wizard installs. While MariaDB is a drop-in replacement for MySQL and similar in most respects, there are some minor differences that have crept in, including during installation. For that reason, it’s good to be familiar with both.

The options available during the MSI installs are also pretty different. MySQL still uses MySQL Workbench for it’s GUI and while Workbench will work with MariaDB, it tends to complain about it. HeidiSQL is included as an option in the MariaDB install.

All the options are, of course, available on their respective download pages.

MySQL: https://dev.mysql.com/downloads/mysql/

MariaDB: https://downloads.mariadb.org/

Coming in 2020 - "Understanding MySQL and MariaDB"
Download a FREE sample chapter!
The ability to work with data and the systems that store it is a fundamental career skill and will remain one for many years to come. Understanding MySQL and MariaDB is an upcoming book that will guide you in learning about two of the most popular database software titles in the world today.  More than a textbook, it will combine quality instruction with online resources to help you understand the concepts you need to work with database technology and add more skills to your resume.

Check out the official book page and learn more about this new resource coming in 2020!


Designing a Standing Desk Without Spending Lots of Money

I admit it, I spend way too much time sitting. My work is mostly at the computer so that’s where I am for hours every day and that’s not counting the time in front spent watching Hulu and streaming other stuff.

Much is being written now about the dangers of prolonged sitting from increased risks of diabetes and heart disease to back and leg problems. I’ve seen the effects on my own weight over the years. I’ve been wanting to get a standing desk for awhile now but they often run in the hundreds of dollars and I’m not spending that.

Continue reading →

A Few Thoughts on the Book Writing Process

It Seems Like I’ve Been Here Before …

People seem fairly impressed when I mention that I’ve written a couple of books on databases and software. I try not to make a big deal of it, just mentioning it in passing as appropriate, because I actually don’t consider it to be a big deal. I’m one of those people who likes to share knowledge, likes to write and has accumulated a decent amount of knowledge on a few things. Occasionally, that knowledge demands to be organized and backed up in article or book form. There’s also been the hope that others will find it useful in their own lives and might actually pay for it.

Continue reading →

Recovering a Corrupted Table in Access With VBA

Recently, a client started experiencing errors, seemingly at random, in the database application I managed for them. The errors immediately started mentioning corruption and then these came through …

Error 3167 – Record is Deleted

Getting everyone out of the system and running a Compact / Repair seemed to help, but then …

“The Microsoft Access database engine cannot find a record in the table … with key matching fields() …

At this point, I decided a rebuild of the database was in order since it hadn’t been done in years. I’d just create a new database file, import everything, test and everything should be resolved.

Then the import process failed on one of the main tables with the following error …

Continue reading →

How to Shut Down Remote Instances of Microsoft Access

When you’re managing a multi-user Microsoft Access application, there are times when you need all users out of the database so that you can make changes to table structures and other items. Asking everyone to shut down is unreliable and using the Windows Task Manager to forcibly shut down instances of Microsoft Access is risky at best. Most multi-user apps should be split into separate front-end / back-end files with each user having a separate copy of the front-end so you need something that will signal all those copies to shut down and stay shut down until whatever maintenance you’re performing is complete.

In summary, my preferred way to do this is to use a settings table in the back-end database file that includes a boolean field which can be set to True to activate maintenance mode. The front-end files contain a form that stays open but hidden in the background as long as the application is open. This form uses a timer to check the value of the boolean field every 10 minutes or so. If the field is set to True, the application will notify the user, give them a minute or two to finish what they’re doing and then use the Applicaiton.Quit command to shut down automatically.

Ideally, you should still communicate with the users to let them know the database will be down for a period of time but this method helps with users who leave the app open overnight or while out to lunch, etc..

(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 →

Latest Mystery: Tracking Down the Archive

I installed The Events Calendar by Modern Tribe, Inc. on another site the other day so I could keep a community calendar of tech and business events. It seems like a pretty good WordPress plugin which is probably why it has over 700,000 installs.

Click for full size view …

It’s not perfect – the free version doesn’t handle recurring events and Gutenberg seems to break the CSS, at least on the theme that I’m using – but Gutenberg can be turned off for this plugin and it creates a very attractive calendar for the site. The events are stored as blog posts with their own specific post type so the plugin makes use of the existing tables within the database. I could write some SQL to copy events with new dates but it’s pretty much just as easy to go through the interface.

The sticking point came when I noticed that it was titling my main calendar page “Events Archive” which is not the title I want Google seeing when it looks at my site. The calendar page is auto-generated by the plugin so it’s not stored in the Pages section of the site and I couldn’t set the title manually.

After searching through Google and with a little bit of guesswork, I finally tracked the problem down to my Yoast SEO plugin. Yoast adds a new Events section under its Search Appearance >> Content Types settings for Event Tribe events and it was using the word Archive for the SEO Title of the generated page. Once I changed this, it fixed the problem on the site.

Troubleshooting MS-Access Error 31532 – Unable to export data.

Microsoft Access can sometimes seem to have its own obstinate personality, throwing errors that persist no matter what you try. I wanted to share a recent troubleshooting experience to show some of the steps that you can take when a function is just not working as expected and the decision process involved in fixing it.

Continue reading →

Things I Learned About CiviCRM This Week

I was introduced to CiviCRM a few years ago when a new customer needed a replacement for an old poorly designed database solution and needed it quickly. After getting quotes starting at $10,000 for systems supposedly designed for non-profits, I stumbled on CiviCRM which was free, open source and seemed to have a good community behind it.  The project became one of my notable success stories as I was able to discover a new product and learn enough about it to implement it in a short time.  I even managed some advanced customizing of the system.

The system has worked without any serious issues for the past few years and I hadn’t heard from the customer very often.   Then, last week, I got an e-mail stating they’d moved their offices, made some changes to their network and needed me to come in and get the software working again. Everything went smoothly for the first 10 minutes I was there. Then I realized that CiviCRM was running incredibly slow and its top menu had disappeared which pretty much makes it unusable for anyone who doesn’t want to type in a lot of complicated URLs.  Again, it had been quite a while since I’d touched CiviCRM or dealt with the specifics of their system so I ended having do do a bit of research to find out what was going on.

In the end, the solution came down to updating the civicrm.settings.php file with the new network information. Once that was done, the system worked great but I ended up taking the long way around to discover it. Along the way, I refreshed my knowledge of CiviCRM and learned a few new things as well. This was just as well as I was able to be even more confident that no other problems lurking in their system when I was done.

Continue reading →

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 →