Source Code Control in MS Access 97

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Chris Capossela, Microsoft Corp. Redmond, WA

November, 1996

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 the interface1.

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:

See which objects are checked out.
Check out an object to control changes to it.
Check in an object so other developers can get your changes.
View a history of changes for each object or the entire database.
Visually compare differences between versions of an object.
Version objects and revert to a previous version of an object (even deleted objects).
Merge differences in different versions of an object.
Easily add new developers to your team who sync to the latest versions of all objects.

The software component communicates with both Microsoft Access 97 and the source code control application. While you work with Microsoft Access 97, the add-in steps in at various points and lets you do things like add, check out, or check in Microsoft Access objects into the source code control project. Source code control integration with Microsoft Access is very similar to source code control integration with 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 their hard drive. Checking out objects from the Visual SourceSafe version control system copies them down from Visual SourceSafe into your database. Checking in objects copies them back out from your database into Visual SourceSafe. 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 SourceSafe2. When you do a "get" or "check out" on one of these objects, the add-in copies the text file from Visual SourceSafe 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 but you don't really know or care.

All the other Microsoft Access objects (tables, relationships, command bars, database properties, startup properties, import/export specs; Visual Basic, Applications Edition project references; Visual Basic, Applications Edition project name; conditional compilation arguments) are stored in one Microsoft Access database 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 ships in Microsoft Office 97, Developer Edition (ODE). Visual SourceSafe does not ship in Microsoft Office 97, Developer Edition.

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

Source Code Control Commands in Microsoft Access 97

When you install the source code control component, it adds the following commands to the Microsoft Access menus:

Create Database from SourceSafe Project
Add Database to SourceSafe
Get Latest Version
Check Out
Check In
Undo Check Out
Add Objects to SourceSafe
Options
Refresh Object Status
Run SourceSafe
Show History
Show Differences
SourceSafe Properties
Share Objects

This section of this document gives 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 yourself in a multideveloper project that someone has already set up. This command brings up a Visual SourceSafe dialog which you use to point to an existing Visual SourceSafe project, and choose the folder 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 Visual SourceSafe. 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 brings up a dialog that lets you select the Visual SourceSafe project that you want to put the current database into. It then adds database properties to the Microsoft Access database (and to particular Microsoft Access objects) to track that the database is under source code control, to track the location of the Visual SourceSafe project for this database, and track versions for particular objects in the database.

Next, the add-in brings up the Add Objects dialog that lets you choose what database objects should be added to source code control. By default, all the objects are selected so you just have to 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 re-open it exclusively. Then all the selected Microsoft Access queries, forms, reports, macros, and modules are written out to text files to a special folder on your machine. Next, Microsoft Access creates a new binary file in this special folder (with the same name as your database) and exports all the tables, relationships, command bars, import/export specs, database properties, Visual Basic for Applications project references, the Visual Basic for Applications project name, and conditional compilation arguments from your database to the new binary file. Then Microsoft Access 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 container for tables, queries, forms, reports, macros, and modules have additional glyphs to indicate that each object is under source code control, checked out to you, or checked out to someone else.

Here are the file extensions for the files that get added to Visual SourceSafe:

Object	Extension
queries	acq
forms	acf
reports	acr
macros	acs
modules	acm
tables, relationships, misc. objects	acb

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

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

You check the items you want to carry out the command on and then click OK. Depending on what you had selected before choosing the command, certain items in the list box may already be checked.

In the case of "get" and "check out", some objects may appear in the list box which are not actually part of your database. These are objects which other users have put in Visual SourceSafe, which you have not gotten yet3.

When you select "get", "check out", or "undo check out", the object on your machine may be a different version than the object in the source code control project. If so, the objects are synchronized by copying the object from the source code control project to a special folder on your machine and then importing the object into your database, overwriting your version of it.

Add Objects to SourceSafe

This command displays a dialog 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 to add these objects to Visual SourceSafe.

Options

This command brings up a dialog to set options about the way the add-in 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 container 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 bitmaps in the database container for the tab you are looking at.

Other Commands

All other commands call directly into Visual SourceSafe code. Visual SourceSafe does exactly what it would have done in the Visual SourceSafe user interface.

Run SourceSafe runs the Visual SourceSafe Explorer. If Visual SourceSafe is already up, it is brought to the front, not re-launched.

Show Differences compares the Microsoft Access local copy of an object to the one in Visual SourceSafe and shows how they differ (in a textual display). This command does not work for tables, relationships and the misc. other database objects that are tracked in one binary file in Visual SourceSafe.

SourceSafe Properties brings up Visual SourceSafe's "Properties" tab dialog on an object.

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

Show History brings up a dialog that lets you show the history of the currently selected object or the history of the entire project. Note that holding down the shift key when selecting this command will enable you to see the history of the entire database not just the selected object.

This document doesn't cover the details of what these commands do as it will vary between different source code control providers. Basically, these just execute the Visual SourceSafe commands.

Changes to Microsoft Access Behavior

The "Compact" 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 via 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 or not the exclusive check box was checked. This is akin 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 does a get on 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 on open, either the objects are automatically refreshed without prompting, or you are prompted if you want to refresh objects.

Closing a Database

Depending on the source code control option for checking in objects when closing a database, the add-in displays the "Check In" dialog, or prompts you asking if you want to check in the objects that are checked out. The add-in 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 if the object is checked out to you. If so, you're taken into Design view.

If the object is not checked out to any user, the add-in asks if you want to check out the object. If you answer no, you are taken into 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 add-in displays an alert 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 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 an alert 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 down before going into design view (at which point you'll have the ability to check it out).

Saving a New Object or "Save As" of an Existing Object

You 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 add-in displays the "Add Files" dialog with just the new item checked, or prompts you asking if you want to place the object under source code control.

Renaming an Existing Object

When you rename an object, the add-in updates the name in the source code control 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, command bars, import/export spec, etc.

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 add-in determines if an alert should be displayed asking if you want to delete the object from the source code control database.

You must check out the Data and Misc. Objects in order to delete any tables, relationships, command bars, import/export specs, etc.

Objects Microsoft Access Puts Under Source Code Control

This table shows which objects are put under source code control and which aren't. Objects in italics are not stored in Visual SourceSafe as individual text files but are stored together in one binary file.

Under source code control	Not under source code control
queries	security
forms	replicated databases
reports	enabled databases
macros
modules
tables (table defs and data)
relationships
command bars
database properties
import/export specs
vba project references
vba project name
conditional compilation arguments

This section of the document goes through the objects that Microsoft Access puts under source code control and explains 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(s) 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 non-enforced 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 them into the "Data and Misc. Objects." So 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, imex specs, etc. stopping any other developer from touching any query until you checked this object 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 in Query Browse

When you design a query, the source code control add-in 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, etc.

If you browse a query that isn't checked out to you and then you 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 check out the query manually 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(s), report(s), or macro(s) 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 of the Microsoft Access objects, a form, report, or macro can only be checked out by one user at a time.

Forms have the same restrictions with 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 modules(s) 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 of the 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 be adding 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 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.

Note that by default Visual SourceSafe is set to only allow one user to check out a file at one time. To change this, go into the Visual SourceSafe admin tool and under the Options dialog, check the "Allow Multiple Checkouts" checkbox.

Data and Misc. Objects

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

Tables (both data and the table definition)
Relationships
User-defined command bars
Import export specs
Database properties, startup properties
Visual Basic for Applications project references
The Visual Basic for Applications project name
Conditional compilation arguments

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 this object checked out, you can add a new "local" table (or relationship, command bar, etc.) to your database but you cannot add that table to source code control later on. As soon as you synch to the version under source code control this local table 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 add-in checks to see if 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 add-in asks if you want to check out the object.

If the "Data and Misc. Objects" are checked out to another user, the add-in displays an alert 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 bring up the Import dialog, Microsoft Access checks to see if the Data and Misc. Objects is checked out to you.

If it is not, then Microsoft Access gives you an alert: "In order to import tables, relationships, command bars, or import/export specs 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 specs are all disabled.

Creating a New Table through 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 akin 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 do a get, 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 get overwritten when you get the latest version of the "Data and Misc. Objects."

Relationships

When you choose the Tools/Relationships command, this is your signal to the add-in that you want to go into design mode on 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 choose the View/Toolbars command, this is your signal to the add-in that you want to go into design mode on 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 choose the Tools/Startup command or the File/Database Properties command, this is your signal to the add-in that you want to go into design mode on 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 choose the Tools/References command, this is your signal to the add-in that you want to go into design mode on 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.

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" menu item is disabled 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 the objects in the database. When you add the database to source code control, the user and group permissions that existed are stripped away. You need to re-apply your security settings after removing the database from source code control.

Replication

You cannot put any replicated database 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 disabled.

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

Programmatic Changes through Jet

Because tables, queries, and relationships can be modified through DAO, it is possible to make changes to table, query, and relationship definitions even when you don't have the object checked out, 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 for Windows® 95 database that you open in Microsoft Access 97 without converting to the 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 recreate it from Visual SourceSafe in the new location.

Questions and Answers

Why do we store tables, relationships, etc. in a Microsoft Access database file? Why can't we treat these as independent text files like queries, forms, reports, macros and modules?

There are a few different reasons:

Tables and relationships are dependent items -- you need to work on them in tandem. If Microsoft Access treats them as independent objects, you can very easily get yourself into a state where your database schema is inconsistent and Microsoft Access has no good way to reconcile the schema. This leads to complex rules for checking in and out tables and relationships. Even with these rules, its trivial to come up with an example where you get your schema in an inconsistent state with no good way to reconcile.
Versioning data is made more complex thanks to referential integrity. Microsoft Access would need a set of complex rules for checking out a table. As mentioned above, these rules don't guarantee things will always work -- you can get yourself into trouble.
Each additional object (tables, relationships, command bars, database props) is additional development and test work. By storing all these things in a binary file you get a lot of functionality for a lot less work.

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 they leave on vacation?

The "admin" of the Visual SourceSafe project can go to the Visual SourceSafe explorer and uncheck out the items that the vacationing employee forget to check in. This is also the solution to use if a user checks out some objects and then their hard drive crashes (or something else awful happens to their machine).

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 Visual SourceSafe 5.0, Microsoft Access adds an 'ini' setting to the srcsafe.ini4 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 one of those operations, you see an 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 just delete the "Disable_UI" line from the srcsafe.ini and reopen the Visual SourceSafe Explorer. Then you'll 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.

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 liceses of the product. Run "setup.exe" and choose "Server Setup" to install Visual SourceSafe on a network drive which all your developers can get to.

Next, run the Visual SourceSafe Admin program from this network location and create your user list for your five developers (if you add six names you need to buy another license of 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 their machine and points back up to the central database on the network. Each developer must do this so that Microsoft Access and will find the source code control provider in the registry on the developer's machine. Otherwise, Microsoft Access will give you an error and none of the source code control commands will be 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 SetWarnings off 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 read only in design view so no design changes can be saved. There is no mechanism to programmatically check out an object.

1 The SourceSafe team designed these interfaces and has shared them with other companies to incorporate in their products.

2 For clarity 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 interfaces. Each source code control product has its own features and user interface so the integration with Microsoft Access will differ accordingly.

3 The items that show in this dialog are always objects in a Microsoft Access database. If you want to check in a Word document you can do this through the source code control provider, not through Microsoft Access directly.

4 If Microsoft Access can't write to srcsafe.ini to add the entry, it attempts to write to the user's Visual SourceSafe ss.ini file.