Category Archives: Microsoft Access

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.

Adding Passswords to Microsoft Access Switchboard Options

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

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

(Originally published on AndrewComeau.com. Republished here by popular demand.)

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. It’s only the beginning, however, and if you want to get serious about programming, it’s important to expand your skillset 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 executables to websites powered by ASP.NET programming. 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

What You Need to Know About Database Technology

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.

Continue reading

Removing Duplicate Records in Microsoft Access

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.

Read the full article here on Drewslair.com.

Data Analysis with Microsoft Access

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.

Read the full article here on Drewslair.com.

 

Microsoft Access for Beginners / JobSearch 2010

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.

Bringing things up to date …

Every so often, I remember that I have a blog attached  to the site and I make new promises to myself to keep it up to date but it doesn’t seem to happen that often.

2011 was a busy year for me which was one reason I didn’t update the blog much.  It’s easier to post quick thoughts to Facebook than deal with the expectations of a blog entry.  Those updates tend to get lost in the ether, though, and if I’m going to spend time and effort writing interesting things, I’d rather use them for my own promotion than Facebook’s.

After working independently for an extended period, last year started with a new full-time job that looked like an interesting opportunity.  I did manage to gain some extra experience with ASP.NET while acting as the sole developer on an online solution for the company and consulting on other applications.  The position was not without its drawbacks, however, and at the end of September I had my first experience of anticipating exactly when the axe was going to fall.  When I left for the day one Thursday, I made sure to clean out my desk and sure enough, the layoff came early the next day.

Microsoft Access for BeginnersI hit the ground running, though. By the end of October, in addition to updating my resume and other job search fun, I’d completed the project to turn the Microsoft Access for Beginners series into an eBook. On October 31, it went live on Amazon.com as my first Kindle offering and I’ve started to see some sales. I probably should have added a dedication to Starbucks where I spent so many hours staying caffeinated while editing material and figuring out how to get around formatting issues.

I decided to see how the book plays on Amazon.com before reformatting for other outlets and I was a little sick of looking at the project so I moved on to other things including setting up a Facebook page for Drewslair.com.  After sending out some more resumes, my phone started ringing off the hook and by the beginning of December, I’d landed a new contract as a programmer at a local company.  I actually managed to get two job offers within 24 hours which was a new experience for me.  The other one would have meant moving to Jacksonville and I decided  to stay local.

So, since December, I’ve been gaining a lot more experience developing ASP.NET web applications with Visual Studio 2010 and SQL Server 2008.  Having a regular work schedule again where I could leave the job behind at the end of the day was nice for awhile but I’ve also found it’s inherently limiting. I need to start accomplishing things on my own again so I’m looking at dusting off some of those  projects that I put on hold to accommodate the day job and seeing what I can do with them.

More updates here would probably be a really good start …