Visual Basic Expert Solutions

book coverQUE

Chapter 19

Networkable Applications

By Joseph Armitage


Why is writing a networked application different than writing a single desktop application? All you need to do is to find some place that everybody can get to your application and put it out there for everybody to use, right? Well, it's not quite that simple. You have a lot of flexibility when writing a desktop application; you have only one user to be concerned about, and relatively speaking, the interaction between the user and his data is fairly simple.

In a networked application you have a conflicting set of requirements. You want your application to appear to perform as fast as and be as easy to use as a desktop application, while ensuring that the actions of one user do not interfere with the work of the other users. This balancing of ease of use and providing a stable and secure environment requires a number of important decisions to be made and trade-offs to be considered.

Let's take a look at some of the issues that we will have to deal with to take a simple desktop application and turn it into a robust networked application. In this chapter you learn how to do the following:

Understanding Design Characteristics of Networkable Applications

The overriding design characteristic of a networked application versus a single-user application is robustness. A networked application should be bullet proofed by having thorough and extensive error-checking. This is the most important concept for a programmer new to network programming. In some standalone applications, you may be able to skimp on error-checking in the interests of performance by making some assumptions about "the user will never do that." In a networked application some can and will "do that." The actions of one user must not be allowed to negatively affect the work of another user.

Now we'll further examine each feature in the following sections.

Ensuring Data Consistency

In a multi-user system, database updates from one user may be interspersed with updates from one or more other users. The goal of a multi-user system is to have the database look as if those transactions had all been executed in a serial fashion, one after the other. If a networked application is not designed correctly, a number of problems can occur that leave the database in an inconsistent state.

For example, see what happens when you try running two separate copies of the ADDRESS application on the accompanying disk as follows.

1. Run the ADDRESS.EXE program from the enclosed disk in directory ADDRESS. The dialog box in figure 19.1 appears.

Fig. 19.1The Montare Contact Manager is a very useful tracking tool that can be used on a network.

1. Now start a second copy of ADDRESS.EXE. The same record appears.

2. Change the name field from Joe Armitage to John Bradford and click the Data control to update the record.

3. Switch back to the first version of ADDRESS and change the city to Austin. This version of the data still shows Joe Armitage. Now try updating the database from this version of the application. How will the final record be stored in the database? Will John Bradford be in Dallas or will Joe Armitage be in Austin?

This is a failure to communicate, not only between the two users, but also between the two copies of the application. The way to prevent conflicts like this is to ensure that only one user at a time can change data. This requires a data locking strategy. When data is locked, any number of users can read it, but only one user can make changes to it. Data locks ensure that updates to the data are not lost and the database remains in a consistent state. We will discuss implementing an appropriate locking scheme in the sections on database, recordset and page-level locking later in this chapter.

Allowing Concurrent Access to the Data

If you want to prevent any chance of conflicting updates, you could specify that you wanted exclusive access to the database. This is essentially what happens when you open a database in a standalone application. However, no other user can even open this database, let alone use any of the records. While this is desirable for a desktop application because it provides fast access it is not practical for a networked application because everyone else is locked out.

To complicate matters even more, in Visual Basic you can control how a database is opened, but outside of your application, users can open a database exclusively using the Open Database command and prevent your users from accessing the database. For example, in Microsoft Access the open database dialog box defaults to Exclusive access.

Most users won't even see the check mark in the exclusive box; if they do, they know that they will get faster access if they leave it selected. So in addition to limiting how users can open your database in your application, you need to have database software that can prevent the database from being monopolized by someone using the database software to access the data. An error dialog box (see fig. 19.2) displays when you try to access the MONTARE database using the ADDRESS2 application when it is already opened for exclusive access using Microsoft Access.

Fig. 19.2 This Access error dialog box informs the user that someone else is already using the database so it can't be opened for exclusive access.

The application continues to execute and shows a blank data-entry screen where the fields from the database should be. In an actual application, you would trap this error and end the application in a more graceful manner. You'll see how to do this in the sections on error trapping for database, page and recordset locking.

A networked application must ensure an appropriate balance between allowing the maximum number of users possible to have concurrent access to the database and still maintain the integrity of that data. If there is contention between users, then the application should provide a graceful way to wait and retry to access the data, and give the user the option to exit and retry at a less busy time.

Security and Backing Up

In standalone applications, you may tend to concentrate on the application's features and not so much on protecting data from unauthorized access. Security in a desktop application is usually either limited to being able to boot the PC containing the application or to a sign on password for the database or application itself. Once you are signed on you have carte blanche to create, update, delete records. You can even delete the entire database if you so wish.

Networked applications are more likely to contain information that's critical to you or your users, therefore protecting that information is an important part of application development. In a networked application you need to make decisions about just exactly who can access this application and the data it uses. By securing your application, you can control what a user, or a group of users, can do with the objects and data in your application. For example, you can specify that a user, or group of users, can view but not change the database.

The amount of data entry/update is typically higher in networked applications, simply because there are more people accessing the application. For many database applications, controlling and managing who can view information and who can change information can be as important as creating the application. Protecting the application itself from being changed or copied may also be a concern. In order to handle this you need a security strategy and your network and database software must be capable of implementing this strategy.

Along with a security strategy, regular backups of your data become more important because you are dealing with critical data. Backups now have to be scheduled at a time when no one else is accessing the database. Compacting the database (purging deleted records) requires exclusive access to the database, so this is usually scheduled at off hours.

Performance and Tuning

Speeding up performance of a desktop application usually means using a machine with a faster CPU, adding more RAM or a faster and bigger hard drive. You can essentially throw resources at the problem until it goes away.

The Microsoft Jet engine has a stated requirement of 6 megabytes of RAM to run. In reality this is closer to 8, especially if you are using add-ins, OLE, or interfacing with other products such as Microsoft Mail or EXCEL. Upgrading one machine to 12 or even 16 megabytes of RAM is a simple matter, but upgrading numerous client machines can be expensive.

With a networked application you need to do some detective work to decide where to add hardware. If there is a performance problem with a networked system it is much more involved to try to track down the source of the problem. Is the client too slow or the server? Is there a problem with the speed of the network wire? How much data is going across the network? Are too many users trying to access too few resources?

In a standalone application, database design is done primarily with an eye to providing as many features as possible to the user. In networked applications you need to assess your database design more carefully in terms of trade-offs between ease of access and speed of access. A standalone application can simply present as much of the application data as can sensibly fit on a screen. A networked application must be concerned with how much data is being sent back and forth between the server and the workstation. Particularly in a wide area network where data transfer rates may be slow, the goal of a networked application is to send the minimum amount of data at one time that the user needs to do their job.

Using Peer-to-Peer Networks

Peer-to-peer networks such as Microsoft Windows for Workgroups, Microsoft Windows 95, Novell Netware Lite, and Artisoft Lantastic are designed primarily for small companies or small workgroups within a large company.

The main emphasis of peer-to-peer networks is on sharing hardware resources such as printers, scanners, fax modems and CD ROMs. Peer-to-peer networks also provide basic file and database sharing capabilities. Any machine on the network can act as a server, making its resources available to any other machine on the network—or conversely, as a client requesting resources from any other machine on the network—provided those resources have been shared by the server machine.

Peer-to-peer networks can usually support 10 to 15 people, so long as their workload is not high volume and is evenly spread through the work period. Some of the advantages of using a peer-to-peer network are the following:

Additionally, peer-to-peer networks are typically informal with no full-time network administrator. This means that there is no one person responsible for coordinating file security and backup, or for ensuring that application performance remains within acceptable limits. This can be good, but on the other hand, it can provide the following disadvantages:

Making the Application Networkable

To make an application networkable, first create a path to it from each user's machine. Using Windows 95 as our example, follow these steps:

1. Install appropriate hardware, such as network cards, hub, and cabling. Install Windows 95, if necessary on all machines, and activate the Client for Microsoft Networks option along with File and Print Sharing from the control panel.

2. Select the machine with the most available hard disk space and RAM (or with the most peripherals such as a CD ROM, printer, and so on) to be the "server." Create a directory named ADDRESS on the "server" machine and copy the files from the ADDRESS directory on the accompanying disk to it.

3. Open the Windows Explorer from the Start button on the Programs menu.

4. Right-click the mouse button on your new ADDRESS directory to open a shortcut menu for this directory.

5. Choose the Sharing option to bring up the properties dialog for the directory. If the Sharing option does not appear on your shortcut menu, either the Client for Microsoft Networks has not been installed or the File and Print Sharing option has not been turned on. Select the Sharing tab and click the Shared As radio button to create a share name for this directory. The share name defaults to the directory name. Because you have only 12 characters here, leave it as directory name. You can describe the application a little better in the comment section.

6. Choose Depends on Password. Enter a password in the read-only text box to provide to users whom you only want to be able to read this. Enter a password in the Full Access text box to provide to users you want to be able to read and write this data. The Password Confirmation dialog box appears. Retype the passwords exactly as you did in the sharing properties dialog box.

7. Click OK to complete the share operation. The Windows Explorer file list is redisplayed. You see a little hand grabbing ahold of your directory folder file. You have successfully shared control of your application to anyone on the network who knows either the Read Only password or the Full password.

Connecting from the Client Side

To connect from the client side, follow these steps:

1. Open Windows Explorer from the Start button on the Programs menu.

2. Choose Map Network Drive from the Tools menu. This produces the Map Network Drive dialog box showing the next available logical disk-drive letter on your local machine (see fig 19.3). Click the Path pull-down combo box to show a list of UNC names for potential "servers" other computers attached to the network that are sharing directories. UNC names consist of \\computername\sharename where computername is the name you gave a computer when you set up your network software. Sharename is the name you gave the resource when you shared it from the server.

Fig. 19.3 The Map Network Drive dialog box allows you to create a new logical drive letter.

1. Select the UNC name that corresponds to the computer and directory where your application was installed and click OK.

2. The Enter Network Password Box appears; enter either the read-only password or the full access password. The Windows Explorer is redisplayed with the share directory added as a new "virtual" drive with the next available drive letter. Notice that all sub directories are also available.

Extending Peer-to-Peer Networking Capabilities

You can add extra levels of security to the application itself using your database software. See the section "Setting Up Security for Your Database" later in this chapter.

In addition, you can tune how fast the server machine shares its resources with clients as follows:

1. Select Control Panel from the Start button on the Settings menu. Double-click the System icon to display the System Properties dialog box. Select the Performance tab and double-click the File System button. From the Typical Role Of This Machine drop-down box, select Network Server (see fig. 19.4).

Fig. 19.4 The File System Properties dialog box allows you to configure a machine for typical use as either a desktop, mobile, or network server computer.

Note: This slows response for the local user; make sure that this machine is either dedicated as a server or receives only occasional light use.

Provided your client machines are powerful enough and have enough disk space, you can distribute your application. That is, put the application EXE on each client that needs to access the data. The server is now not responsible for actually running the EXE, and potentially can serve more clients. Network traffic may be reduced because your EXE does not have to be shipped across the wire.

Down sides to this are that anyone can make a copy of your application from any client and try to run it themselves on their machine with unpredictable results. Conversely, every time your application is updated, you have to distribute your EXE to everyone and make sure that everyone gets a copy and installs it. The clients may not be as powerful as the server, so if the server is lightly used, the clients may actually see longer response times. As an alternative you can buy a faster disk and more RAM for the server machine so that it can keep more data in RAM and doesn't have to access the disk as often.

If you have Windows NT workstations, you can use them to run a peer-to-peer network. Windows NT workstations provide better security in that logon IDs and passwords are mandatory in NT; they are optional in Windows for Workgroups. If you use the NTFS file system you can protect individual files rather than granting access to the entire directory.

Note that beginning with the 3.5 release of NT, Microsoft limited the number of connections to 10 concurrent users that can attach to the workstation. This is to ensure that performance remains acceptable for all users. Beyond this level you need to move to a dedicated server like Windows NT Advanced Server for performance and maintainability reasons. Usually machines powerful enough to run NT workstation are running heavy-duty applications of their own; placing the extra burden of server duty on them would not result in acceptable performance.

Using Dedicated File Server Networks

Dedicated server networks such as Microsoft Windows NT Advanced Server, Novell Netware, and Banyan Vines are designed to provide a centralized point of control and shareability. They provide a greater ability to support large departments. Dedicated file server networks are also used for small workgroups where security and centralized backup are an important consideration.

Dedicated file servers can be connected together to form a wide area network with hundreds or even thousands of users. They have more robust, feature-rich toolsets with security options to the individual file level, ability to dial in from a remote location to the server, and capability to monitor server and application performance.

Dedicated file servers can also be managed at the workgroup level by partitioning people and resources into domains. This allows users to access databases or hardware resources on a different physical server than the one that they are connected to. Other advantages of this method are as follows:

Note: Windows NT Server has a sophisticated performance monitoring tool built-in. You can monitor and tune numerous features of the system. The NT Resource kit devotes a whole book to this subject.

Setting up security for a multiple server configuration involving large numbers of users requires careful planning and coordination. If your network involves this kind of large scale deployment, a formal security structure plan is a must. You will need to work with network administrators from multiple departments to provide a balance between enough security, but not so much that it restricts users from doing their jobs. You also need to ensure that adequate resources are available on each server to support your application's demands. What works on one server flawlessly may not work on a similar machine, due to differences in configuration. Though there is a good solution for some, there are the following disadvantages to this method:

The major differences between the two types of network are summarized in Table 19.1.

Table 19.1 Table 19.1 Comparing Peer-to-Peer and Dedicated Server Networks

# of Security Cost Maintainability Scalability
Users

Peer to 10-15 Controlled to Inexpensive, Relatively Limited to
Peer Directory can use simple, can be 1 processor
level existing done by end
software users
Logon
password
optional

Dedicated 10-?* Can be Requires More complex, Can use
Server Controlled purchase of requires > 1 CPU
to File level additional trained
software administrator

* limited only by hardware capacity

Sharing an Application on a Dedicated File Server Network

The process for sharing files on NT Server is similar to that for Windows 95. You must access the server machine, and create a directory, if necessary, to contain your application. This directory becomes the share name or access point for the programs and files in the application.

1. Logon to the server using the Admin account or the account that owns the files that you wish to share.

2. Open File Manager from the Windows Program Manager.

3. Select Share As from the Disk menu to create a share name for this directory. The New Share dialog box appears (see fig 19.5). Enter a meaningful Share Name. Because you have only 12 characters here you may as well leave it as the directory name. You can describe the application a little better in the comment section.

Fig. 19.5 The New Share dialog box allows you to establish a share point for your application from the server.

Note: You can limit the number of users who can access the directory. This gives you the opportunity to trade off, allowing multitudes of people to fight for control of this resource versus limiting the number of people, but gives them a reasonable share of access to the resource. This is something that you can adjust later if people are having problems getting a reasonable response time. So leave the radio button checked at Maximum Allowed for now.

1. Click the Permissions button to set the permissions for the directory. The Access Through Share Permissions dialog box appears.

2. Click the Add button and the Add Users And Groups dialog box appears.

3. Scroll through the list of groups, or click the Show Users button to display individual users. Double-click your selection to add it to the Add Names box; select the type of access you want the user or group to have in the Type Of Access box and click OK.

4. The Access Through Share Permissions dialog box appears again (see fig 19.6), showing your new user/group.

Fig. 19.6 The Access Through Share Permissions dialog box allows you to specify the type of access that client workstations have to the share point.

If your files are created on an NTFS partition, you can enforce security at the file level. The permissions you can grant in an NTFS partition are also more granular. You can assign Read, Write, Delete, Execute, change Permission and take ownership rights to individual users or groups of users.

Using Desktop Databases

There are a multitude of desktop databases that you can use with Visual Basic. You can select from dBase, FoxPro, Paradox, Microsoft Access, Btrieve, even Lotus, Excel, or plain old text files. Many desktop databases are adding multi-user capabilities, but quite simply the easiest way to access any of these databases in Visual Basic is through the Microsoft Jet Database Engine provided with the Visual Basic package. The combination of Visual Basic and the Jet engine make database access largely a matter of point and shoot through the use of Data controls.

In addition, desktop database products are fast because they lock data at the database level and do not provide the extra features provided in multi-user databases. The only down side of this are the following disadvantages:

Even if you use a single user desktop database system on a network, you will want to supplement network security with a database security scheme. By default, the Jet engine allows you to access any database without having to supply a user name or password. This is usually okay in a single user environment. In a multiple-user environment, you want to require the users to logon with their name and password. You do not have to set up multiple security IDs. Every user who knows the logon password for the application is granted the same permission to add, delete, or update records.

Visual Basic now includes a number of security features that can restrict applications from accessing entire databases or their table, field, user, or group objects. In order to manage which users have permission to access which objects, Jet uses a SYSTEM.MDA file. This file and the application you use to create and maintain files of this type are included with Microsoft Access but not with Visual Basic.

When you install Microsoft Access, a default SYSTEM.MDA with no security is automatically created in your Microsoft Access directory. A tool called the Access WorkGroup Administrator is automatically placed in the program group where you installed Microsoft Access. Until you explicitly set up the security system neither Access nor Visual Basic know anything about security. Also, Microsoft Access uses the default workgroup each time you start Microsoft Access.

Setting Up Security for Your Database

Whenever you create or change any security objects, write down their names, including whether letters are upper or lower-case, and keep them in a safe place. If you ever have to recreate the system database, you must supply the exact same entries. If you forget or lose these entries, you can't recover them unless you have a current copy backed up in a safe place.

Follow these steps to password-protect your database:

  1. Create a workgroup system database in which to establish and maintain users and groups of users or join an existing workgroup.
  2. Activate the logon procedure for this workgroup.
  3. Create an administrator logon account for the workgroup and an owner account for the application database.

You can use the MS Access WorkGroup Administrator to create a new Microsoft Access workgroup as follows:

  1. Set up the directory on the server machine to hold your application.
  2. Double-click the MS Access WorkGroup Administrator icon. The WorkGroup Administrator dialog box appears.
  3. Click the Create button. Microsoft Access displays the WorkGroup Owner Information dialog box, which allows you to enter information that identifies the owner of the system database that defines the new workgroup. Type your name and organization in the dialog box if they're not already present.
  4. Type a unique workgroup ID using a meaningful name, in this case SALES, and click OK. Click OK again when the Confirm WorkGroup Information dialog box appears.

1. Click the OK button to display the WorkGroup System Database dialog box (see fig. 19.7). Enter the path and name of the system database that defines the new workgroup. It's best to name the database SYSTEM.MDA, because there are other utilities that expect your security file to be called that name.

Fig. 19.7 The WorkGroup System Database dialog box is where you specify that you want to keep the SYSTEM.MDA security permissions database for your application.

1. Click OK to display a Confirmation dialog box that displays the path to your system database. This path is saved in your Microsoft Access initialization file and, until you change it again using the WorkGroup Administrator, this will be your default security file.

Your new workgroup is now set up. The next time you start Microsoft Access, it uses the new workgroup. Microsoft Access stores any user and group accounts or passwords you create, or changes in system option settings, in the new workgroup's system database.

Activating the Logon Procedure

Until you establish security for a workgroup, Microsoft Access always logs you on at startup using a default Administrator account which does not require you to enter a password. The default password for the Admin account is a zero-length string. To activate the logon procedure, change the Admin user account's password from a zero-length string to another password as follows:

1. Start Microsoft Access using the new workgroup you've chosen (which is the default, unless someone else has used the WorkGroup administrator to join a different group).

2. Open any database. Because the security settings are stored in the workgroup system database, you can open any database (.MDB) file.

3. From the Security menu, choose Change Password. The Change Password dialog box appears (see fig. 19.8).

Fig. 19.8 The Change Password dialog box allows you to set up and maintain the password security for your Access databases.

1. In the New Password box, type the new password.

2. Confirm the new password by typing it again in the Verify box and clicking OK.

The next time you start Microsoft Access, it will display the Logon dialog box (see fig. 19.9).

Fig. 19.9 The Microsoft Access Logon dialog box provides logon security to your application database.

Using WorkGroup Databases

The Microsoft Jet engine can also function as a workgroup database. To make your application into a true multi-user system you need to do a few more things.

Multi-user (WorkGroup) databases such as Microsoft Access provide their own security system which acts as a complement to any security provided by the network software. For Access, this is provided via the SYSTEM.MDA database and accompanying WorkGroup Administrator program.

WorkGroup databases provide flexible and efficient support for locking data that is in the process of being updated. Access has three native locking mechanisms to control multi-user data access in your Visual Basic applications. These locking levels are discussed in the section on database locking.

Note: I do not recommend trying to write your own locking scheme. WorkGroup databases locking support, while sophisticated, is not as robust as that of client/server databases, and it requires you to be extremely thorough in your error-checking procedures or your users will get cryptic and unhelpful error messages. Also, the security systems in WorkGroup databases must be maintained by someone and the security files must be backed up regularly.

Caution: You have protected the database from the casual user, but if someone wants access, he can get access by using another copy of Microsoft access and the default Admin account.

Because the default Admin account is exactly the same for every copy of Microsoft Access, it is not enough to password protect the default Administrator account. You should set up your own administrator account in place of Admin. Otherwise, anyone with a copy of Microsoft Access can log on to your workgroup using the Admin account and have full permissions for the workgroup's objects. And then, of course, you need to delete that Admin account.

The first step in activating the logon procedure is to create a new administrator account along with the user and group accounts for the application. Then you need to remove default permissions and assign new permissions for the application objects. Finally, you need to create passwords for the necessary accounts and encrypt the database. The following sections take you through this process.

Creating a New Administrator Account

To create a new administrator account, follow these steps:

1. Log on to Access using the default Admin account and the password you set up in the section "Activating the Logon Procedure."

2. Choose Users from the Security menu. The Users dialog box appears (see fig. 19.10).

Fig. 19.10 The Microsoft Access Users dialog box allows you to maintain the list of valid users for this application.

1. Click New and the New User/Group dialog box appears (see fig. 19.11). Type the Name of the new account and a Personal ID.

Fig. 19.11 The New User/Group dialog box allows you to add a new user or group of users to the list of valid users/groups for this application.

  1. Click OK to create the new account. Microsoft Access returns to the Users dialog box.
  2. From the Available Groups list, select the Admins group and click Add. Microsoft Access adds the new account to the Admins group and displays Admins in the Member Of list.
  3. Click Close to close the Users dialog box.

An administrator account is a member of the Admins group for a workgroup. An administrator account can always get full permission for all objects created in the workgroup. Therefore, rather than just setting up an Admin account for your database and giving out that password, you want to create an owner account that can do routine maintenance on that particular database only. An owner account is a user or group account you designate to own (have control over) an application's databases and objects. Like an Administrator account, an owner accounts permissions can't be taken away, even by an administrator.

Changing Database and Object Ownership

If you're creating a secure application from scratch, the best approach is to log on with the account that you want to own the database and its objects, and then create the database and its objects.

In this case, because the database was created using the default Admin account, Admin owns the database and any objects in ADDRESS application and in any other application that hasn't been secured. To secure the application, you must change ownership of the database and its objects to a designated owner account.

You can change ownership of an existing database and all its objects by creating a new database, and then importing the contents of the existing database.

To change ownership of a database and all its objects, follow these steps:

1. Start Microsoft Access using the new workgroup you created and log on using the account that you want to be the new owner.

2. Select New Database from the File menu. This displays the new database dialog box. Type a name for your database and press Enter.

3. Choose File, Import, and click OK. The Import data source dialog box appears (see fig. 19.12).

4. Double-click Microsoft Access, which brings up a dialog box listing the available Microsoft access databases in the current directory.

Fig. 19.12 The Import dialog box allows you to specify which database you want to import.

1. Double-click the database you wish to import and the Import Objects dialog box appears (see fig. 19.13).

2. In the Object Type box, select Tables and click Import. Microsoft Access imports all objects for which you have permission from the selected database to the new database you've created.

Fig. 19.13 The Import Objects dialog box allows you to select which database objects to import.

Creating and Managing User and Group Accounts

Setting up groups of users makes it easier to manage a secure application. Instead assigning permissions to each individual user for each object in your application, you can assign permissions to groups, and then add users to the appropriate group. A member of a group inherits the permissions of any groups to which they belong, in addition to any specific permissions you grant to that specific user.

For example, as you secure the ADDRESS application, you can create a Managers group for managers and a Sales group for sales representatives. You can assign one set of permissions to the Managers group, and another set of permissions to the Sales Reps group. To create a new Sales group account, follow these steps:

1. Start Microsoft Access using the workgroup you've chosen.

2. Open a database.

3. Choose Security, Groups. The Groups dialog box appears (see fig. 19.14).

Fig. 19.14 The Groups dialog box allows you to maintain valid groups for your application.

1. Click New and the New User/Group dialog box appears.

2. Type the Name of the new account and a Personal ID.

3. Click OK to create the new account.

4. The Groups dialog box appears again. Click Close.

Assigning and Removing Permissions

To secure an application, you must remove the permissions of the Admin user and Users group, which includes all users in a workgroup. Until you remove those permissions, users may have permissions that you didn't intend for objects. Once the permissions have been removed, users have only the permissions you assign.

After you've created user and group accounts, you can assign permissions to those accounts for the application's objects. To secure the ADDRESS2 application, you can assign permissions to the Sales group you created in the section "Creating and Managing User and Group Accounts."

1. Open the MONTARE database. Choose Security, Permissions. The Permissions dialog box shows a list of valid users (see fig. 19.15).

Fig. 19.15 The Permissions dialog box lists valid users/groups for the application and their specific access rights or permissions for individual database objects.

1. Highlight the appropriate user and select or clear the appropriate check boxes to set/revoke permissions and click Assign.

To set permissions for groups, select the Groups option button to display a list of groups in the Permissions dialog box. Then do the same for users.

Encrypting a Database

The final step in protecting your database from unauthorized access is encrypting the database. When you encrypt a database file, Microsoft Access makes it indecipherable in order to protect it from unauthorized viewing or use by someone using a utility program or a word processor.

1. In the Microsoft Access startup window choose File, Encrypt/Decrypt. The Encrypt/Decrypt Database dialog box appears.

2. Select the database that you want to encrypt or decrypt and click OK. The Encrypt Database As dialog box appears (if the database you selected isn't already encrypted). If the database is encrypted, the Decrypt Database As dialog box appears.

3. Specify a file name, drive, and directory for the destination encrypted or decrypted database using standard MS-DOS naming conventions. Then click OK.

Using Client/Server Databases

By separating the Jet Database Engine from the physical data store (.MDB, .DBF files, etc.), Microsoft made it possible to connect to a number of different back-end physical database file types. You can connect to virtually any database that uses SQL as an access mechanism, such as SQL Sever, Oracle, DB2. You can even use mainframe files in VSAM or ADABAS through third-party ODBC drivers.

The flexibility of the Jet engine leads to some confusion about what that engine is suited for and consequently to misuse and associated performance problems. Even though you can couple the Jet engine to a database on a remote server, the Jet Database Engine is not a client/server engine.

The database engine is dynamically linked to your application, and so each copy of your program will have access to its own local copy of the Jet Database Engine. A thorough understanding of the SQL preprocessor and the results processor can dramatically improve the speed of your client/server application. You can decrease network traffic by structuring your application so that an efficient query that takes advantage of existing indexes is sent to the server and returns the minimum data set that meets your criteria to the client.

When using external data engines such as Microsoft SQL Server, Sybase, or Oracle, the locking methodology is the responsibility of the remote database engine.

The advantages of client/server databases are as follows:

The disadvantages of client/server databases are as follows:

For more information on client/server databases, see Chapter 9, "Client/Server Databases."

Locking Levels

In order to prevent the types of problems discussed in the sections on Consistency and Concurrency in a multi-user system, you need to implement a locking strategy. A locking strategy is simply a means to prevent other users from attempting to update a particular record, part of a particular record, or a collection of records while another user is in the middle of updating the same data. In a high-use multi-user system, the typical goal is to lock the minimum number of records possible for the shortest period of time, while ensuring that the database is maintained in a consistent state at the end of our processing.

Access provides three different levels of locking, database level, page level, or recordset level. Recordset locking is the most often used type of locking because it is less restrictive than database locking. Using this method, you have the flexibility of locking only the records or tables you are working with at the time, so other records or tables can be used by others who are using the same database.

The default locking level when accessing a Jet engine database is page-level locking. Unless you specify Database- or Recordset-level locking, the entire 2K page of data containing the record you're editing is locked. Because entire pages are locked, and a given page may contain more than one record, users need not be on the exact same record to cause a locking conflict. For example, if user A locks a record in page 1, and user B attempts to edit another record that is also on page 1, user B will receive a locking error. Other users can read data from locked pages.

Database locking is the most restrictive type of locking. It provides fast access in single user systems, but is rarely used in multi-user situations, except to perform database maintenance or in specialized applications that require a lot of updates done by one user

Caution: Programmers often overlook the fact that the same locking scheme applies to the index pages. When you use the Seek method or you are rebuilding indexes, entire 2K pages of indexes are locked. This can cause locking errors which can affect access to a large number of actual records in the database.

Note: The Jet engine does not provide for locking at the individual-record level. If your application requires that you lock at the record level, you can force Jet to lock individual records by creating record sizes that are larger than half a page—that is, larger than 1024 bytes. This works because Access won't begin storing a new record on a partially filled page if it can't fit the entire record on that page. Of course, this will also waste a tremendous amount of disk space.

The following programs set locking at the database, page, and recordset levels and demonstrate how to check for possible multi-user locking conflicts with other people trying to access the same data. If there is a conflict, the user receives a message box that describes the type of error. In most cases the code allows the user to retry the operation or cancel. Note that the error handling in the samples is only for the purposes of trapping locking errors, and you will need much more extensive error handling code in an actual application.

Page Locking

There are two main runtime errors to trap for with page level locking. The first is error 3260 ("Couldn't update; currently locked by user x on machine y"), which indicates that another user has the page you want to modify locked.

The second error is 3197 ("Data has changed; operation stopped"), which indicates that someone has modified the record that you are attempting to modify in the database since the last time you retrieved that page from the database.

For example, user A creates a Recordset on the Contacts table and pulls in the first page (2K) of records from the database. User B now creates an identical Recordset, retrieving the same 2K of records. If User A updates the first record in the Recordset, user B will not immediately see this change, because User B has already retrieved the first page of record and will not do so again unless he or she refreshes (recreates) the Recordset.

If user B now attempts to modify the first record of the Recordset, he or she will receive error 3197, warning them that they are about to overwrite new data which they have never retrieved from the database. If you re-execute the operation that caused the "Data has changed; operation stopped" after receiving the error once, the data will be overwritten without the error being generated a second time. Listing 19.1 is sample code to deal with this situation.

Listing 19.1 PAGELOCK.TXT Routine to Handle Page-Locking Errors

Sub Handle_Lock ()
Dim Mydb As database
Dim Myds As dynaset
Dim ret As Integer, fSuccess As Integer
Set Mydb = OpenDatabase("MONTARE.MDB")
Set Mydb = db.CreateDynaset("CONTACTS")
Do Until Myds.EOF = True
' Read records, checking for possible page locking conflicts
fSuccess = False
' Disable any previous error handler and continue
On Error Resume Next
While Not fSuccess
Err = 0
Myds.Edit
If Err Then
If Err = ERR_DATA_CHANGED Then
ret = MsgBox("Record has been updated. Overwrite?",_
MB_RETRYCANCEL)
If ret = IDNO Then fSuccess = True
ElseIf Err = ERR_RECORD_LOCKED Then
ret = MsgBox("Record in use by another user.",_
MB_RETRYCANCEL)
If ret = IDCANCEL Then Exit Sub
Else
MsgBox "Unexpected error" & Str$(Err) & " editing_
record."
Exit Sub
End If
Else
fSuccess = True
End If
Wend
' disable error trapping OR place On Error statements
' pointing to a new error handler here
On Error GoTo 0
Myds("CONTACT") = Myds("CONTACT")
Myds.Update
Myds.MoveNext
Loop
Myds.Close
Mydb.Close
End Sub

Recordset Locking

To update our standalone contact manager application, which uses a Data control, the method is as follows:

1. Open up the ADDRESS.MAK project in the ADDRESS directory on the accompanying disk. Open Form1.

2. Right-click on the Data control and select Properties.

3. Scroll to the Recordset type property, bring up the list box, and you can see that you can specify whether you want the Recordset to be a Dynaset, Table, or Snapshot. Leave it as a Dynaset.

4. Scroll, if necessary, to the Options property which is defaulted to 0, no locking. Change this to 5, which is a combination of 1 (dbDenyWrite) + 4 (dbReadOnly). In a multi-user environment dbDenyWrite specifies that other users cannot make changes to records in the Recordset. The other option, dbReadOnly, specifies that your process can't make changes to records in the Recordset. You might use this combination if you wanted to scan through a group of records and summarize them and didn't want anyone changing them during the process.

5. If you set the DatabaseName and/or RecordSource properties of a Data control at designtime, the Data control will automatically attempt to perform the equivalent of an OpenDatabase and OpenRecordset when the form containing the Data control is first loaded.

6. If an error occurs when the Data control attempts to automatically open the Database or Recordset, the Data control will fire its Error event and pass in the appropriate runtime error value. Because no Visual Basic code is executing at this time, you will need to handle any possible locking conflicts in the Error event rather than using On Error. Add the following code:

Sub Data1_Error(DataError As Integer, Response As Integer)
ret = MsgBox("Table(s) in use by another user.",_
MB_RETRYCANCEL)
Select Case DataError
Case 3024 ' If database file not found.
' CMDialog1.Action = 1 ' Display an Open _
dialog box.
ret = MsgBox("Table(s) in use by another user.", _
MB_RETRYCANCEL)
End Select
End Sub

Note: If you change the Options property at runtime, you must use the Refresh method for the change to have any effect.

1. Compile the program and try running two different instances of it. If another user tries to edit any of the records in your recordset at the same time you are running the previous code, he would get an error message.

2. Save the project as MYADDR if you wish to keep it for future reference.

The OpenRecordSet method gives you more control over your access to the data.

1. Open the ADDRESS4.MAK project in the ADDRESS4 directory on the accompanying disk. Change the line opening the Recordset from:

Set data1 = db.OpenRecordset("MONTARE")

to this:

Set data1 = db.OpenRecordset("MONTARE", DB_DENYWRITE Or DB_DENYREAD)
"Check for errors
If Err Then
MsgBox "Unexpected error" & Str$(Err) & " opening database."
Exit Sub
Else
fSuccess = True
End If
' check for locking conflicts or other errors
If Err Then
If Err = ERR_CANT_OPEN_TABLE Then
ret = MsgBox("Table(s) in use by another user.", _
MB_RETRYCANCEL)
If ret = IDCANCEL Then Exit Sub
Else
MsgBox "Unexpected error" & Str$(Err) & " opening _
database."
Exit Sub
End If
Else
fSuccess = True
End If

1. Save the project as MYADDR4 if you wish to keep it for future reference.

2. Choose Run, Start (or press F5) to run the program. Click the Show Me A Record button. If another user tries to access the MONTARE table when you're running the program, they would get an error message.

This method is not normally used for Snapshots, because they are read-only, and changes to the data will not normally affect a Snapshot (with the exception of memo fields).

Error 3197 ("Data has changed; operation stopped") can occur for Snapshots that have memo fields. Because memo fields are usually quite large, the Access engine does not pull the entire contents of a memo field into the Snapshot at the time it is created; instead, a reference to the memo field in the database is stored in the Snapshot.

If the data in the memo field is changed by another user between the time a Snapshot is first populated (meaning that you access a given record, using the Move or Find methods or visit all records, using the sn.MoveLast method) and the time that record is revisited or made the current record again, the database engine will signal that your data is out of date by invoking runtime error 3197.

Database Locking

Once again, using our standalone application as a starting point which uses a Data control, the method for database locking is as follows:

1. Open the ADDRESS.MAK project in the ADDRESS directory on the accompanying disk. Open Form1.

2. Right-click on the Data control and select Properties.

3. Scroll, if necessary, to the Exclusive property which is defaulted to False. To enable database locking, change this to True via the list box. As long as this Data control is active, no one else will be able to access any record in the entire database; they will receive an error message explaining that the data is in use by another user.

4. Compile the program and try running two different instances of it. If another user tries to open the MONTARE.MDB database at the same time you are running the code listed above, they will receive an error message.

5. Save the project as MYADDR if you wish to keep it for future reference.

If you're handling your data access programmatically, as in the ADDRESS4 application, the OpenDatabase method is as follows:

1. Open up the ADDRESS4.MAK project in the ADDRESS4 directory on the accompanying disk. The OpenDatabase method is opening the database in non-exclusive mode as follows.

Set db = OpenDatabase("MONTARE.MDB")

1. Change this to:

Set db = OpenDatabase("MONTARE.MDB", True)

1. Now the entire database is being locked for your exclusive use.

"Check for errors
If Err Then
MsgBox "Unexpected error" & Str$(Err) & " opening database."
Exit Sub
Else
fSuccess = True
End If
'Check for locking conflicts or other errors
If Err Then
If Err = ERR_CANT_OPEN_DB Then
ret = MsgBox("Database in use by another user.",_
MB_RETRYCANCEL)
If ret = IDCANCEL Then Exit Sub
Else
MsgBox "Unexpected error" & Str$(Err) & " _
opening database."
Exit Sub
End If
Else
fSuccess = True
End If

1. Save the project as MYADDR4 if you wish to keep it for future reference.

The Jet engine provides two different approaches to locking: optimistic and pessimistic. In either case, you need to handle errors raised by the Jet engine that indicate that someone else has either locked the data or has updated the data in the middle of your attempt to update the data.

Optimistic Locking

You can take the optimistic approach and only lock data at the point where you actually update the data. This approach assumes that the likelihood of another user attempting to access your data to update it is low.

The advantages of using optimistic locking are as follows:

The disadvantages of optimistic locking are as follows:

Listing 19.2 opens a Recordset and sets its LockEdits property to False. This enables optimistic locking so other users can change the database records at any time. Your application triggers a trappable error if the data changes before you use the Update method.

Listing 19.2 OPTLOCKS.TXT Setting LockEdits Property to False

Sub OptLocks()
Dim MyDB As Database, MyRecords As Recordset
Set MyDB = Workspaces(0).OpenDatabase("ADDRESS.MDB")
Set MyRecords = MyDB.OpenRecordset("MONTARE")
MyRecords.LockEdits = False
MyRecords.Edit ' Start editing.
MyRecords!Name = "Joe Armitage"
OnError Goto MyErrH
MyRecords.Update ' Try to post the changes.
MyRecords.Close
Exit Sub
MyErrH:
If Error = 3197 Then ' Data changed.
MsgBox "Data changed by Someone else."
Resume Next
Else
Msgbox "Some other error."
End If
End Sub

In a real application you would allow the user to display the new data and then perhaps attempt his update again.

Using External Database Engines

When using ODBC data sources such as Microsoft SQL Server, Sybase, or Oracle, locking is handled by the remote database engine. Visual Basic simply acts as a front-end to these database servers and does not control the locking mechanisms used in the back-end database engines. Therefore setting LockEdits or the Exclusive parameter of the OpenDatabase method have no effect on ODBC databases.

Caution: In some cases, you can control how the remote server locks data by using backend-specific SQL statements or administrative options. Do not send commands that lock the entire table or database in a high-volume transaction environment, as this will lock everyone else out, bringing the system to its knees.

Note: Designing thorough error-handling routines that react correctly when locking contention occurs is the only reliable way to allow the backend database engine to operate its native locking scheme for maximum throughput. Think about what you are trying to achieve before you code and test for a variety of possible lock/contention situations.

Pessimistic Locking

Jet by default takes the pessimistic approach and assumes that someone else will want to update your data, and automatically locks the data as soon as you use the Edit method on the data. The data is not released until you use the Update method, Close method, or Find or Move to another record.

The advantages of pessimistic locking are as follows:

™B7 It is simple for the developer and may be less confusing to the user.

The disadvantages of pessimistic locking are as follows:

Caution: Pessimistic locking can potentially prevent your users from being able to edit or add new records for long periods of time if someone places a lock and walks away from their computer or starts a task that updates large amounts of data. Unless you have a dire need to use pessimistic locking, I strongly recommend that you use optimistic locking with appropriate error-checking. If you decide to use pessimistic locking in your applications, you will spend a lot of time explaining what locked records are and why the users can't get at their database.

From Here...

This chapter taught you how to identify the different types of network system and their unique advantages and disadvantages. It also taught you the importance of thorough, up-front design techniques, and complete error-checking to identify and/or prevent multi-users errors. You also learned the different roles that single-user, workgroup, and client/server databases play in the design and development of networked systems.

To round out your knowledge on using Visual Basic for developing networkable and client/server applications, you should also review the material in the following chapters of this book:


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.