Chris Capossela
Microsoft Corporation
November, 1996
Overview of Source Code Control Integration
Source Code Control Commands in Microsoft Access 97
Changes to Microsoft Access Behavior
Objects Microsoft Access Puts Under Source Code Control
Limitations of Source Code Control in Microsoft Access 97
Questions and Answers
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 interface. (The SourceSafe team designed these interfaces and has shared them with other companies to incorporate in 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 add-in steps in at various points and lets you do things like add, check out, or check in Microsoft Access objects in the source code control project. Source code control integration with Microsoft Access is very similar to source code control integration with Visual Basic®.
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.
Each Microsoft Access query, form, report, macro, and module is stored as a text file in Visual SourceSafe. (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.) 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.
Figure 1. Visual SourceSafe and temporary projects
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.
When you install the source code control component, it adds the following commands to the Microsoft Access menus:
This section gives a brief explanation of what each command does.
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 that you use to point to an existing Visual SourceSafe project and choose 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 Visual SourceSafe. Finally, it opens the new database (exclusively, not shared) in Microsoft Access.
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 to 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 reopen 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 are added to Visual SourceSafe:
| Object | Extension |
| queries | acq |
| forms | acf |
| reports | acr |
| macros | acs |
| modules | acm |
| tables, relationships, misc. objects | acb |
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 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 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.)
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.
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.
This command brings up a dialog to set options about the way the add-in behaves.
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.
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 relaunched.
Show Differences compares the Microsoft Access local copy of an object to the one in Visual SourceSafe and shows how they differ (in a text display). This command does not work for tables, relationships and the miscellaneous 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 results will vary between different source code control providers. Basically, these source code control providers just execute the Visual SourceSafe commands.
In order to deliver a database that's under source code control 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 using a message box.
To remove the database from source code control, Microsoft Access removes the Visual SourceSafe properties from the Microsoft Access 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.
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.
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 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).
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.
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.
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, and so on.
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.
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 only the 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 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 them into the "Data and Misc. Objects." So in order to modify or add a query, you'd have to check out all the tables, queries, relationships, command bars, database properties, imex specs, and so on. 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.
When you design a query, the source code control add-in asks 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 then make 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.
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 only the forms, reports, or macros 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.
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 only the 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 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 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 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 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.
When you put a database under source code control, a bunch of database objects are put into the Visual SourceSafe project lumped together inside a Microsoft Access database, rather than being stored as independent text files. The objects that are lumped into this binary 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 this object 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 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.
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 and 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 will be deleted.
When browsing a table, query, or form you can enter data in 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."
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 as it does when you design a table.
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 as it does when you design a table.
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 as it does when you design a table.
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 as it does when you design a table.
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 reapply your security settings after removing the database from source code control.
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.
Because tables, queries, and relationships can be modified through 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, 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.
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.
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.
Why do we store tables, relationships, and so on, 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:
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 leaves 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 forgets to check in. This is also the solution to use if a user checks out some objects and 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.ini (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.) 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 licenses 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 the 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 as read-only in design view so no design changes can be saved. There is no mechanism to programmatically check out an object.