Source Code Control in Microsoft Access 97

Chris Capossela
Microsoft Corporation

February 1997

Overview of Source Code Control Integration

The Microsoft® Office 97, Developer Edition Tools (ODE Tools) provides a software component that integrates various source code control products (including Microsoft Visual SourceSafe™) into Microsoft Access. Because this component writes to the standard source code control interface, ODE Tools users can use any source code control product that supports this interface. (The Visual SourceSafe team designed this interface and has shared it with other companies for incorporation into their products.)

By integrating source code control into Microsoft Access 97, you can now work with a team of developers to build a Microsoft Access application. You can:

The software component communicates with both Microsoft Access 97 and the source code control application. While you work with Microsoft Access 97, the source code control component steps in at various points and lets you do things like add, check out, or check in Microsoft Access objects to the source code control project. Source code control integration with Microsoft Access is very similar to source code control integration with Microsoft Visual Basic®.

The Basic Model

A group of developers work together to build a database application. The master copies of all the Microsoft Access database objects are kept in a Visual SourceSafe project. Each developer works on a Microsoft Access database on his or her hard disk drive. Checking out objects from the Visual SourceSafe version control system copies them from the Visual SourceSafe project into a developer's local database. Checking in objects copies them back from the developer's local database into the Visual SourceSafe project. In other words, this is not a model based on multiple users working together on the same Microsoft Access database at the same time.

Checking out an object gives you the ability to make changes to that object. Checking in an object gives other developers the chance to get your changes to that object. When you check out an object, no other developer can check out that object until you check it back in.

The one exception to this rule is modules. You can check out a module that other developers currently have checked out. When you check the module back in, you can merge the changes that other developers have made to the module since you checked it out.

How Are Microsoft Access Database Objects Stored in Visual SourceSafe?

Each Microsoft Access query, form, report, macro, and module is stored as a text file in Visual SourceSafe. (For convenience, this document often refers to Visual SourceSafe as the source code control product being used. Keep in mind that you can use any source code control product that supports the source code control interface. Each source code control product has its own features and user interface, so the integration with Microsoft Access will differ accordingly.)

When you get or check out one of these objects, the source code control component copies the text file from the Visual SourceSafe project to some temporary location on your machine. Microsoft Access then imports the text file into your Microsoft Access database, turning it into a Microsoft Access object (query, form, report, macro, or module).

Similarly, when you add a Microsoft Access object to Visual SourceSafe, Microsoft Access exports the object to a temporary text file and copies that text file into the Visual SourceSafe project. Text files are constantly being created and cleaned up without your being aware of it.

All the other Microsoft Access objects, known as the Data and Misc. objects, (tables, relationships, command bars, database properties, startup properties, import/export specifications, Visual Basic for Applications project references, Visual Basic for Applications project name, conditional compilation arguments) are stored in one .acb file that Visual SourceSafe treats as a binary file.

What Products Do I Need for This Feature?

The software component that integrates source code control into Microsoft Access 97 is included with Microsoft Office 97, Developer Edition (ODE). Microsoft Office 97, Developer Edition does not include Visual SourceSafe.

Therefore, in order to get multideveloper support, you need to license Microsoft Office 97, Developer Edition and Microsoft Visual SourceSafe version 4.0 or later.

Source Code Control Commands in Microsoft Access 97

When you install the source code control component, it adds the following commands to the SourceSafe submenu on the Microsoft Access Tools menu:

The following sections give a brief explanation of what each command does.

Create Database From SourceSafe Project

You use this command to create a new Microsoft Access database from a Visual SourceSafe project that someone else has already created. This is how you enlist in a multideveloper project that someone has already set up. This command displays the Open SourceSafe Project dialog box, which you use to select an existing Visual SourceSafe project and the folder in which to place the new Microsoft Access database. Visual SourceSafe names and creates a new Microsoft Access database in that folder and populates the database with all the Microsoft Access objects in the Visual SourceSafe project. Finally, it opens the new database (exclusively, not shared) in Microsoft Access.

Add Database To SourceSafe

You use this command to put a Microsoft Access database under source code control. This command displays a dialog box that lets you select the Visual SourceSafe project that you want to put the current database into. It then adds properties to the Microsoft Access database (and to particular Microsoft Access objects) to track the database under source code control, to track the location of the Visual SourceSafe project for this database, and to track versions of particular objects in the database.

Next, the source code control component brings up the Add Objects To SourceSafe dialog box that lets you choose which database objects are added to source code control. By default, all the objects are selected; just click OK to add everything in the database to source code control.

When you click OK, Microsoft Access alerts you that it needs to shut down the database and reopen it exclusively. Then, all the selected Microsoft Access queries, forms, reports, macros, and modules are written out as text files to a special folder on your machine. Next, Microsoft Access creates a binary file in this special folder (with the same name as your database and an .acb extension) and exports all the Data and Misc. objects (tables, relationships, command bars, import/export specifications, database properties, Visual Basic for Applications project references, the Visual Basic for Applications project name, and conditional compilation arguments) from your database to the binary file. Microsoft Access then checks the text files and the binary file into the Visual SourceSafe project you specified. When the database is closed, all the temporary objects in the special folder are deleted.

You have now placed your database under source code control. The only visible change is that the icons in the Database window for tables, queries, forms, reports, macros, and modules have additional symbols to indicate that each object is under source code control, checked out to you, or checked out to someone else.

The following table shows the file name extensions for the files that are added to Visual SourceSafe:

Object Extension
Queries .acq
Forms .acf
Reports .acr
Macros .acs
Modules .acm
Data and Misc. objects (tables, relationships, etc.) .acb

Get Latest Version, Check Out, Check In, and Undo Check Out

These commands all display a dialog box that contains a list box with all the objects in the Visual SourceSafe project that this command applies to. For instance, if you choose the Check In command, the list box is filled with all the objects that you have checked out.

Select the items that apply for the chosen command and then click OK. Depending on what you had selected before choosing the command, certain items in the list box may already be selected.

In the case of the Get Latest Version and Check Out commands, some objects may appear in the list box that are not actually part of your database. These are objects that other users have put in Visual SourceSafe, which you have not gotten yet. (The items that are displayed in this dialog box are always objects in a Microsoft Access database. If you want to check in a Microsoft Word document, you can do so through the source code control provider, not through Microsoft Access directly.)

When you select the Get Latest Version, Check Out, or Undo Check Out commands, the object on your machine may be a different version than the object in the Visual SourceSafe project. If so, the objects are synchronized as the object is copied from the Visual SourceSafe project to a special folder on your machine and then imported into your database, overwriting your version of it.

Add Objects To SourceSafe

This command displays a dialog box with a list of the objects that are currently in your Microsoft Access database, but are not under source code control. You use this dialog box to add these objects to Visual SourceSafe.

Options

This command displays a dialog box to set options about the way the source code control component behaves.

Refresh Object Status

Whenever you're working with a Microsoft Access database that's under source code control, the object icons in the Database window show you the state of each object. There's an icon to show that an object is under source code control, a different icon to show that you have the object checked out, and a third icon to show that the object is checked out by someone else. The Refresh Object Status command simply refreshes the icons in the Database window for the tab you are looking at.

Other Commands

All other commands call Visual SourceSafe directly. Visual SourceSafe does exactly what it would do in the Visual SourceSafe user interface.

The Run SourceSafe command runs Visual SourceSafe Explorer. If Visual SourceSafe is already running, it is brought to the front of your screen, instead of being relaunched.

The Show Differences command compares the local copy of an object to the one in the Visual SourceSafe project and shows how they differ (in a textual display). This command does not work for the Data and Misc. objects, which are tracked in the binary .acb file.

The SourceSafe Properties command displays Visual SourceSafe object properties such as comments, check-out status, links to other projects, and so on.

The Share Objects command takes objects from other Visual SourceSafe projects, shares them into the current project, and then gets them into the database.

The Show History command displays a dialog box that shows you the history of the currently selected object or the history of the entire project. To see the history of an entire project, hold down the SHIFT key while you are clicking the Show Differences command.

The details of what these commands do vary between different source code control providers and aren't covered by this document.

Changes to Microsoft Access Behavior

Using the Compact Database Command

In order to take a database that's under source code control and deliver it to a user, you need a way to cut the database's ties to source code control. When you compact a database that's under source code control, after you choose the source and destination databases, Microsoft Access 97 gives you the option to remove the database from source code control through a message box.

To remove the database from source code control, Microsoft Access just removes the Visual SourceSafe properties from the Microsoft Access database.

Opening a Database

When you open a database that is under source code control, Microsoft Access opens the database exclusively regardless of whether the Exclusive check box is selected. This is similar to what Microsoft Access does when you open a database that's marked as a read-only file. Microsoft Access needs to know that this database is only being used by one developer, so it doesn't have to deal with developer A browsing a table while developer B gets the latest version of all the tables.

When you open a database that is under source code control, depending on the source code control option setting for automatically refreshing objects upon opening, the objects are either automatically refreshed or you are asked if you want to refresh the objects.

Closing a Database

Depending on the source code control option for checking in objects when closing a database, the source code control component either displays the Check In dialog box or asks if you want to check in the objects that are checked out. The source code control component also deletes any temporary text or binary files that it has created.

Opening an Object in Design View

When you open an object in Design view, Microsoft Access checks to see if the object is checked out to you. If so, the object is displayed in Design view.

If the object is not checked out to any user, the source code control component asks if you want to check out the object. If you click No, the object is displayed in read-only Design view (your changes can't be saved).

If the object is checked out to another user, and the object is a module, the source code control component displays a message telling you another user has the object checked out and gives you the option to also check the module out. Any changes that you and the other user make will need to be merged later by using the source code control application.

Note that if you are browsing an object that isn't checked out to you, and then you switch to Design view, Microsoft Access displays a message telling you that you can only go into read-only Design view. If you want to make changes to the object, you need to close it before going into Design view (at which point you'll have the ability to check it out).

Saving a New Object or Using "Save As" to Save an Existing Object

You can create and save a new query, form, report, macro, or module. Depending on the source code control option for automatically placing an object under source code control, the source code control component either displays the Add Files dialog box with just the new item selected or asks you if you want to place the object under source code control.

Renaming an Existing Object

When you rename an object, the source code control component updates the name in the Visual SourceSafe project. The rename fails if the object is checked out by someone else.

You must check out the Data and Misc. objects in order to rename any tables, relationships, command bars, import/export specifications, and so on.

Deleting an Existing Object

When you delete an object, depending on the source code control option for automatically deleting an object from the source code control project, the source code control component determines whether a message should be displayed that asks you if you want to delete the object from the Visual SourceSafe project.

You must check out the Data and Misc. objects in order to delete any tables, relationships, command bars, import/export specifications, and so on.

Objects Microsoft Access Puts Under Source Code Control

The following table shows which objects are put under source code control and which aren't. Objects in italics are the Data and Misc. objects, which are not stored in Visual SourceSafe as individual text files but are stored together in one binary file with an .acb extension.

Objects under source code control Objects not under source code control
Queries Security
Forms Replicated databases
Reports Enabled databases
Macros
Modules
Tables (table definitions and data)
Relationships
Command bars
Database properties
Import/export specifications
Visual Basic project references
Visual Basic project name
Conditional compilation arguments

The following sections summarize the objects that Microsoft Access puts under source code control and explain any variations from the norm for the particular object.

Queries

When you put a database under source code control, queries are one of the items that Microsoft Access puts under source code control.

Each query is stored as an independent object in Visual SourceSafe, allowing you to check out just the query or queries you want. You can also add a local query to your database and later add it to source code control for other developers to use. Like most of the Microsoft Access objects, a query can only be checked out by one user at a time.

Queries and Relationships

Queries can be part of nonenforced relationships in the Relationship window. When you put your database under source code control, Microsoft Access ignores relationships involving queries. If Microsoft Access didn't ignore them, it couldn't work with queries as individual text files. Microsoft Access would have to lump queries into the Data and Misc. objects. In order to modify a query or add a query, you'd have to check out all the tables, queries, relationships, command bars, database properties, import/export specifications, and so on, which would stop any other developer from touching any query until you checked the Data and Misc. objects back in. This is too restrictive for most Microsoft Access developers. As a result, if you want multideveloper support for your Microsoft Access database, you can't put relationships involving queries under source code control.

Changing the design of a query while browsing it

When you design a query, the source code control component asks you if you want to check out the query. When you browse a query, you're not asked this question. However, you can change various design properties of a query when you browse it: column width, font size, default sort order, and so on.

If you browse a query that isn't checked out to you and make these changes at browse time, you have no way of checking in your changes to the Visual SourceSafe project. The changes are not saved when you close the query. You must manually check out the query before browsing it and making design changes.

Forms, Reports, and Macros

When you put a database under source code control, forms, reports, and macros are items that Microsoft Access puts under source code control.

Each form, report, or macro is stored as an independent object in Visual SourceSafe, allowing you to check out just the form, report, or macro you want. You can also add a local form, report, or macro to your database and later add it to source code control for other developers to use. Like most Microsoft Access objects, a form, report, or macro can only be checked out by one user at a time.

Forms have the same restrictions pertaining to design changes at browse time as queries do.

Modules

When you put a database under source code control, modules are one of the items that Microsoft Access puts under source code control.

Each module is stored as an independent object in Visual SourceSafe, allowing you to check out just the module or modules you want. You can also add a local module to your database and later add it to source code control for other developers to use. Unlike most Microsoft Access objects, a module can be checked out by more than one user at a time. This means multiple developers can check out the same module at the same time. Chris can add comments to his procedures while Sally adds new procedures to the same module. If Chris checks in his module first, his check-in succeeds without incident. When Sally checks in her version of the module, if there are conflicts, she's told that someone else has made changes to the same parts of the module and that she needs to go into the module and resolve the conflicts by hand. Visual SourceSafe 5.0 makes this easy with the Visual Merge dialog box.

Note: By default, Visual SourceSafe is set to allow only one user to check out a file at a time. To change this, open the Visual SourceSafe Administrator tool, and select the Allow Multiple Checkouts check box in the Options dialog box.

Data and Misc. Objects

When you put a database under source code control, a number of database objects are put into the Visual SourceSafe project lumped together inside of a binary .acb file, rather than being stored as independent text files. The objects that are lumped into this .acb file are:

You can't check out just one of these objects--you check them all out (by checking out the Data and Misc. objects), or you don't check any of them out. Only one user can check out the Data and Misc. objects at one time.

If you don't have the Data and Misc. objects checked out, you can add a new local table (or relationship, command bar, and so on) to your database, but you cannot add that table or other object to source code control later on. As soon as you synchronize to the version under source code control, the local object will be deleted with no warning.

Creating Tables

There are multiple ways to create a new table: click the New button in the Database window, copy and paste an existing table in the Database window, import a table from another database, or run a make-table query.

Creating a table from the Database window (new or copy/paste)

When you try to create a new table, the source code control component checks to see whether you have the Data and Misc. objects checked out. If you do, you're able to create the table.

If no user has this object checked out, the source code control component asks whether you want to check out the object.

If the Data and Misc. objects is checked out to another user, the source code control component displays a message telling you that if you create a new table, you won't be able to add it to source code control.

Creating a new table by importing

When you're working with a database that is under source code control and you display the Import dialog box, Microsoft Access checks to see whether the Data and Misc. objects is checked out to you. If it is not, then Microsoft Access gives you the message: "In order to import tables, relationships, command bars, or import/export specifications, you must have the Data and Misc. objects checked out." In addition, the Tables tab and the check boxes for importing relationships, command bars, and import/export specifications are all unavailable.

Creating a new table through make-table queries

When you create a new table by using a make-table query, Microsoft Access does nothing to ensure that you have the Data and Misc. objects checked out. This is similar to programmatically creating a table. The new table will be created, but if you don't have the Data and Misc. objects checked out, the next time you run the Get Latest Version command, your new table is deleted.

Browsing tables, queries, forms

When browsing a table, query, or form, you can enter data into tables in the database. If you don't have the tables checked out, this data will be overwritten when you get the latest version of the Data and Misc. objects.

Relationships

When you click the Relationships command on the Tools menu, this is your signal to the source code control component that you want to go into design mode for a relationship. Microsoft Access prompts you to check out the Data and Misc. objects, just as it does when you design a table.

User-Defined command bars

When you click the Toolbars command on the View menu, this is your signal to the source code control component that you want to go into design mode for a toolbar. Microsoft Access prompts you to check out the Data and Misc. objects, just as it does when you design a table.

Database properties

When you click the Startup command on the Tools menu or the Database Properties command on the File menu, this is your signal to the source code control component that you want to go into design mode for the database properties. Microsoft Access prompts you to check out the Data and Misc. objects, just as it does when you design a table.

Visual Basic for Applications project references

When you click the References command on the Tools menu, this is your signal to the source code control component that you want to go into design mode for the Visual Basic for Applications project references. Microsoft Access prompts you to check out the Data and Misc. objects, just as it does when you design a table.

Limitations of Source Code Control in Microsoft Access 97

Security

You can't work on user and group permissions when you're working with a database that's under source code control. The User and Group Permissions command on the Security menu is unavailable whenever the active database is under source code control.

To add a secured database to source code control, you must have full permissions on all objects in the database. When you add a secured database to source code control, the user and group permissions that existed are stripped away. You need to reapply your security settings after removing the database from source code control.

Replication

You cannot put replicated databases under source code control. Microsoft Access disables the appropriate source code control commands when the active database is a replicated database. Likewise, when the active database is under source code control, the replication commands are unavailable.

You can, however, import objects into a Design Master from a database that is under source code control. This allows you to make changes to your database under source code control and then bring those changed or new objects into a Design Master and replicate them to other databases in the replica set.

Programmatic Changes through Microsoft Jet

Because tables, queries, and relationships can be modified by using Data Access Objects (DAO), it is possible to make changes to table, query, and relationship definitions even when you don't have the object checked out, thereby circumventing source code control. If you circumvent source code control in this manner, your changes will be lost when you synchronize that object with the one stored in Visual SourceSafe. This behavior is not recommended.

Enabled Databases

An enabled database is a Microsoft Access 2.0 or Microsoft Access 95 database that you open in Microsoft Access 97 without converting it to Microsoft Access 97 format. Microsoft Access cannot put enabled databases under source code control. Microsoft Access disables the appropriate source code control commands when the active database is an enabled database.

File Operations on the Local Database

You can't move, rename, or copy your local version of the database and continue working with it under source code control. If you want to move the database to a different location, you need to check in all your objects, delete the local database, and then re-create it from Visual SourceSafe in the new location.

Questions and Answers

Why do we store tables, relationships, and so forth in a binary .acb file? Why can't we treat these as independent text files like queries, forms, reports, macros, and modules?

There are a few different reasons:

Can I take my laptop home and work on a database that's under source code control if I can't run Visual SourceSafe?

Yes. Just check out the items you want to work on before going home.

What if someone checks out some objects and then he or she leaves on vacation?

The administrator of the Visual SourceSafe project can go to the Visual SourceSafe Explorer and undo the checkout of the items that the vacationing employee forget to check in. This is also the solution to use if, for example, a user checks out some objects and then his or her hard disk drive crashes.

Note that if you are using Visual SourceSafe 5.0, you can't do this just by running the Visual SourceSafe Explorer. When a Microsoft Access database is added to a Visual SourceSafe 5.0 project, Microsoft Access adds a setting to the Srcsafe.ini file (if Microsoft Access can't write to the Srcsafe.ini file to add the entry, it attempts to write to the user's Visual SourceSafe Ss.ini file) that looks something like this:

[$/Northwind]
Disable_UI = Yes

This setting disables the Check Out, Check In, Undo Check Out, and Get Latest Version commands from the Visual SourceSafe 5.0 Explorer. When you try to use one of those commands, you see this error message:

This is an integration only project; getting files is not allowed from the SourceSafe Explorer.

To enable these commands in the Visual SourceSafe Explorer, delete the "Disable_UI" line from the Srcsafe.ini file and reopen the Visual SourceSafe Explorer. You'll then be able to perform the operation. When you reopen the Microsoft Access database, Microsoft Access will add the "Disable_Ui = Yes" line back to the Srcsafe.ini file.

How do I set up Visual SourceSafe to work with Microsoft Access?

License one copy of Visual SourceSafe for each developer on your team. If you have five developers, acquire five licenses for the product. Run Setup.exe and click Server Setup to install Visual SourceSafe on a network drive that all your developers can get to.

Next, run the Visual SourceSafe Administrator program from this network location and create a user list for your five developers (if you add six names, you need to acquire another license for the product).

Next, each developer connects to the network that Visual SourceSafe is installed on and runs Netsetup.exe. This installs the client portion of Visual SourceSafe on the developer's machine and points to the central database on the network. Each developer must do this so that Microsoft Access finds the source code control provider in the Windows registry on the developer's machine. Otherwise, Microsoft Access gives you an error and none of the source code control commands are available.

Finally, license one copy of Microsoft Office 97, Developer Edition for each developer on your team. Install Microsoft Access and the ODE Tools, which both come with this product.

Is there any way to programmatically check in or check out objects?

No. The only programmatic control you have is to stop the check out prompt from coming up. If you turn the SetWarnings method off (DoCmd.SetWarnings False) before opening an object in Design view, Microsoft Access will not prompt you to check the object out. Microsoft Access will just open the object as read-only in Design view so that no design changes can be saved. There is no mechanism for programmatically checking out an object.