Source Code Control in Microsoft Access 2000

Shawn McDowell
Microsoft Corporation

Revision Date: January 1999

For the latest information, see http://msdn.microsoft.com/officedev/default.asp.

Contents

Overview of Source Code Control Integration
Source Code Control Commands in Microsoft Access 2000
Changes to Microsoft Access Behavior
Objects Microsoft Access Puts Under Source Code Control
Limitations of Source Code Control in Microsoft Access 2000
Questions and Answers

Overview of Source Code Control Integration

Microsoft® Office 2000 Developer provides the Access Source Code Control add-in that integrates source code control into Microsoft Access using Microsoft Visual SourceSafe™.

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

The Access Source Code Control add-in communicates with both Microsoft Access 2000 and the source code control application. While you work with Microsoft Access 2000, the source code control component 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

One or more users can work together to build a database application. The master copies of all 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, using the Access Source Code Control add-in, 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. A user enlisted in the SourceSafe database can create the most up-to-date version of the database from objects stored in the Visual SourceSafe project.

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. When you get or check out one of these objects, the Access Source Code Control add-in copies the text file from the Visual SourceSafe project to a working folder 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 text file in the working folder. The file is then added to the Visual SourceSafe project by the Access Source Code Control add-in.

All other Microsoft Access objects, known as the Data and Misc. objects (such as 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 binary file. See Figure 1.

Figure 1. Storing database objects in Visual SourceSafe

What Products Do I Need for This Feature?

All products necessary to use the Access Source Code Control add-in, which integrates source code control into Microsoft Access 2000, are included with Microsoft Office 2000 Developer.

At a minimum, the Microsoft Visual SourceSafe client must be installed. The Visual SourceSafe shared database server can be installed locally or on a remote machine. If the shared database server is installed on the same machine that is using the add-in, it is not necessary to install the Visual SourceSafe client.

Source Code Control Commands in Microsoft Access 2000

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 each command.

Create Database from SourceSafe Project

The Create Database from SourceSafe Project command creates a new Microsoft Access database from a Visual SourceSafe project that someone else has already created. 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. The Access Source Code Control add-in 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

Use the Add Database to SourceSafe command to put a Microsoft Access database under source code control. When you choose this command, Microsoft Access alerts you that it needs to close the database and reopen it exclusively. The add-in displays a dialog box that lets you select the Visual SourceSafe project into which you want to put the current database.

Next, the Access Source Code Control add-in brings up the Add Objects To SourceSafe dialog box that lets you choose which database objects to add to source code control. By default, all objects are selected. Click OK to add everything in the database to source code control.

Then, all selected objects are written out as text files to the working folder on your machine. During this process, Microsoft Access creates a binary file in the working folder that stores all of the Data and Misc. objects from your database. Access also creates a naming file that is used when the database is created from the Visual SourceSafe project. All files are added from the working folder to the Visual SourceSafe project you specified.

Properties are added to the Microsoft Access database and its objects. These properties are used to track the location of the Visual SourceSafe project, Visual SourceSafe database, and the status of each object.

You have now placed your database under source code control. The only visible change is that the icons in the Database window 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 Applies to Extension
Queries Databases .acq
Forms Databases and Access Projects .acf
Reports Databases and Access Projects .acr
Macros Databases and Access Projects .acs
Modules Databases and Access Projects .acm
Data and Misc. objects Databases .acb
Data and Misc. objects Access Projects .acp
Naming file Databases and Access Projects .acn

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

The Get Latest Version, Check Out, Check In, and Undo Check Out 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 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 part of your local database. These are objects that other users have added to Visual SourceSafe. To include these objects in your local database, use the Get Latest Version or Check Out commands.

Note   Items displayed in this dialog box are always objects in a Microsoft Access database. If you want to check in a Microsoft Word document, 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 Access Source Code Control add-in synchronizes the objects by copying the text file for the object from the Visual SourceSafe project to the working folder on your machine and then imports it into your database, overwriting your version of the object.

Add Objects to SourceSafe

The Add Objects to SourceSafe command displays a dialog box with a list of the objects currently in your Microsoft Access database, but not under source code control. Use this dialog box to add these objects to Visual SourceSafe.

Options

The Options command displays a dialog box to set options about the way the Access Source Code Control add-in behaves. The options affect behavior when a database is opened and closed, and when objects under source code control are created and deleted.

Refresh Object Status

When you are working with a Microsoft Access database that is under source code control, the object icons in the Database window show you the state of each object. There is one 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 with the most up-to-date information.

Other Commands

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

The Run SourceSafe command opens the 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 versioned as a binary file.

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 the entire project, hold down the SHIFT key while clicking Show Differences.

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 makes them part of the database.

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

Changes to Microsoft Access Behavior

Using the Compact Database Command

In order to take a database that is 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 is under source code control, Microsoft Access 2000 prompts you to remove the database from source code control.

To remove the database from source code control, Microsoft Access simply removes the Visual SourceSafe properties from the Microsoft Access database and its objects.

Opening a Database

When you open a database that is under source code control, Microsoft Access opens the database exclusively, regardless of options in the Open Database dialog box. This is similar to what Microsoft Access does when you open a database that is marked as a read-only file. Microsoft Access needs to know that this database is only being used by one developer, so it does not 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.

With previous versions of the Access Source Code Control add-in, the working folder was deleted when the database was closed. For better performance, the working folder is now left on your machine with Access 2000.

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 cannot 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 out the module. Any changes that you and the other user make will need to be merged when the object is checked in. In most cases, an automatic merge will occur, and no action is necessary. In more complex merge scenarios, consult the Visual SourceSafe documentation.

If you are browsing an object that is not checked out to you and then switch to Design view, Microsoft Access displays a message that says 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 will 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 if 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 lists those objects that are put under source code control and those that are not. 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.

Objects under source code control Applies to
Queries Databases
Forms Databases and Access Projects
Reports Databases and Access Projects
Macros Databases and Access Projects
Modules Databases and Access Projects
Tables (table definitions and data) Databases
Relationships Databases
Command bars Databases and Access Projects
Database properties Databases and Access Projects
Import/export specifications Databases
Visual Basic for Applications project references Databases and Access Projects
Visual Basic for Applications project name Databases and Access Projects
Conditional compilation arguments Databases and Access Projects
Connection String information Access Projects
Data Access Page Links Databases and Access Projects
Objects not under source code control Applies to
Security Databases
Replicated databases Databases
Enabled databases Databases
Database object shortcuts and groups Databases and Access Projects
SQL Tables, Views, Diagrams, Stored Procedures Access Projects

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

Queries

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 did not ignore them, it could not work with queries as individual text files and would have to combine queries into the Data and Misc. objects. If the queries were combined into the Data and Misc. objects, every time you wanted to modify a query or add a query, you would have to check out all the tables, queries, relationships, command bars, database properties, import/export specifications, and so on. This would prevent 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 cannot 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 are 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 is not 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

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

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 is 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 and later make 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 a binary file, rather than being stored as independent text files. The objects that are lumped into this file are:

You cannot check out just one of these objects—you check them all out (by checking out the Data and Misc. objects), or you do not check out any of them. Only one user can check out the Data and Misc. objects at a time.

If you do not have the Data and Misc. objects checked out, you can still add a new local table (or relationship, command bar, and so on) to your database. However, as soon as you synchronize to the version under source code control, by using Get Latest Version or Check Out, the local object will be deleted with no warning.

Creating tables

The following 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 if you have the Data and Misc. objects checked out. If you do, you are able to create the table.

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

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

Creating a new table by importing - When you are working with a database that is under source code control and you display the Import dialog box, Microsoft Access checks to see if the Data and Misc. objects are checked out to you. If 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 using make-table queries - When you create a new table 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 do not have the Data and Misc. objects checked out, the next time you run the Get Latest Version or Check Out command, your new table will be deleted.

Browsing tables, queries, forms

When browsing a table, query, form, or data access pages connected to a database under source code control, you can enter data into tables in the database. If you do not have the tables checked out, any data you have added or modified will be overwritten when you Get Latest Version or Check Out the Data and Misc. objects.

Relationships

When you click Relationships 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 like it does when you design a table.

User-defined command bars

When you click Toolbars 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 like it does when you design a table.

Database Properties

When you click Startup on the Tools menu or Database Properties 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 like it does when you design a table.

Visual Basic for Applications project References

When you click References 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 like it does when you design a table.

Connection information (for Access Projects)

In an Access Project file, you can change the connection information by choosing Connection on the File menu. Microsoft Access prompts you to check out the Data and Misc. objects, just like it does when you design a table in the database.

When you add an Access Project to source code control, only the Connection String information is stored in the Data and Misc. objects. Microsoft Access does not version any of the SQL Server tables, views, database diagrams, or stored procedures. If you choose Connection on the File menu and the Data and Misc. objects are not checked out, you will be prompted just like designing a table in a database.

Data Access Page links

If your database or Access Project contains links to Data Access Pages, it is important to note that the Access Source Code Control add-in is only storing the link to the page and not to the HTML file. When you check out the Data and Misc. objects, you check out all Data Access Page links at the same time. When you design or create a new Data Access Page from Microsoft Access, you will be prompted to check out the Data and Misc. objects, if they are not already checked out. If you choose no, you will be able to save your Data Access Page to disk, but you will not be able to save the links to any new pages.

Limitations of Source Code Control in Microsoft Access 2000

Security

You cannot work on user and group permissions when you are working with a database that is under source code control. The User and Group Permissions command on the Security menu is unavailable when 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 deleted. 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 (DAOs), it is possible to make changes to table, query, and relationship definitions even when you do not 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 any previous version database that you open in Microsoft Access 2000 without converting it to Microsoft Access 2000 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 cannot 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, and then re-create the database from Visual SourceSafe in the new location.

Questions and Answers

Why do we store tables, relationships, and so forth in a binary 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 team member forgot to check in. This is also the solution if, for example, a user checks out some objects and then his or her hard-disk drive crashes.

If you are using Visual SourceSafe 5.0 or later, you cannot do this just by running the Visual SourceSafe Explorer. When a Microsoft Access database is added to a Visual SourceSafe project, the Source Code Control add-in adds a setting to the Srcsafe.ini file (see Endnote) 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 Explorer. When you try to use one of those commands, you see an error message similar to this one:

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 will then be able to perform the operation.

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

Everything you need to set up Visual SourceSafe to work with Microsoft Access is included in Microsoft Office 2000 Developer. First, install Microsoft Access 2000 using the Microsoft Office 2000 Premium setup. Then, install the Microsoft Office 2000 Developer tools using the Microsoft Office 2000 Developer setup.

After installing the Microsoft Office 2000 Developer tools, install Visual SourceSafe 6.0, and choose Shared Database Server. Installing Visual SourceSafe to a shared network location will allow multiple developers to work on a database application.

Next, run the Visual SourceSafe Administrator program, and add user accounts for each developer working on the application.

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. If the Visual SourceSafe Shared Database Server is installed on the same machine that the Microsoft Office 2000 Developer Tools are used, there is no need to run the Visual SourceSafe network client setup.

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

There is no way to programmatically check in or check out objects using the Access object model. Visual SourceSafe provides a means for programmatically checking objects in and out, but it is not integrated with the Access Source Code Control add-in. If you do not use the add-in for check in and check out, object states and content could become out of sync. Therefore, using the Visual SourceSafe API is not recommended with Access source control projects.

Endnote

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.