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 readingI 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 readingMy 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
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 readingJobSearch 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.
One easy way to do this is use the form’s Filter and FilterOn properties along with the KeyUp event as shown here.
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.
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 …
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.
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.
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.
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.
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.