Add new fields to existing table

[David Dunster]

I have been trying to find in the 'big white book' the procedure for adding
new fields to an existing table with zero luck.

I am using Access 2000. for my application which has 13 provincial
databases. All of these databases are identical in structure. A person who
is testing my database has requested that some additional fields be added to
the existing databases to track 'DOB' (date of birth), 'DOL' (date of
license) and 'DOD' (date of death).

What procedure do I use to have these three new fields added to the other 13
databases.

I pretty good and 'banging my head against the brick wall' when it comes to
figuring it out but I need a start point or rough example ... which I can't
find in the 'big white book'.

I am hoping I can create a single small database that will have the
necessary code in which to copy the fielddefs from a single database into
the other 13.

I sure it can be done .. I just need to be pointed in the right direction.

Much thanks and best of the Holiday Season and upcoming New Year to one and
all.

signed

Dave
Regina SK Canada

 

[TC]

I just need to be pointed in the right direction.

Ok, here goes!

Write a small "update" database along the lines described below. Email it to
the 13 sites. Tell the users they need to copy it into the same folder as
the "data" databse, then run it. The "update" database will locate the
"data" database by assuming that it is in the same folder. Then it will use
VBA to add the relevant fields.

Code for "update" database - UNTESTED:

' locate the "data" database.
dim s as string
s = dbengine(0)(0).name
while right$ (s, 1) <> "\": s = left$ (sm en (s) - 1): wend
s = s & "DATA.MDB"
msgbox s

' open the "data" database.
dim dbData as database
set dbData = dbengine.opendatabase (s)

' add date field DOB to table BLAH.
dim td as tabedef, fld as field
set td = bData.tabledefs![BLAH]
set fld = td.createfield ("DOB", dbDate, ...) ' check the parameters!
td.fields.append fld

' all over.
set fld = nothing
set td = nothing
set dbData = nothing
msgbox "Done!"
docmd.quit

HTH,
TC

 

[David Dunster]

Thank you very much TC. I spent the last two hours or so experimenting and
adjusting it to my scenario. It works like a charm. Updates all 13 databases
faster than I can blink

The following code was used to add new fields to 13 databases. Many thanks
to TC for pointing me in the right direction and I hope this will help
others as well.

A MERRY CHRISTMAS and JOYOUS NEW YEAR to all!

' --- start code

Private Sub Hams_Database_Update_Click()
On Error GoTo Err_Hams_Database_Update_Click

Dim dbs As Database, rst As Recordset
Dim tblname As String, filename As String

Dim dbData As Database
Dim td As TableDef, fld As Field

' open query for Provinces only

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Prov")

Do Until rst.EOF

filename = rst![Database Directory] & "\" & rst![Database Name]
tblname = rst![Database Name]

' open the destination database

Set dbData = DBEngine.OpenDatabase(filename)

' add data fields to table

Set td = dbData.TableDefs(tblname)

Set fld = td.CreateField("Female", dbBoolean) ' set Yes/No Field
td.Fields.Append fld

Set fld = td.CreateField("DOB", dbDate) ' Set Date of Birth
Field
td.Fields.Append fld

Set fld = td.CreateField("DOL", dbDate) ' Set Date of
License Field
td.Fields.Append fld

Set fld = td.CreateField("DSK", dbDate) ' Set Date Silent
Key Field
td.Fields.Append fld

Set fld = td.CreateField("Age", dbByte) ' Set Age Field
td.Fields.Append fld

rst.MoveNext

Loop

[Hams Database Updated] = True
[Hams Database Update].Enabled = False

MsgBox "Update Completed!"

Exit_Hams_Database_Update_Click:

Set fld = Nothing
Set td = Nothing
Set dbData = Nothing
Set rst = Nothing
Set dbs = Nothing

Exit Sub

Err_Hams_Database_Update_Click:

msg = ""
msg = msg & "Database: " & rst![Database Name] & Chr(10) & Chr(13)
msg = msg & "Err Number: " & Err.Number & Chr(10) & Chr(13)
msg = msg & "Cause: " & Err.Description
MsgBox msg, vbCritical
Resume Exit_Hams_Database_Update_Click

End Sub

' --- end code