Category Archives: Microsoft Access

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!

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 →

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 →

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 →

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 →

How to Programmatically Relink Tables in Microsoft Access / VBA

Several years ago, I designed a Microsoft Access application called JobSearch Plus for managing job leads during an employment search.  It was a split application, meaning that the tables were stored in one Access file, the back-end, and everything else was in the front-end file with table links to the other one.

JobSearch Plus was also meant for distribution and I had no idea where any of the users were going to store the files on their computers.  Access uses absolute file paths to specify the source database for a linked table. This meant I needed some way to determine if the back-end existed and what it’s file path was.

On it’s own, relinking Access tables in VBA is just a few lines of code.

 Dim dbCurr As Database
 Dim tdfTableLink As TableDef
 
 For Each tdfTableLink In dbCurr.TableDefs
   tdfTableLink.Connect = ";DATABASE=" & (Insert new file path)
   tdfTableLink.RefreshLink
 Next

This is fine if you know exactly where the new file is but my program could be anywhere on the user’s computer so I had to do some more engineering.  Also, since this was going out over the Internet to who knows how many strangers with my name on it, it had to work right … always.  This is where the fun of actual software development comes in.

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

How To Create a Dynamic Search Filter on a Microsoft Access Form

Best practice when retrieving data in Microsoft Access or any database, for that matter, is to limit the amount of data that you pull at any one time. This means using the proper filters and limiting the number of fields retrieved.  Often, you still end up with a lot of information to look through anyway. That’s when it can help to have a dynamic search filter that will enable you to easily search the records with just a few keystrokes.

One easy way to do this is use the form’s Filter and FilterOn properties along with the KeyUp event as shown here.

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

How to Dynamically Set the Report RecordSource in Microsoft Access

One of the steps for creating a report in Microsoft Access is to define where the data is coming from.  This is done through the report’s Record Source property which can be a table or standalone query in the database or a SQL statement as shown in the screenshot below.

Usually, this is defined when the report is created and only gets changed if needed.  In some cases, however, you might want to use the same report object for many different queries.  You could have a mailing list report that you want to use for employees, customers or other groups at different times and the data would be drawn from different tables or even different databases.

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