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