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

Coming in October – MySQL Explained (updated)

MysqlMy newest book, MySQL Explained, is due to be published this month! You can check out the LeanPub page to show your support and to sign up for notifications of when it’s published.  See the description of the book below.

UPDATE 10/24/2015 – MySQL Explained is now available in both EPUB and MOBI (Kindle) formats with the first chapter available for FREE from LeanPub.com and Amazon.com! Read more about this guide to the leading database for online applications and on the official page!

About the Book

MySQL Explained is a step-by-step tutorial for everyone who’s ready to learn about the database software most commonly used for storing information behind some of today’s most popular websites and online applications.

Written especially for people outside the I.T. world, MySQL Explained provides the background information you need to get familiar with database theory and the principles behind organizing data. This book starts from the ground up, helping the reader to understand the very definition of a database, the forms it can take and the different options for storing information. By the end of this book, you’ll understand the reasons for choosing MySQL, the options for installing it and the tools that it offers to store and safeguard your data.

If you are in any way involved in designing or managing a website or data solution of any kind, you owe it to yourself to understand the tools involved. Quality database management sytems are essential in today’s data-driven world and such essential tools should not be a mystery to those who depend on them. MySQL Explained can help you unravel the mystery and learn more about a technology that will be around for a long time to come.

MySQL Explained is published through OS Training which, in addition to publishing tutorials like this one, offers onsite and online training in web technologies including Joomla, Drupal and WordPress.

A Complete Guide to Installing MySQL and PHP with IIS

One of my latest projects was setting up WordPress, the popular content management system, on a client’s web server which was running Windows Server 2008. WordPress requires installations of MySQL for the database back-end and the PHP scripting language in order to serve up the WordPress content. On a local Windows machine, I’d probably just use a pre-configured WAMP (Windows, Apache, MySQL, PHP) stack package like EasyPHP which is installed quickly and includes all the necessary components. That’s not quite an option in a professional environment, though. I was also working with Internet Information Services (IIS) 7.5 for the web server instead of Apache so the process is a little more involved.

Continue reading

New Kindle Fire for $49.99!

The Kindle Fire goes on sale for $49.99 on Sept. 30, 2015.

The Kindle Fire goes on sale for $49.99 on Sept. 30, 2015.

I don’t usually do blatant advertising in these posts but I have to get the word out about this one. On September 30, 2015, Amazon.com will be selling the 7″ Kindle Fire with 8GB of memory and Wi-Fi for only $49.99. That’s a fantastic price for a great product. I’ve had mine for a few months now and love. it.  In addition to reading books, I can watch Netflix on it, surf the web, listen to music and take pictures.  I’ve been amazed by the video and audio quality on it and the camera is better than the one on my smartphone.

If you’ve been thinking about getting a tablet, I would definitely recommend the Kindle Fire. With webmail and all the free apps available through Amazon’s store, I can do most everyday tasks on my Kindle Fire. The voice recognition is also excellent so it’s easy to dictate e-mails and updates.

At $49.99, the Kindle Fire is something you definitely have to consider when buying a new tablet.

(In the interest of full-disclosure, the links in this post are affiliate links.)

Job Opportunity – Systems Administrator

(This post is re-published from OcalaITPros.com.)

CareerSource Citrus / Levy / Marion is looking for a Systems Administrator to provide support for their offices in Citrus, Levy and Marion Counties. The job description below was provided by the agency. If you or someone you know is qualified for this position and interested in applying, please apply through EmployFlorida.com, referencing job order # 11078489. (Veterans Preference)

Continue reading

Upcoming Hiring Events – Ocala / Marion County, August 14, 2015

I’ve received the following announcements from CareerSource for Citrus / Levy / Marion counties of upcoming hiring events and job fairs in Ocala / Marion County and surrounding areas. See the individual announcements for full details and contact information.

  • Labor Ready, Golf Course Maintenance positions in Citrus County, Florida – Tuesday, August 18, 2015, 8 – 11 a.m.
  • ResourceMFG Production Associate positions in Ocala, FL, Tuesday, August 18, 2015, 10 a.m. – Noon.
  • Sonic Drive-In Hiring Event, Multiple positions open in Ocala, FL – Tuesday, August 18, 2015, 9 a.m. – Noon
  • Tri County Job Fair 2015, Chiefland, FL, Wednesday, August 26, 2015, 10 a.m. to 3 p.m.

Continue reading

Portable USB Chargers – Simple, Inexpensive and Useful Accessories

51cSWdgxMHL._SL1000_In our new world of rechargeable devices and constant connection, more battery power is never a bad thing. Seeing a battery indicator at 5% and a device begging to be recharged sends many people into a scramble looking for the nearest convenient charging solution. When there’s no outlet available or when you don’t want to leave your device tethered to a wall for the next hour, miniature USB chargers can be a lifesaver whether you’re on the road trying to summon assistance on your phone or just trying to buy some more time on your tablet at a crowded conference.

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).