Category Archives: Microsoft Access

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 →

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.

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.

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.

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.

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 …

Continue reading →