'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



New Tables and Fields via VBA - Part 1

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?

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

I came up with three (3) possibilities:

(article continues after sponsor spot)

  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")
Do Until rstold.EOF
   For Each fld In rstold.Fields
      strName =
   rstnew(strName).Value = rstold(strName).Value
   Next fld

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

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

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

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

Exit Sub

' --- 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.

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.


© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy