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..

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.

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 →

Unboxing the Unitek IDE / SATA to USB 3.0 Drive Adapter

I hoped it would be a simple fix when the customer asked me to look at her Windows 7 machine this week. I don’t really do PC repair anymore but I have one or two old customers I’ll help out when needed. I figured it was just some Windows settings that needed adjustment but after struggling with the machine for about an hour, I realized that it really needed a wipe and reload of the system. The repair shop I referred the customer to discovered hard drive issues I’d suspected but couldn’t confirm with CHKDSK. They gave her a fantastic rate on a hard drive upgrade and Windows reinstall and returned her old hard drive to her for recovery of whatever data she needed.

Continue reading →

Unboxing the ASUS Chromebook

This past weekend, I finally decided to spring for a Chromebook. I’ve been thinking more and more about a Windows replacement machine, at least for basic tasks. I also wanted an inexpensive solution that would extend the life of my current laptop. I often don’t need the full machine out on the road and the smaller profile of the Chromebook makes an attractive alternative.

The Specs

Continue reading →

Helping Users Work With Subforms in Microsoft Access

Creating an Access application for other people means anticipating how the users are going to interact with your program. Without clear direction from the program or you, users can be easily confused by even simple things like the order in which data is to be entered.

One of the basic tools in Access is the subform which allows for the input of records related to a master record. A classic example is a database which stores customer orders and where each order might have multiple items (i.e. order lines) associated with it.

Continue reading →