Category Archives: Microsoft Access

Helping Users Work With Subforms in Microsoft Access

Leave a reply

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

Leave a reply
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
 End If

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

5 Replies
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

Leave a reply
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?

Leave a reply
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 →

Splitting a Database in Microsoft Access

Microsoft Access is unique among all of the Microsoft Office applications in that it can be used to create entire applications that contain large storehouses of data, versatile data entry forms and sophisticated reports to present the data in a variety of ways. It also goes beyond other applications such as Microsoft Excel in that a single database is able to store and organize large amounts of data from different sources and on different subjects while enabling many users to easily find and work with that data as needed. While an Excel spreadsheet is excellent for analysis and can provide access to multiple users, Access takes it a few steps further and enables users to store and manage volumes of data and present it in many different ways based on the needs of the data users and the intended distribution.

Why Split a Database?

The multi-user environment provides its own challenges. Access is often used as an ad hoc development tool by people outside the I.T. department to design needed solutions that would otherwise have to be purchased for large amounts of money. These applications can end up being used by many people across a company and that means there can be many people accessing the same database file at the same time. If enough people are in the file at once, the performance of the application can degrade pretty quickly. Also, when one person has the database file open, the file is then read-only for anyone else who opens it which means that they will be unable to create or edit their own reports or database queries.

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.

Adding Passswords to Microsoft Access Switchboard Options

1 Reply
The Switchboard Manager in Microsoft Access makes it easy to create a system of menus that will enable your users to navigate through your application. It also relieves you of the need to manage multiple menu forms and macro calls.

Microsoft Access switchboard form

Figure 1 – Example Access Switchboard

Sometimes, however, you might want to keep all but a few users out of specific areas, even if it’s just a password to discourage the curious. This is one thing the Switchboard Manager doesn’t offer and I’d like to show you one method for achieving this here. I want to stress that this method is not truly secure.  On it’s own, it won’t prevent a user from hitting F11 and browsing through the database. A power user who wants to see those areas could defeat this method pretty quickly. It will keep the merely curious and inexperienced users out, however, and could be combined with other measures to add more security.

Continue reading →

Microsoft Access for Beginners – FREE for download

Leave a reply
Just as a reminder, my first book, Microsoft Access for Beginners, is still available as a free download in PDF format from Scribd.com. This book covers versions of Access through 2010 and I’m not planning on updating it further at this time but much of the information is still applicable to the current version of Access. If you’ve been wanting to get an introduction to Microsoft’s desktop database software, this is a great place to start. You’ll learn the principles of organizing data into tables, writing queries on those tables and creating the forms and reports to work with your data as needed. If you’re interested general database principles in addition to Access, please see my other book, Your First Guide to Database Design which is available from both Amazon.com (Kindle edition) and Scribd.com (PDF).

Using Visual Studio to Manage Your Data

(This article was updated in August 2018 for use with Visual Studio 2017.)

Moving Beyond Microsoft Access

I’ve written a lot about Microsoft Access over the years and still believe it’s a great training ground for people who want to learn to design database applications. Its user-friendly interface provides an easy learning curve and introduction to the basics of relational databases, data entry forms and report design. If you want to get serious about programming, however, it’s important to expand your skillset beyond Access with tools that are in demand by potential employers and customers.

One of these tools is Microsoft Visual Studio, the development suite that provides access to the .NET family of languages including C# and VB.NET. With Visual Studio, you can create a variety of professional applications from Windows form applications to websites powered by ASP.NET. While Microsoft Access provides some impressive tools for the office power user and even some full-time programmers, Visual Studio is the next step up the development ladder. It enables professional programmers to design any type of solution without being tied to a Microsoft Office installation and without the limitations of the Windows desktop. A couple quick searches of sites like CareerBuilder.com will show you the kind of jobs and salaries that knowledge of Visual Studio can lead to as opposed to Microsoft Access.

Continue reading →