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 …
(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 →
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.
(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 →
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.
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.
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.
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!
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.
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.
(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 →
(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.
In the modern world, our daily activities follow and leave a rich trail of electronic data from e-mail and text messages to credit card transactions and medical records. At its simplest, the data could be an address book or contact list stored in a text file while more complex information such as a store’s inventory and customer information might take up terabytes of space on network servers and require full-time administrators to maintain it. Most of this data is stored in electronic databases of one kind or another where it can be searched, sorted and easily retrieved as needed.
After a basic understanding of how to work with computers, knowing the basics of how data is stored and manipulated is an important part of being technically savvy in today’s world. Whether you work with large amounts of data and need to organize it better or you need to communicate with the people who do, the better you understand the technology, the more effectively you’ll be able to face the daily challenges that come from living in a data-driven world.
One of the strengths of Microsoft Access is its ability to import and link to data from different sources. With Access you can also query and report on the data in ways that a spreadsheet program like Excel doesn’t readily provide. This can come in handy as part of a database application but it’s also useful for quick analysis of new information.
I found a new example of this the other day when I was looking over the website statistics for my site. This data is stored in log files which record every incoming request to the site. Every time someone pulls up a page, like the one you’re reading now, their web browser generates a number of file requests that are stored in these logs.
This adds up to thousands of requests per day, each one being stored as a separate line in the log file. The log stores such basic information as the time and date of the request, the file name, the IP address of the computer making the request and the type of browser being used. This information comes in very handy for analyzing the site traffic. My hosting service provides analysis software that summarizes these logs but it’s not customizable, I don’t fully trust its analysis and it doesn’t always answer the questions that I have like the one that came up the other day;
“Why are my visitors getting so many 404 errors??”
For those who don’t know, 404 is the error code you get when you try to pull up a page that doesn’t exist. The analysis software was telling me I had hundreds of these results which is worrisome because they’re not very attractive to visitors and can indicate problems with the site. The statistics didn’t tell me what page requests were generating the errors, however.
I decided to look at the logs directly and see what I could find out. I figured if the analysis software could read it, I could find a way to work with it. The initial view was not encouraging.
It only looks like it’s from NASA …
After I looked at the file for a few moments, and turned off the word-wrap, I saw that it was a delimited text file. This is a text file that contains the same fields on each line and where each field is separated by a specific character. That character could be a comma, a space or another character so long as it doesn’t show up within any of the fields that are presented. In this case, the character is a blank space. Every time a blank space occurs, it means one field has ended and the next one is beginning.
Okay, that’s a little clearer.
Another type of text data file is a Fixed Width file where each field is allocated a certain number of characters on the line. The fields do not need to be separated although if the value for a field exceeds the allocated width, it might be cut off. If a field value falls short of its specified width, the remaining space may be filled by spaces or other characters. Either way, this type of file is read by knowing the list of fields and their character allotments.
Now I knew it was just a matter of importing the data somewhere and manipulating it as needed.
This Looks Like a Job for ACCESS!
I was working with Access 2002 for this project as shown by the screenshots. The Import Wizard is also present in Access 2007 and is virtually identical to the one shown here. This is not something that has changed a lot between Access versions.
Start with the Get External Data menu (Access 2002)
In Access 2007, the Import Wizard can be accessed from the External Data portion of the control ribbon.
Access provides a handy wizard that will guide you through the import process for a data file. It will import a variety of file types including other spreadsheet and database files. For this exercise, I just wanted to import the text file I’d saved the log contents to so I selected the text file in the Open File dialog that the wizard presented.
The wizard immediately recognizes the file contents as you can see below. The first screen presents a choice between the two file types, Delimited and Fixed Width.
The wizard makes its best guesses but, in the end, depends on the user to pick the right settings.
Clicking on the Advanced button brings up the Import Specification screen which provides additional settings that will help Access read the data. The below screenshot shows the settings I chose for the file. It’s a delimited file using a blank space for a field delimiter. From the above screenshots of the file, you can see the dates are shown with the four-digit year first and then the month and day and dashes in between. The months and days use leading zeroes (i.e. 2010-08-31). The timestamps use the colon to separate the hour and minute.
I could use the Text Qualifier selection to specify if quotations are marked with single or double quotation marks but I don’t need it here.
Under the field information, I could even specify the name, type and other information for each field. This would be useful as part of a regular import if the data was going to be referenced by an application or if Access had trouble with part of the data but this is a one-time import and Access seems to have figured out the fields okay so I’m not bothering with that.
(Article continues after ad)
(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!
Using the command buttons on the right, you can even save and load specification settings for reuse.
The second screen of the wizard specifies the delimiter character that’s being used although I already did that on the Advanced Settings screen. This screen also presents the option to use the first row of the data file as a set of fields names which is the case with this file.
The wizard can store the data in either a new or existing able. Using an existing table could be useful if you had a number of files to import and wanted to combine them all in a single table or if you were importing similar data from other database.
The next screen is another chance to manually define the fields as was earlier shown on the Advanced Settings screen. You can also mark fields to be skipped in the import.
Access specializes in relational data so, unlike Excel, it wants to add a primary key to any tables that are created. This key acts as a unique identifier for each row in the table and can be used to link the table to other tables in the database. On the next screen of the wizard, you are prompted to set the table key.
If you let Access add a primary key, the program will add an AutoNumber field to your table which will increment by 1 for each row in the table.
With the second option, you can choose an existing field within the table that provides a unique value. This will be used as the identifier.
The third option skips the primary key altogether.
The final screen in the wizard simply asks for a table name if the data is to be imported to a new table.
Once the import is completed, the wizard notifies you of the results. As you can see from the above message, things don’t always go perfectly. There were thousands of records in the log file I imported so that’s not a surprise. The data was still imported but not quite as it exists in the file. The wizard also creates an ImportErrors table with details on what went wrong.
Out of 3783 rows imported, it seems that 153 of the rows had at least one field that was too long for the field type selected by Access. I could start over and specify a memo type for the field but I’m not concerned with the that particular field right now so I won’t bother.
For my purposes, I call that a successful import. Now let’s look at the data.
Under the Microscope
Once the import wizard has put the data into a nice neat table, all that’s left is to query it at will. Here’s the one that I used to find out where all the 404 errors were coming from.
After looking at the file, I was able to figure out that the cs-uri-stem field is the name of the file that’s being requested, whether it’s a page, a graphic or any other type of file.
The cs(Referer) field is used to hold the page from which the visitor followed a link to the requested page. This could be a page on another site or a page on this site. If the visitor typed the address of the page in manually (i.e. “http://www.drewslair.com/about.shtml”), the field is blank.
The sc-status field shows the status of the request. If the file was retrieved successfully, as in most cases, it should be 200. I’m specifying “404” as the criteria because those are the only rows I want to see.
The results paid off and told me about a few things that were going on with the site.
A problem with a relative URL reference caused one of the page borders to look to the wrong directory for the cascading style sheet. Knowing that all the errors were coming from pages in one directory helped me to track this one down. Fortunately, it probably wasn’t noticeable to visitors.
Many browsers are looking for the favicon.ico file. That’s the file that supplies that little icon you see for so many websites. It shows up in your Favorites or Bookmarks menu and on the title bar of your browser when you’re viewing the site. Drewslair.com doesn’t have one. It probably should but it’s not a priority.
I’ve republished a few articles elsewhere on the web and these articles link back to graphics and other files from the site. Some of those files have changed names since the Knols were published. Updating the Knols fixed the problem.
Then there are the external sites linking to files that have long since been moved or deleted or just getting the address wrong altogether. There will always be a need for a 404 page.
Having fixed the problems that I could, I’ll keep an eye on the stats for a few days and look forward to seeing the number of 404 errors go down. In addition to tracking down this issue, it’s turning out to be a quick way to check the popularity of specific pages and downloads with confidence in the results.
Filling in the Gaps
This is not the first time I’ve used Microsoft Access to analyze data in a way the original software couldn’t. It’s a great tool for both rapid development and quick analysis of many types of data. Many more people have Excel on their desktops which also does a great job of manipulating external data but if you have Access at your disposal, it’s worth your time to learn as much as you can about it beyond it’s native data format.
Occasionally, you might have a table in Access that contains duplicate records. Maybe it was imported from one or more sources or perhaps it was not setup with the necessary key to ensure unique records. Whatever the reason, now you want to make sure that the records in the table are unique. Fortunately, Access offers a couple of options that you can use to remove the duplicates from your table quickly and easily. This article will explain two methods for removing duplicate information from tables.
One of the strengths of Access is its ability to import and link to data from different sources. Excel does this too but with Access you can go on to query and report on the data in ways that Excel doesn’t readily provide. This can come in handy as part of an application but it’s also useful for quick analysis of new information.
I’ve started to see some sales of the eBook version of “Microsoft Access for Beginners” and was happy to hear from one of the readers today, despite the fact that he was reporting a problem …
It seems there was an issue with the Access 2007 / 2010 version of the program that I believe had to do with differing installations of Access 2007. I’ve corrected the problem and the corrected version is available for download.
Whether or not you’ve bought the book, JobSearch 2010 is definitely worth a look. This is a free program designed with Access 2007 that helps to organize your job search by enabling you to store complete information on every job lead in one place, track the progress of individual leads and follow-up on opportunities more effectively. If you don’t have Microsoft Access, there’s a version that includes the Access 2007 run-time, a free version of Microsoft Access that will enable you to run the program and change data while preventing any design changes to the program.
For more information, check out the download page where you’ll find links to all the versions available and a program manual to help you get started.
Do you need help with Microsoft Access?
Comeau Software Solutions specializes in the development and enhancement of Microsoft Access applications. We can also rescue Access projects that have gone off-track and help you decide when it's time to move to another solution. Please contact us for more information on how we can help you with your database needs.
Also, check out our online course Managing Your Data with Microsoft Access. You can learn how to build and manage your own applications and work with your data more efficiently!
