Navigation:  Articles > Jul-1997 >

Using Source Code Control with Access 97

Previous pageReturn to chapter overviewNext page

Mike Gilbert


Microsoft has added built-in support for source code control to Access 97. Mike explains how this works in Access 97, and how to go about using this exciting new feature with Access 97, the ODE, and Visual SourceSafe.


Source Code Control (SCC) is a process where individual source components of a software program are maintained by a central database and made available to multiple developers in an orderly and controlled fashion. SCC lets many people use the source files to compile and test copies of an application but (usually) restricts the ability to make changes, allowing only a single programmer to perform modifications at any given time. SCC is crucial in large-scale development tasks and also can be useful for single developers because it maintains prior versions of source files. C, C++, and even Visual Basic developers have been able to use SCC to manage their applications for years. Until the latest version of Access, however, it was nearly impossible to use it with MDB files.


Why, you ask? The reason stems from the fact that SCC programs depend on individual text files to make up the components of a project. This is necessary because a file is the smallest unit that can be managed or shared. As you know, however, Access "projects" are contained within a single binary file -- the MDB file. However, Microsoft added features to Access 97 that allow it to export objects as text files so an SCC program can control them.


What you need

Before implementing source code control with Access 97 you'll need several things, the first of which is a source code control provider application. An SCC provider is the actual software that creates and maintains a central database of source code. It's also the piece of software that controls access to the files. Microsoft has its own SCC provider called Visual SourceSafe (VSS). VSS comes with the Enterprise versions of Visual Basic and Visual Studio, but you can purchase it separately. You can also use other SCC programs that are enabled for Access 97, but there are none that I know of yet. The examples in this article refer to VSS concepts and commands.


Installing VSS is a two-step process. You first run the setup program from the VB, VS, or VSS CD-ROM on the computer where you want to create the central database. Because other developers will need access to the database, you should make sure the computer is connected to a network and that file sharing is enabled. During setup, select the "server" option to create the central database.


The next step in installing VSS is to run the network setup program on each workstation from which you want to access the database. During installation of the server files, the setup program will copy everything needed to install VSS on any number of workstations. By running the setup program on each workstation, you not only copy the required VSS files but you also establish the connection to the central database.


Once an SCC provider such as VSS is installed, the next thing you'll need is the add-in that lets it work with Access 97. This add-in ships with Office 97 Developer Edition (ODE). When you run the ODE setup program, one of the optional installation components is "Source Code Control Integration." Make sure you select this if you want to use SCC with Access.


Finally, you'll want to be familiar with the overall process of using an SCC program with a development tool such as Access. To do this you'll need to understand some terminology. Table 1 lists several key SCC terms along with their meanings. If you've never used a SCC program before, take a moment to review the list.


Table 1. A few important source code control terms.


Term        Description


Project        A project consists of one or more source files necessary to compile a working application. A project may also imply program options such as the application name, compile options, and so on.

Source file        Source files are individual text or binary files that make up a project. The most common type of source file is a code module, but bitmaps, icons, and even documentation files can be considered source files. Most SCC providers let you share source files among many projects.

Check in and check out        When you want to work on a particular source file, you must "check it out" from the project on the server. After making sure no other developer already has the file checked out, the provider copies the latest version of the file to your computer so you can modify it. When you've made your changes and want others to be able to use them in their copies of the application, you check the file back in. In this sense, source code control is like a library with the SCC provider acting as librarian.

Multiple check out        Some source code control providers allow more than one developer to check out the same source file. While this can lead to problems, it's sometimes necessary, especially with source files for generic or "utility" functions.

Merge        During the check-in process, the SCC provider compares the version being checked in with the current version. If it detects conflicts, it will try to resolve them by merging the two source files. If it can't resolve a conflict, you must manually merge the two files.

Get        If you don't want to modify a particular file but only want to include it in your copy of the application, you perform what's known as a get. A get copies the most recent version from the server to your computer, overwriting the existing version stored there. You should perform gets periodically during the day to bring in changes to source files made by other developers.

Diff and Restore        Most SCC providers support versioning. When a developer checks in a source file, rather than simply overwriting the copy on the server, the provider archives it, logging the date and time it was checked in and by whom. This allows you to compare versions, called performing a diff (for difference). If you wish, you can restore an older version of the source file and make it the current one.


The process

Later in this article, I'll explain the exact steps needed to manage a database project using a source code control provider. However, here's the general process.


After installing the appropriate software (a provider and the SCC add-in), you (as a single developer who has the most up-to-date copy of the MDB file) begin the process by creating a source code control project from an existing Access database. Creating a project from an existing database produces the appropriate source files on the source code control server and makes them available to other developers.


Once the project exists on the server, you must allow other developers to begin using it. Each developer begins by creating a new Access database on his or her workstation and, in effect, importing all the objects from the project on the server. (In actuality the SCC add-in creates the copy and imports the objects for you.) Each developer then works on his or her own copy of the database, checking objects out to modify them and checking objects back in when they're done.


Eventually, when you're ready to ship your application, you'll want to remove a database from source code control. To do this you simply compact the database. Each time you compact a database, Access asks whether you want to remove it from source code control. If you answer yes, Access breaks the link from the SCC project, and you'll no longer be able to use SCC commands. Once you've removed a database from source code control, there's no way to re-establish the link with the SCC project.


How Access exchanges objects

An Access database stores two things that are normally separate in other development environments: data and application objects (queries, forms, reports, macros, and modules). Access 97 treats these separately when it comes to source code control. When Access exchanges application objects with an SCC provider, it exports each object as a separate text file. These files define the properties of each object and, where applicable, any VBA code the object contains, using a format similar to the one Microsoft Visual Basic has used for years. Everything else an Access database can contain (tables, relationships, command bars, custom properties, and import/export specifications) remains in a single, binary file that the SCC add-in treats as a separate component. Figure 1 illustrates how Access treats objects under source code control. When you retrieve them from the SCC provider, Access reassembles them into a single MDB file.


sccfig01   sccfig02


sccfig03   sccfig04


sccfig05   sccfig06


sccfig07   sccfig08





While the exchange of objects is accomplished with text files, it's important to remember that developers themselves still work with an Access database. The conversion to text files and back is completely transparent. Developers who work with a database under source code control each maintain a separate MDB file on their own hard disks. When they check out or get objects from the SCC project, the SCC add-in copies them, as text, to their hard drives and then integrates them into a local MDB file. When they check the objects back in to the project, the process is reversed. The add-in instructs Access to export the objects to your local hard drive and then copies them back to the project on the server.


In addition to objects that are part of an SCC project, you can maintain local objects in your copy of the Access database. This applies to everything except tables. (You can't create local tables because all tables are stored as a single binary object.) Local objects exist only in your database and are useful for testing and experimentation. After creating a local object, you can add it to the SCC project.


Also, because Access breaks up a database into separate source files while working with an SCC provider, it must have a place to store these files. In SCC nomenclature, this location is sometimes called the working directory. It's the place on a developer's hard drive where files are stored when they are checked out, as well as before they are checked back in to the project. Access implements a working directory by creating a subdirectory beneath the directory where the local MDB file is stored.


Access provides visual feedback for objects that are under source code control. Special icons in the database window designate these objects (see Figure 2). They include the lock, indicating an object in the SCC database; the check mark, indicating that you have the object checked out; and the small person, indicating that another user has the object checked out. Local objects won't have any icons at all.


In addition to the new properties, changes to objects affect source files in the server project. For example, renaming an object in your copy of the database renames it in the project as well. The next time another developer performs a get, that developer's local object will be renamed. Given the potential complications involved in renaming objects (such as hard-coded references in code), use care when renaming objects. You must check out the binary data object in order to rename tables, command bars, or relationships. You won't be able to rename an object if another user has the object checked out.


Deleting an object also has an effect on the project. When you delete an object from your database, the SCC add-in asks whether you want to delete it from the project as well. If you choose to do this, other developers won't be able to check out or use the object.


Placing a database under source code control

The first step in using an Access database with source code control is to add it to a VSS project. To place a database under VSS source code control, open it in Access and select Tools, SourceSafe, Add Database To SourceSafe. After you give Access permission to add the database to SourceSafe, it closes and reopens the database. You'll then have to log in to VSS. Unless you've enabled the option to use your network name and password with VSS, you'll be prompted by the VSS Login dialog box. Enter a valid VSS user name and password.


Access then prompts you with a dialog box like the one shown in Figure 3, listing all the VSS projects and subprojects. You can either enter the name of a new project in the text box or select an existing project from the tree view window. If you want to create a new project, you can either enter a complete path in the text box (for example, "$/Sales/Inventory") or select an existing project and enter just the name of the new subproject. In the latter case, VSS creates a new subproject beneath the selected project. The Create button creates a new project immediately and displays it in the tree view window. If you don't click the Create button, VSS automatically creates the new project when you click OK.


Access won't let you add a database to a VSS project that already contains a database under source code control. After choosing a VSS project name, you're presented with another dialog box. This one (see Figure 4) gives you the opportunity to add database objects (including the binary data object) to the VSS project. You need not add any objects at this time. This dialog box is accessible whenever you're working in a database under source code control.


You can select as many or as few objects as you like from the "Objects to be added" list. If you have a lot of objects in your database, you can view only those of a given type by selecting them from the Object Type combo box. Note the highlighted entry in Figure 4 ("Other: Data and Misc. Objects"). This is the entry for the binary data component. After making your selections, click OK to add them to the VSS project.


If you decided not to add all the objects in the database when you added it to SourceSafe, or if you've created new local objects, you can add objects to a VSS project by selecting the Add Objects command from the SourceSafe menu.


Creating a database from a VSS project

Once one developer has created a VSS project from an Access database, other developers can begin working with it. However, they can't simply copy the MDB file to other workstations because it contains custom properties that apply to the original VSS user. If you attempt to open a database placed under source code control and you aren't the VSS user who created it, Access issues a warning message and disables all source code control functions.


To begin working with a database under source code control, you must use the SourceSafe menu to create a new database. Select Tools, SourceSafe, Create Database from SourceSafe Project. A dialog box will prompt you to specify a local working directory and to select a VSS project folder. Select the VSS project containing the database you created using the previously listed steps.


After making your selections, click the OK button. Access and Visual SourceSafe will create a new MDB file and copy all the objects from the VSS project on the server. You can then start working with database objects under source code control.


Working with objects in a VSS project

Adding objects to a VSS project is only the first step in using source code control features with Access. If you want to make changes to objects, you'll need to check them out. You can check out objects in one of two ways. If you attempt to open an object under source code control in design view, Access will prompt you to check it out, and will check it out if you tell it to. You can also check out objects interactively using a VSS-supplied dialog box. To open this dialog box, shown in Figure 5, select the Check Out command from the Tools, SourceSafe menu.


The Check Out dialog box lets you select one or more objects to check out at the same time. Use the Object Type drop-down list to view objects of a given type or all objects in the project. Mark those you wish to check out with a check mark in the Objects to be Checked Out list. Use the Comment text box to enter a comment regarding why you're checking out the objects. Comments are stored in the VSS database and can be read and printed by the project administrator.


Occasionally you may want to reverse or "undo" a checkout. For example, suppose that after checking out an object and making numerous changes, you discover you've approached a problem from a completely wrong direction -- that you've reached a programming dead end. Rather than trying to remember all the changes you've made to the object and repealing each one individually, you can use VSS to undo the checkout, thus reverting to the current VSS project version.


To do this, select the Undo Check Out command from either the Tools, SourceSafe menu or the right-click menu for an object in the database window. SourceSafe warns you that undoing a checkout will overwrite any changes to the object.


Once you've made changes to an object that you want to share with others, you must check the object back in to the VSS project. Select the Check In menu command to open the dialog box shown in Figure 6. Like the Check Out dialog box, you can view only the objects of a particular type or all objects. You can also supply a comment indicating what changes were made that will be saved along with the objects.


If you want to continue working on the objects, select the "Keep checked out" check box. This instructs VSS to check the objects in to the project on the server so other developers can retrieve them, along with your changes, but leaves the objects checked out to you so you can continue editing them.


Before checking in the objects, you can view the differences between them and those currently in the VSS project on the server by clicking the Differences button. For application objects, this displays the VSS Differences dialog, which I'll discuss later.


Finally, if you're working with multiple developers on the same database project, you'll occasionally want to merge changes made by other developers into your copy of the database. To do this, select an object in the database window and activate the Get Latest Version command from the SourceSafe or right-click menu. This replaces the current version of the object with the one stored in the VSS database.


Merging changes by multiple developers

Visual SourceSafe allows multiple users to check out any text file from a VSS project. This lets different developers make changes to an object at the same time. While it's preferable to restrict checkouts to a single developer, sometimes multiple checkouts are necessary due to the size and complexity of a source file. Access supports multiple checkouts for code modules only. To enable multiple checkouts you must run the VSS Administrator application and select this option from the Options dialog box.


When checking in source files that have been checked out by multiple developers, VSS must merge the file being checked in with the one in the VSS database. Merging involves integrating changes made to the local copy with the current version in the VSS project. VSS uses a rather sophisticated pattern-matching algorithm to process the merge and to spot conflicts. Therefore, VSS can often perform the merge automatically if there are no direct conflicts between files. For example, if only new procedures are added to a code module, VSS adds the new procedures to the VSS database copy of the file. Changes to the same line of code, or any other change that short-circuits the pattern-matching algorithm, will cause conflicts that VSS can't resolve on its own.


When conflicts are detected, VSS displays its Visual Merge dialog box, shown in Figure 7. This dialog box contains three panes. The upper-left and upper-right panes show the current VSS project version of the file alongside the local version. The lower pane shows the results of the merge. It's up to you to sort out the differences and, where there are conflicts, to pick a "winner."


To make the process easier, VSS color-codes each conflict to indicate which lines have been added, changed, or removed. You can use the toolbar to move quickly from one conflict to the next. For each conflict, you have the option of discarding your changes and reverting to the VSS database version, discarding the version in the VSS database, or accepting both sets of changes where applicable. For example, if two new, different procedures exist in the two versions, you could decide to accept both. On the other hand, if there are conflicts within a single line of code, you must pick one winner. To select a winner, simply click on it in the appropriate upper pane. You can also right-click on a conflict and select from menu commands that accept the change, discard it, or apply both.


When you're done merging the two files, close the Merge dialog box. VSS prompts you to confirm that the merge was successful. If you were able to resolve all the conflicts, click Yes to check in the file. If you weren't -- perhaps because you needed to check with another developer -- click No to keep the object checked out.


Histories and differences

As you begin to save numerous versions of an object, you may want to look back at previous incarnations. This is called viewing the history of an object. VSS makes this information available via the History dialog box shown in Figure 8. You access this dialog box by selecting the Show History command on the Tools, SourceSafe menu.


The dialog box lists the version number, the user name of the developer who checked in that version, the date and time of the check-in, and the action that initiated the new version. You can view a version's details, such as any comments or labels associated with it, by clicking the Details button. This opens a secondary dialog box.


You can also take action from this dialog box. For example, you can retrieve any version of the file into your copy of the database by clicking the Get button. You can also view the differences between your local copy and any other version by selecting that version and clicking the Diff button. If there are differences between the two versions, VSS displays the Differences dialog box, which is similar to the Visual Merge dialog box. You can use the toolbar to move from difference to difference, but you can't make changes using this dialog box. You'll have to manually note the differences and change them in your local copy.


In addition to viewing differences between your local copy of an object and a version in the history list, you can view the differences between any two versions in the VSS database. Just select the first version you want to examine, hold down the Ctrl key, and click on another version. Now click the Diff button.


If you discover a critical error in your local version of an object and it's impractical to correct it manually, you can use the History dialog box to restore a prior version. Simply select the version you want to restore (click the View button to examine it if you're unsure) and click the Rollback button. Restoring a version of an object permanently deletes all subsequent versions, so use care when doing this. VSS doesn't, however, merge the restored version into your local copy of the database. Your database will still contain the newer, yet flawed, version. I recommend, therefore, that after performing a rollback, you immediately get the latest version.


Sharing and branching

If you've been developing applications for a while, you probably have modules of source code that you share between projects. This is an especially useful technique for utility functions that aren't application dependent. VSS also supports sharing of source files among projects. When you share a source file using VSS, only one copy of the file actually exists in the VSS database; however, you can include it in as many projects as you like. Whenever you make changes to the file in one project and check it back in to the VSS database, all other projects that use the file are affected. The next time a get is performed in those projects, your changes will appear.


When using source code control integration in Access, you can select source files from any other project for inclusion in the current database. You do this using the Share With dialog box shown in Figure 9. To access this dialog box, select the Share Objects command from the Tools, SourceSafe menu.


Use the Projects list to view source files in a particular project. You can select any project except the current one. If you select the current project, no files are displayed in the "Files to share" list. This list is a multiple-selection list box, meaning you can choose one or more files to share from this list. After making your selections, click the Share button. This marks the files as shared in the VSS database, adds them to the current project, and copies the latest version of each to your local database. You can then work with them as you would any other source file.


The opposite of sharing is branching. Branching is performed on a shared file and breaks the link between projects using the file. It creates a new, native copy of the file in the project where the branch is created. From that point forward, changes in the new copy don't affect other projects that use the file.


You can't perform branching directly through Access. You must run Visual SourceSafe itself. To do this, select Run SourceSafe from the Tools, SourceSafe menu. From the VSS Explorer window, select the project where you want to create the branch and select the shared file. Select Branch from the SourceSafe menu or click the Branch Files toolbar button.


Is that it?

At this point you might be asking, "Is that all I need to know about source code control in Access 97?" Of course the answer is no. Integrating any type of source code control into your current programming practices requires effort and discipline on everyone's part. You'll need to relearn how to be productive now that you must check out each object before changing it. You'll also need to learn how not to step over each other's changes if you decide to use VSS' multiple checkout feature. Using an SCC program requires enhanced cooperation and communication.


Finally, using the SCC add-in for Access 97 isn't the only way to integrate source code control into your project. Visual SourceSafe 5.0 features an Automation interface that exposes projects and source files using objects, properties, and methods. You can write your own tools to extract utility files stored in a VSS database. If this interests you, check out the source code control chapter in the Access 97 Developer's Handbook or the VSS Web site at