In a perfect world, the database design for your application would never change. In the real world, most applications grow and acquire new tables, new relationships, new fields, and new features. Garry Robinson and Scott McManus show you how they keep the databases for their clients up-to-date and in sync with each other.

In the early stages of database modelling and development, the users and the developers can exchange data-only (back-end) databases relatively easily. This tends to happen quite a bit early on while the developer is modifying the structure, relationships, descriptions, data types, and validation rules of the database. However, as time goes on and the number of users and the size of the back-end database increase, it may not be practical to send the data-only database over the Internet as modifications to the data structure are made. Instead, what you need is some way to modify the database design without losing the data that your users have in the tables.

Don’t understand back-end databases then read Let’s Split

This article explores what you can do when you reach this stage in a project. In particular, we describe the software approach that we’ve taken to make these back-end modifications quick, accurate, and painless for all stakeholders. But before we get into that method, we should point out that you don’t always need to implement our solution.

Alternatives to automating updates

The good old-fashioned way to make a small change to the back end is to write down all the steps that the person at the other end must take to implement the change and then e-mail the instructions to the user. This tends to only work for a small number of simple changes. This method can be good enough when all you need is to add a field or change the data description of a field. However, this method does assume that there’s someone at the site who’s capable and willing to make such changes, so, as a developer, you need to be aware of your clients.

If you do decide to implement your changes with this method, you’ll always do a better job of explaining the process to your users if you use a screen capture program to capture images of the actual task. Not only does this help give your users a clear idea of what to do, it also provides them with feedback that lets them check whether they’re on the right form and doing the right things. While it’s possible to use the Print Screen button and Windows Paint to create your screenshots, we strongly recommend that you purchase a really good screen capture program like SnagIt from TechSmith.com. Garry uses this program all the time for communicating with our users and developers. Scott uses Corel Draw’s screen capture facility (Peter Vogel, the editor of Smart Access, uses CapturEze, but that product doesn’t seem to be available anymore). There are some real time-savers built into the current crop of screen capture utilities, including the ability to capture just part of the screen, the current window, and the current menu selection.

Another option for making complex changes to a back-end database, made possible by the advent of higher speed Internet communications and industrial-strength thin client software such as Citrix or Terminal Server, is to log onto the remote server holding the database and make all the changes yourself. If you’re lucky enough to have a client that’s set up to allow this, all you generally need to do is document all your changes in Word or Excel to ensure that you make all the necessary changes while logged onto the remote session–and don’t make any changes that you didn’t intend.

Unfortunately, if your brain is running version 1.0 software that constantly needs to reboot like Garry’s does (Scott seems to have a newer model), we still recommend that you use a professional screen capture program to help document the more complicated changes that you need to perform on the back end. Scott seems to get by with writing notes in a notebook and carefully checking off each step as he makes the changes. In addition to guiding you through the process, these notes and documentation provide an audit trail of what was done to your client’s database.

Another option is to build a new version of the data structure in an empty database, then import data from the production database to the new database. This also allows you to create a backup copy of the original database. If you perform these updates on the remote computer, you can send copies of the original and updated database back to your computer for safekeeping. If, on the other hand, you bring the production database over to your computer before making the changes, you just need to rename the original database on the remote computer and send the updated version back to provide a complete set of backups.

Automating changes

If you don’t have the ability to log into the server that manages your back-end database and things are getting too complex to explain to the client, it’s time to take the bull by the horns and write some code to make the changes for you. We like this approach because software that updates the back end will help to ensure that the changes will be the same as they appear in your development back end. We’ll call this style of automated update an Updator from now on.

Once you send this software to your client, they’ll have what amounts to a two-button solution to the conversion problem, rather than a series of instructions that must be followed precisely. In our situation, we often work on projects where other developers and the DBA are all remote. In this situation, everyone must have exactly the same back-end database, and an Updator ensures that all the people involved have the same structure in the back end. The software also provides documentation of the changes made to the database (sort of an “audit trail in code”). When set up properly, the time taken to apply a large number of updates can be measured in seconds. It also makes rollbacks to previous structures easier when the changes aren’t successfully implemented.

Before you get too carried way with writing the software for implementing changes to your client, be aware that the only way to reduce bugs in the Updator is to use the Updator to change your own development database. You first build the program to make the changes, test the program by converting the database at your site, and then send the Updator to the remote sites to have the same change made there (a process known, unappetizingly, as “eating your own dog food”). This methodology also ensures consistency between your version of the back end and the client’s.

Setting up to do updates

The first things that we like to establish when creating an Updator are the location of the back-end database and the username and password specified in Workgroup Security that we’ll need to log on to the database. To link the software to the back-end database, you can ask the user to type the path in an input box. To query the user and make the link, we use the File Dialog in the Office 2002 library and some code for linking tables that was included in Garry’s Access security book. It’s possible that the user may type the path to the database incorrectly, so we always do a “test link” to one table in the database before proceeding. As we always like to have a Version table in the back-end database, we tend to use this table. There will be more on back-end version logging later:

Const BACKENDMDB = "backend2002.mdb"

Const VERTABLE = "tblVersion"

If RelinkTables_FX(BACKENDMDB, VERTABLE) = True Then

MsgBox "Data Database Linked OK"

Else

MsgBox "Problem relinking to back-end"

End If

Once we know the location for the back-end database, we then try and connect to the database to test that we can, in fact, open the database exclusively. To do this we use a Workspace object to define a named session for the current user (the Administration user). The Workspace object manages the open database connection and provides, in Microsoft Jet workspaces, secure workgroup support. It’s important that you and your client use a workgroup administration account to change the back-end database. If you’re not using Workgroup Security, this account will simply be the standard user, ADMIN.

In the following code, we make use of a function for getting the path of a linked database (included in this month’s download database) plus our OpenExcl function, which will tell us if we can open the database exclusively. Following is a function that OpenExcl uses to test whether we can open the database exclusively. The most important line of code in this function is the OpenDatabase method:

Dim wrkJet As DAO.Workspace

Dim strPswd as String

strPswd = InputBox( "Please enter password for Account XXX")

Set wrkJet = CreateWorkspace("new", "ADMIN", strPswd)

If OpenExcl(curLinkDir_FX("tblVersion")) = True Then

Set db = OpenDatabase(curLinkDir_FX("tblVersion"))

'…database management code (see below)

Else

MsgBox "You do not have exclusive access " & _

"to the Back-end database. Please ensure all " & _

"users are logged off. If In doubt, use the WorkBench"

End If

Here’s the subroutine that determines if the database is being used by someone else:

Function IsDatabaseOpen(strDbPath As String) As Boolean

' This function will test if a database is open

Const FILENOTFOUND = 3024

Const ALREADYOPEN = 3356

Const ALREADYOPENEXCL = 3045

Const DISKDOESNOTEXIST = 3043

Dim wsp As DAO.Workspace

Dim myDbs As DAO.Database

On Error GoTo IsDatabaseOpen_error

'Return reference to default workspace.

Set wsp = DBEngine.Workspaces(0)

'Attempt to open with exclusive access to a database

Set myDbs = wsp.OpenDatabase(strDbPath, True)

'No one is using the database

IsDatabaseOpen = True

Set myDbs = Nothing

Set wsp = Nothing

IsDatabaseOpen_Exit:

Exit Function



IsDatabaseOpen_error:

'Test for errors which are caused by trying to open

'the database in exclusive mode.

IsDatabaseOpen = True

Select Case Err.Number

Case FILENOTFOUND

MsgBox Err.Description, vbInformation, "File Not Found"

Case DISKDOESNOTEXIST

MsgBox Err.Description & vbCrLf & vbCrLf & strDbPath, _

vbInformation, "Disk does not exist"

Case ALREADYOPEN

'Opened by one or more people.

'One name appears in message

MsgBox Err.Description, vbInformation, "File Already Open"

Case ALREADYOPENEXCL

'Already opened exclusively by someone

MsgBox Err.Description, vbInformation, "File Already Opened Exclusively"

Case Else

MsgBox "Error number " & Err.Number & " -> " & Err.Description

End Select



IsDatabaseOpen = False

GoTo IsDatabaseOpen_Exit

End Function

The critical task here is to make sure when setting up the Updator that the Workspace connection to the back-end database has the correct username and password to give you the necessary permissions to update the objects that you’re going to change.

Once we’ve written the code to find and open the database, we start thinking about the code to change the structure of the database. To do this, we use a combination of DAO and SQL statements to make the changes–with preference given to using SQL. The portion of the SQL language used to modify table structures is referred to as the Data Definition Language (Table 1 lists SQL’s data definition commands). If you want to read more about these queries and are using Access 97, the easiest way is to search your computer for a file called JETSQL40.CHM or JETSQL35.HL. In that file you’ll find adequate help on all SQL-related topics. You can find this information through the Access Help system, but from Access 2000 onwards it will take some hunting.

Table 1. SQL Data Definition Language statements (from Access Help).

In our code we like to check to see if the change we’re about to make (in the table, field, or relationship) already exists prior to applying the change. If the change is already present, this is a good indicator that the software has been run before.

One final note before looking at typical code for making changes: An important part of developing your Updator application is to build reusable code modules for future use; this also saves you revisiting the sometimes complex processing behind many of the changes. We’ll walk you through some of the modules that we’ve built over the years for performing typical updates. All of our code assumes that our Updator has exclusive access to the back-end database.

The following, for instance, is a typical main line for one of our Updators:

Adds a new field to an existing table.

• Adds a lookup table to the database.
• Adds a combobox to an existing table that’s tied to a lookupdate.
• Modifies properties for a combobox in a table.
• Creates a new relationship between two tables.
• Updates the version table we keep in each database.

Here’s the routine that calls the various modules (the code even lets the user know when it’s done):

Const MAINTABLE = "tblGeology"

Const LOOKUPTABLE = "tlkpGShade"

Const MAINFIELD = "Shade1"

Const MAINFIELDSIZE = 2

Const LOOKUPFIELD = "GCode"

Const LOOKUPFIELD2 = "Description"

'Add the new field

updAddChrField dbs, MAINTABLE, MAINFIELD, MAINFIELDSIZE

'Export the lookup table into the backend

expTbl dbs, strLinkPath, LOOKUPTABLE



'Add a lookup combo box to the main table

Call tblFldProperty(dbs, MAINTABLE, MAINFIELD, "DisplayControl", 111, dbInteger)

'Now define the combobox rowsource type

Call tblFldProperty(dbs, MAINTABLE, MAINFIELD, "RowSourceType", "Table/Query")

'Add a query to populate the lookup combo box

Call tblFldProperty(dbs, MAINTABLE, MAINFIELD, _

"RowSource", "SELECT " & LOOKUPFIELD & ", " & _

LOOKUPFIELD2 & " FROM " & LOOKUPTABLE)

'Create a join between main table & lookup table

Call addSimpleRelation(dbs, MAINTABLE, _

LOOKUPTABLE, MAINFIELD, LOOKUPFIELD)

'Add version information to the backend

Call updVer(5)

lblMessage.Caption = "Lookup table for " & MAINFIELD & " added."

lblMessage.visible = True

MsgBox lblMessage.Caption

Making changes

We’ll start with the code to add a field to the back-end database. As you’ll see in all of our procedures, we pass a reference to the back-end database as a DAO Database object to the routines. Having gone to all the trouble of opening the database exclusively, we don’t want to open and close the database in the individual routines (it might provide a window of opportunity for someone else to open the database). This is our standard routine for adding a field to a table. It must be passed the database, the name of the table, the name of the field, and the size of the field. After checking if the field already exists, the code uses the SQL Alter Table command to actually make the change:

Function updAddChrField(db As Database, tbl As String, fld As String, intSize As Integer)

'Add a text column to the update database

On Error GoTo ErrorHandler

If FindFieldname(db.TableDefs(tbl), fld) = False Then

db.Execute "ALTER TABLE " & tbl & " ADD COLUMN " _

& fld & "  CHAR(" & intSize & ");"

Else

MsgBox "It would appear that field: " & fld & _

" in Table: " & tbl & " has already been added : "

End If

exit_proc:

Exit Function

ErrorHandler:

MsgBox " Error in subroutine " & Err.Description & "(" & Err.Number & ")"

Resume exit_proc

End Function

The FindFieldName function that we use in this routine to check to see if the field exists uses nothing but DAO and looks like this:

Function FindFieldname(tdf As TableDef, fld As String) As Boolean

' Check if a field exists

On Error GoTo ErrorHandler

Dim Flds

For Each Flds In tdf.Fields

If Flds.Name = fld Then

FindFieldname = True

Exit Function

End If

Next Flds

FindFieldname = False

Exit_Proc:

Exit Function

ErrorHandler:

MsgBox " Error in subroutine " & Err.Description & "(" & Err.Number & ")"

Resume Exit_Proc

End Function

In addition to adding fields to a table, you often need to add new tables. A typical change is to transfer tables (often containing data) into the back-end database. The following routine is our standard module for meeting this goal, using the TransferDatabase method to move a lookup table into the back-end database:

Function expTbl(db As Database, strPath, tbl As String)

'Export a table to the backend database

On Error GoTo ErrorHandler

If FindTablename(db, tbl) = False Then

DoCmd.TransferDatabase acExport, _

"Microsoft Access", strPath, acTable, tbl, tbl

Else

MsgBox "It would appear that the table: " & _

tbl & " Has already been exported into the BE database."

End If

exit_proc:

Exit Function

ErrorHandler:

MsgBox " Error in subroutine " & Err.Description & _

"(" & Err.Number & ")"

Resume exit_proc

End Function

Function FindTablename(db As Database, tdf As String) As Boolean

'Find if a table exists

On Error GoTo ErrorHandler

Dim tbls

For Each tbls In db.TableDefs

If tbls.Name = tdf Then

FindTablename = True

Exit Function

End If

Next tbls

FindTablename = False

exit_proc:

Exit Function

ErrorHandler:

MsgBox " Error in subroutine " & Err.Description & _

"(" & Err.Number & ")"

Resume exit_proc

End Function

Extra power with DAO

Life would be fairly simple if you could make all the necessary changes with SQL, but we frequently need to do things that SQL cannot. In most of these cases we can use DAO to meet our goals. DAO gives us the power to change database properties that aren’t part of the SQL specification (for instance, the properties for lookup comboboxes and validation checks that can be added to fields). In the following examples, we show two functions: one for adding a relationship and one for checking for the presence of the relationship. On other occasions we’ve used DAO for tasks that could have been done with SQL, usually because the DAO version of the code was easier to write in a reusable way.

Here, for instance, is our routine for creating a relationship. While this could have been done with a SQL statement, we chose to do it with DAO:

Function addSimpleRelation(db As Database, tbl1, tbl2, fld1, fld2)

' Create a join (no integrity) between two tables

On Error GoTo ErrorHandler

Dim dRel As Relation

If findRelation(db, tbl1, tbl2) = False Then

Set dRel = db.CreateRelation(tbl1 & tbl2, tbl1, tbl2, dbRelationDontEnforce)

dRel.Fields.Append dRel.CreateField(fld1)

dRel.Fields(fld1).ForeignName = fld2

db.Relations.Append dRel

Else

MsgBox "It looks like the relationship for : " _

& tbl1 & " and  " & tbl2 & " already exists. "

End If

Exit_Proc:

Set dRel = Nothing

Exit Function

ErrorHandler:

MsgBox " Error in subroutine addSimpleRelation " _

& Err.Description & "(" & Err.Number & ")"

Resume Exit_Proc

End Function

For our routine to check if a relationship exists, we chose to use DAO also. In SQL the only way to check for the existence of a relationship would be to try create it (and catch the error if it already existed) or delete it (and catch the error if the relationship doesn’t exist). The problem with using error handling in these situations is that you must either assume that the error is triggered by the presence/absence of the relationship or check for the specific error code. You also generally need to provide comments to explain what your code is doing. Our DAO check for a relationship is both more obvious and less ambiguous than the SQL version:

Function findRelation(db As Database, tbl1, tbl2) As Boolean

'See if a relationship exists

On Error GoTo ErrorHandler

Dim dRels As Relation

findRelation = False

For Each dRels In db.Relations

If dRels.Name = tbl1 & tbl2 Then

findRelation = True

Exit Function

End If

Next dRels

findRelation = False

Exit_Proc:

Exit Function

ErrorHandler:

zasSaszMsgBox " Error in subroutine " & _

Err.Description & "(" & Err.Number & ")"

Resume Exit_Proc

End Function

You may be wondering why we didn’t use the data definition objects that make up the ActiveX Data Objects Extensions library (ADOX)–added to Access as part of the great ADO push in Access 2000. The ADOX library has many useful tools for managing table structures and database properties. However, when we initially tried to use ADOX with Access 2000, we found that ADOX wasn’t always installed on the machines that our clients were using. To solve this problem, we would either have had to build an installer that would put ADOX on our clients’ computers or talk our users through the installation of ADOX. Further, ADOX doesn’t fully replace SQL and DAO functionality, so we would have had to continue to use SQL and DAO in our Updators. As a result, we gave up on ADOX. Since then there’s been minimal further development in ADOX by Microsoft, which makes our decision to not bother with ADOX seem like a good one.

Logistical issues

With the framework we’ve sketched out here, you can probably build any other routines that you need (and you can look at our sample database for code not included in this article). However, there are some important issues around using the Updator that we should address.

For instance, it’s important that maintenance of the database be a scheduled activity that occurs frequently enough to support updating the database. In order for the Updator to do its job, all other users have to be locked out of the database. When maintenance is scheduled and expected, the minimal number of people are disadvantaged when the Updator is run. We use our own tool (called the Access Workbench) to implement regularly scheduled maintenance. Among other features, the Workbench allows us to determine who is on, stop new users from starting a new session, send users messages about the process, and finally shut users down if they don’t log off (usually caused by users who stray away from their machines and don’t see the scheduled warning messages). There are other tools available (principally from FMS) that will meet the same goals.

Something else that we’ve learned from building these Updators for different clients is the importance of version control. While everyone has the best intentions, some updates never get run because of e-mail hiccups or users going on vacation and forgetting to run the Updator (or, on at least one occasion, when we simply forgot to send the Updator in the first place). To help diagnose these problems, we insert a new record into a version table every time the Updator software completes successfully. This is done with a SQL Insert statement that uses the linked tblVersion table that we always have in the Updator:

DoCmd.RunSQL "INSERT INTO " & VERTABLE & _

" ( updVersion, updDate ) SELECT " & _

verIn & ", Now() ;"

In Figure 1, you can see a picture of the records that are inserted by the Updator. As an aside, we also use a table-based versioning system for our Access front ends that uses a similar table. As we’re forever releasing minor updates to all our clients, we’ve further improved this process by building a Version Table Reader into the Access Workbench that we promote to the Access developer community that allows us to quickly see the changes that a database has had applied. Without versioning, we really would be as muddled as Garry’s version 1.00.01 Brain software.

Figure 1

The sample database

The download consists of a database called backend2000.mdb, which is the version of the database prior to any changes, and remoteBEUpdates.mdb, which is an Updator written in Access 2002. Opening this database will display the form shown in Figure 2 and allow you to make the adjustments to the back-end database. In the sample database you’ll find the code to do the following:

• Transfer a (lookup) table to the back-end database.
• Turn a field into a combobox and add a query for its row source property.
• Add a simple join between the lookup table and the main table.

Figure 2

In a production environment, you’d send your users the remoteBEUpdates.mdb database. When you’re using this database, enter a blank password for the Admin account that has been hardwired into the solution. Depending on your Access workgroup installation, you may need to change this.

In a perfect world, database developers would design the perfect back-end database the very first time and we’d never have to change the design. Unfortunately, this isn’t the world we live in. In this article we’ve provided you the background to create a solution for the real world: the Updator.