the Manx Man's MS Access Pages
to subscribe - click here

New Tables and Fields via VBA - part 1 (021)

NOTE:This article has been converted to our new web site. See it here.

A client called me last week and asked for some new fields on an existing screen (form). The actual changes were only going to take a couple of hours. Problem though - their site is one hour away one way. How do I incorporate the new fields in their data base, without the high cost of an on site visit?

I came up with 3 possibilities:

1. have them send me their backend mdb and change it  (they would not be able to modify data until the new version returned - usually not a problem for a standard office environment if we arrange for overnight service)
2. send a blank version of the new backend mdb and a special program that copies all the data from their old one to the new one and then deletes the old one and renames the new one (time consuming if they have a large data base)
3. create the appropriate VBA to add the new fields and put default values in them (my favourite method)

Let's look at these in more detail.

Method 1 needs no further explanation. It is assumed that people reading this site/ezine know how to accomplish this.

Method 2 requires (i) a blank version of the backend mdb with the new fields defined, and (ii) a small VBA routine to copy the data and do the renaming. Again, part (i) is something you already know how to do. Here is some sample code to do the second part.

--------------------------------------
Option Compare Database
Option Explicit

Public Sub CopyData()

   On Error GoTo err_Copy

   Dim dbold As Database
   Dim wrkold As Workspace
   Dim rstold As Recordset
   Dim dbnew As Database
   Dim wrknew As Workspace
   Dim rstnew As Recordset
   Dim fld As Field
   Dim strName As String

' --- open original file
   Set wrkold = CreateWorkspace("", "admin", "", dbUseJet)
   Set dbold = wrkold.OpenDatabase("d:\data\original.mdb", True)

' --- open new version
   Set wrknew = CreateWorkspace("", "admin", "", dbUseJet)
   Set dbnew = wrknew.OpenDatabase("d:\data\empty.mdb", True)

' ==============================================================
' === copy each table (careful of order due to 1 to many relationships ===
' ==============================================================

' --- table from one side of relationship
   Set rstold = dbold.OpenRecordset("select * from table1")
   Set rstnew = dbnew.OpenRecordset("select * from table1")
   rstold.MoveFirst
   Do Until rstold.EOF
      rstnew.AddNew
      For Each fld In rstold.Fields
         strName = fld.name
         rstnew(strName).Value = rstold(strName).Value
      Next fld
      rstnew.Update
      rstold.MoveNext
   Loop

' --- table from many side of relationship
   Set rstold = dbold.OpenRecordset("select * from table2")
   Set rstnew = dbnew.OpenRecordset("select * from table2")
   rstold.MoveFirst
   Do Until rstold.EOF
      rstnew.AddNew
      For Each fld In rstold.Fields
         strName = fld.name
         rstnew(strName).Value = rstold(strName).Value
      Next fld
      rstnew.Update
      rstold.MoveNext
   Loop

' --- close everything
   rstold.Close
   rstnew.Close
   Set rstold = Nothing
   Set rstnew = Nothing
   dbold.Close
   dbnew.Close
   Set dbold = Nothing
   Set dbnew = Nothing
   wrkold.Close
   wrknew.Close
   Set wrkold = Nothing
   Set wrknew = Nothing

' --- delete old version and rename new one to old one
   Kill  "original.mdb"
   Name "empty.mdb" As "original.mdb"   (n.b. include drive and folder inside the quotes)

   MsgBox "New version of Back End data base is ready."

   Exit Sub

err_Copy:
' --- table was empty on 'movefirst'
   If Err.number = 3021 Then Resume Next
' --- unknown error
   MsgBox Err.number & " - " & Err.Description
   Exit Sub

End Sub

In part 2 of this article we will look at how to add a new table (and its fields) using VBA.