MS-Access / Getting Started

---

Creating an Automated Build

When we talk about creating a build, we typically think of languages such as Visual Basic .NET, C#, or C++ that produce compiled code. In Access 2007, the equivalent of this, of course, is the ACCDE file. In addition to creating compiled code, development environments that use these languages do some other things as well, such as stamping a binary with the build number.

For an Access application, you might choose to stamp it with a build number to indicate the build of the database being distributed. The build number can also be used to help track when it was created. In addition to a build number, you may have other processes that can be automated such as removing test data.

Because this is an automated process, this is a good opportunity to create an external tool. We'll use Visual C# to create the tool in this section.

Creating the Visual Studio Project

To provide a user interface that allows you to select objects, you need to create a Windows Forms application. Start by launching Visual Studio and create a new C# Windows application. Name the application BuildAccApp.

We're not crazy about the default name of the form: Form1. Select form in the Solution Explorer and then select the File Name property for the form. Change the File Name of the form to MainForm.cs.

We are going to use code in some additional namespaces as well. Add the following lines of code to the top of the MainForm.cs source file:

using System.IO;
using System.Runtime.InteropServices;

This will prevent us from having to fully qualify the namespace - ultimately saving us some typing.

Adding References

In order to create an ACCDE or MDE file, we'll need a reference to the Access object model. Because we're working with tables and database properties, we also need a reference to the Access database engine object model (DAO).

Start with the Access reference. Click the Project menu and then click Add Reference. Choose the COM tab and select the Microsoft Access 12.0 Object Library. The Access object model includes a dependency on the DAO 3.6 object model because of the CurrentDb function on the Access Application object. As a result, when you add a reference to the Access 12.0 object model, you get some additional references including the DAO reference. However, this version of DAO, which is used with Access 2000, 2002, and 2003, cannot open the newer ACCDB file format included with Access 2007. Therefore, there are a couple steps you need to take to add the correct DAO reference.

First, select the current DAO reference from the References folder in the Solution Explorer and delete it. Next, click the Project menu and then click Add Reference. Choose the COM tab and select the Microsoft Office 12.0 Access Database Engine Object Library.

To make these object models easier to work with, add the following statements to the top of the MainForm.cs source file.

using OFF = Microsoft.Office.Core;
using ACC = Microsoft.Office.Interop.Access;
using DAO = Microsoft.Office.Interop.Access.Dao;

These will create shortcuts that you can use in code to avoid typing the full name of the namespace.

Private Data

We know that we're going to need a couple of instances of the Database object in DAO. When creating an ACCDE file, we also need an instance of the Access Application object. In addition, because we are automating the Access database engine from outside of Access, we'll need an instance of the DBEngine object. Add the following code to the form. This code should appear beneath the class definition as follows:

public partial class MainForm : Form
{
    // object data
    private DAO.Database sourceDatabase;
    private DAO.Database targetDatabase;
    private DAO.DBEngineClass myEngine;
    private ACC.ApplicationClass myApp;

We need some additional data in the class as well. Add the following code:

    // scalar data
    string errorText = null;
    int major = 1; // default to 1.0
    int minor = 0;
    int revision = 0;

Build properties in the source and the destination database will be stored in database properties. Add the following constants to define these properties:

// Source properties constants
private const string SRC_PRP_TARGET 	= "BuildTarget";
private const string SRC_PRP_STARTDATE 	= "AppStartDate";
private const string SRC_PRP_APPMAJOR 	= "AppVersionMajor";
private const string SRC_PRP_APPMINOR 	= "AppVersionMinor";
private const string SRC_PRP_APPREVISION= "AppVersionRevision";
// Destination properties constants
private const string DST_PRP_RELDATE 	= "AppReleaseDate";
private const string DST_PRP_VERSION 	= "AppVersion";
private const string DST_PRP_DEBUG 	= "DEBUG";

There are a couple of exceptions that may be thrown from DAO so add constants for their COM error codes:

// error constants
private const int ERR_PROP_NOTFOUND 	= -2146825018;
private const int ERR_PROP_ISNULL 	= -2146824903;

Last, we need to create an instance of the DBEngine object. Modify the MainForm method of the form to instantiate an instance of the DBEngineClass, as shown here:

public MainForm()
{
    InitializeComponent();
    // create an instance of the Access database engine
    myEngine = new DAO.DBEngineClass();
}

The MainForm method is also known as the constructor. The constructor for a class runs when the object is instantiated. The Initialize event of a class module is the equivalent in VBA.

Helper Functions

We're tracking error messages through the variable called errorText. To provide consistent formatting of errors, add the following functions, called ShowErrorMessage, to the code:

private void ShowErrorMessage()
{
    ShowErrorMessage(errorText);
}
private void ShowErrorMessage(string errorMessage)
{
    MessageBox.Show(errorMessage,
	"Build Failed",
	MessageBoxButtons.OK, MessageBoxIcon.Error);
}