vb123.com

Garry Robinson's Popular MS Access, Office and VB Resource Site

Home Contact Us
Order our Software

RSS & Newsletter
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Read More

Get Good Help
If you need help with a database, our Professionals could be the answer
Read More

Is Your Database Corrupt ?
If you have a corrupt database, Try our Access Recovery service

The Workbench Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, mde compile, shutdown database Read and Download

The Toolshed
Searchable help file comprising of all the information at vb123.com plus hidden downloads etc. Read More

The Toolbox
Libraries of software that we regularly import into our projects. Enhances the Toolshed More..

DryToast New
Backup and query your BaseCamp® projects
Read More

Datamining/Graphs
Explore your data with this versatile graphing and data mining shareware tool. Read More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia
Contact Us ...

Search ...

or try our new Expression Web
vb123.com.au

Remote Updates of a Back-End Database

Garry Robinson and Scott McManus

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.

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 Work Bench"
 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).

ADD USER	CREATE USER or GROUP
ALTER USER or DATABASE	CREATE VIEW
ALTER TABLE	DROP
CONSTRAINT Clause	DROP USER or GROUP
CREATE INDEX	GRANT
CREATE PROCEDURE	REVOKE
CREATE TABLE

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

<< Click to expand

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.

Downloads
Click here for the download file if you own "The Toolshed" Else click here

Sidebar: Related Links

• A good piece on Citrix and Access, by Rickard Olsson from Ricol IT in Sweden–www.vb123.com/toolshed/05_access/remotedesktop.htm

• Garry Robinson's article about the File Dialog for Office 2002 in the October 2002 issue of Smart Access

• Access back-end deployment utility from SSW–www.ssw.com.au/ssw/DataPro

• Surgical Strike from Database Creations–www.databasecreations.com/prod_surgicalstrike.htm

• More information about Garry's Access Workbench–www.vb123.com/workbench

Author Bio.

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies popular web site at http://www.vb123.com/ or sign up for his Access email newsletter by sending a blank email to tips@vb123.com The web site features many Access resource sand software that are used by more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at … www.gr-fx.com

Other Pages On This Site You Might Like To Read

Alternative Access Protection/Security Ideas
Take Advantage Of The Class Module Features Of Your Access Forms
Tuning Up Your Design Toolbars In Access 2000/97

This article first appeared in the Augus 2005 Edition of Smart Access. Reprinted with permission from Pinnacle Publishing (http://www.pinpub.com/).

and was written by Garry Robinson from GR-FX Pty Limited

Click on the following button to jump to the next page in the document loop.