DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
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")
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
' --- (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

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.

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