18.1. Opening Up Your Database to the World
When you decide to share your data, the first decision you need to make is whether or not other people need to change any of the information. As you'll see, passing copies of your database around is easy. But getting people to work on the same database file at the same time is a trickier proposition.
Overall, you can take four basic approaches to get your data out to the masses:
Export your data . You can take the data in your database and export it to another format (like an HTML Web page or an Excel spreadsheet). This is the way to go if the people who need to see your data don't have Access. You'll learn how to export data in Chapter 19.
Copy your database . You can give others a copy of your database. For example, if Uncle Earl has a copy of Access, you can email him your list of family addresses. The limitation with this approach is that there's no easy way to sync up the changes in different copies. If Uncle Earl adds a few new people to your database, your original remains the same. If you change the original, Uncle Earl's copy is out of date.
Switch to a server software . You can move your data to an industrial-strength sever product like SQL Server or SharePoint. Once you do, a virtually unlimited number of people will be able to get at the data. Each person uses his or her own copy of Access to connect to the server that has the central repository of information. The obvious drawback to this approach is complexitysetting up either of these products is an ambitious task even for technically savvy people. You'll try two of the best options in Chapter 20 (SQL Server) and Chapter 21 (SharePoint).
Use the multiuser features in Access . You can place your database in a shared location (like a network drive) so several people can use it simultaneously . This way, everyone's working with the same set of data (and Uncle Earl's changes don't get lost). With this method, Access has to keep everyone's work coordinated. If your group is smallsay, with no more than 40 people using your database at oncemultiuser sharing should work. But if your group is large, Access isn't the best choice. Instead, you need a server product that's built from the ground up for high volume, multiuser access. (The next section gives you a handy checklist to help you decide if this approach is for you.)
This chapter focuses exclusively on the last item in the listthe multiuser support that's hardwired into Access. But before you get started, it's important to understand the limits you'll face so you can assess whether or not Access sharing can meet your needs.
18.1.1. How Access Sharing Works
The database sharing features in Access are easy to understand. First, you place your database file in a location where everyone can get to itlike a folder you're sharing on your computer, or (better yet) a spot on a server computer on your company network. Now, anyone who wants to use the database simply needs to open the database file.
Sounds easy, right? Not so fast. Before you rearrange your entire company around a single database file, you need to crunch a few numbers . Here are some indications that suggest Access sharing will work for you:
No more than 40 people use the database at once . The key part is how many people are using the database at once . You're free to share the same database with hundreds of users, so long as they don't all open it at the same time.
Note: This number (40) is a sensible recommendation, not a set-in-stone rule. Some Access gurus have designed databases that can withstand 90 to 100 simultaneous users. However, without some serious (and complex) optimizations, you're likely to hit a brick wall much sooner.
No more than 15 people change the database at the same time . Reading the database is easy, but updating it presents some serious challenges. One obvious problem occurs if more than one person tries to change the same record at the same time, but in different ways. And because of the way Access is designed, even changes that shouldn't clash with one another can slow down your overall performance. You'll tackle this issue later in this chapter when you consider locks (Section 18.3.3).
Note: This number (15) is just a conservative guideline. If different people are making changes in completely different tables, you may be able to squeeze in more updates at once. Conversely, if everyone wants to change the same few records, you may run into trouble even earlier. If in doubt, try it out.
The structure of the database changes infrequently . In other words, you don't expect to regularly redesign your tables, add new fields, or tweak relationships. Ideally, you'll perfect all your tables before you share the database. And for best results, only one person should have the role of Chief Table Designer and be responsible for changing the database structure when needed.
Different people tend to work with different tables . If everyone who uses the database is performing the same task (and accessing the same table), you've got a problem. But if one person maintains the product catalog, five more enter orders, and another six log shipments, you're in a much better situation. Even though everyone's using the same database at once, their work doesn't overlap.
Your database isn't mission-critical . Data is always important. But if you're running an e-commerce company with a Web site that's live 24 hours a day, you can't afford even a momentary glitch. Unfortunately, Access can't guarantee that kind of stability. Although it's rare, a sudden network problem or a computer failure that happens while someone's in the middle of making a change could conceivably damage your database.
Note: All Access fans should perform regular database backups throughout the day. You can use a scheduling tool (like Windows Task Scheduler) to automate this process.
It's no exaggeration to say that shared Access databases are the backbone of many small companies. But if you've reviewed the limitations of Access sharing and decided that Access can't fill your needs, it's time to step up to a more powerful product like SQL Server. (Don't panica free version is available, and you can manage your data in the familiar Access interface.) You'll learn about SQL Server in Chapter 20 and SharePoint Server in Chapter 21.
On the other hand, if Access does fit your needs, congratulationsyou're moments away from transforming your lonely , single-person database into a resource your entire company can use. Just read on.