I have 40 cities using this database as a run-time Access 97 split
database; I send them an updated program.mdb and it needs to change the
table in their data.mdb.  Is there an easy (or any) way to handle
this???

Any suggestions appreciated.

Michelle Matthews

One of the reasons that you split the tables from the code is so that the
user will not open the code.mdb and be able to change the structures of the
tables.

There is no such thing as a checkbox field, but there is a field of type
Yes/No.

You are going to have to come up with a setup routine that adds the field in
the data.mdb, without affecting the user's data.

Structure changes are not fun.
--
Steve Clark (Aspiring MS MVP)
FMS, Inc.
Work: http://www.fmsinc.com
Play: http://members.xoom.com/TightSqueeze/home.htm

You Could Add Code Like This:

Sub PatchTable()
  Dim Ws As WorkSpace
  Dim Db As Database, TDef as TableDef, Fld As Field, RDb As Database
  Dim DbName As String
  On Error Resume Next
  Set Db = CurrentDb()
  Set TDef = Db.TableDefs("Table1")
  Set Fld = TDef.Fields("Check1")
  If Err <> 0 Then ' Field Doesn't Exist
    Err.Clear
    DbName = TDef.Connect
    DbName = Mid(DbName,Len(";DATABASE=")+1) ' Get Path To Data Db
Assumes Access Db
    Set Ws = DbEngine.CreateWorkSpace("tmpWs","<DbOwner>","OwnerPwd")    
    ' If Secured Db else DbEngine(0)
    Set RDb  = Ws.OpenDatabase(DbName)
    Set TDef = RDb.TableDefs(TDef.SourceTableName)
    Set Fld = TDef.CreateField("Check1",1) ' 1 = Yes/NO
    ' ....
    ' Any other properties
    TDef.Fields.Append Fld
    RDb.TableDefs.Refresh
    Db.TableDefs.Refresh
  End If
End Sub

If you need to change the database structure during an upgrade, probably a
better solution that I have used a lot, that doesn't require you to change
your main program, is to make a short little temporary VB program that
changes the back-end database structure with DAO, that runs after your
program upgrade installation.  If you use the ODE Setup Wizard to install
your upgrade, you can even set this to run automatically at the very end of
the setup.

Structure changes are fun!

Alden (Aspiring Know-It-All)

P.S. Steve, is FMS hiring? ;-)

Michael

Function AddFieldVer30 ()
On Error GoTo AddFieldVer30_Err
'Called from AutoExec macro on startup
'Upgrades pmdata.mdb from ver 2.x to 3.0 if necessary
'Adds AddresseeCode2 field to tblAddressees and adds index
Dim ws As WorkSpace, dbData As Database
Dim tdf As TableDef, fld As Field, idx As Index

Set ws = dbengine(0)
Set dbData = ws.OpenDatabase(abCurrDataPath("tblDefaults") &
"PMDATA.mdb")
Set tdf = dbData.tabledefs("tblAddressees")
On Error Resume Next
Set fld = tdf.fields("AddresseeCode2")
If Err = 0 Then GoTo AddFieldVer30_Exit
On Error GoTo 0
On Error GoTo AddFieldVer30_Err
If MsgBox("A new version of Postmaster has been installed on your
system. Postmaster will now update your data files to be compatible
with this new version of the software." & Chr$(13) & Chr$(10) &
Chr$(13) & Chr$(10) & "Your version of Postmaster will now allow a
second Code field to be filled in for each addressee.", 65, "Updating
to Add Addressee Code2 Field") = 2 Then
   DoCmd Quit
   GoTo AddFieldVer30_Exit
End If

Set fld = tdf.CreateField("AddresseeCode2", DB_TEXT, 30)
fld.AllowZeroLength = True
tdf.fields.Append fld
tdf.fields("AddresseeCode2").OrdinalPosition = 17

Set idx = tdf.CreateIndex("AddresseeCode2")
Set fld = idx.CreateField("AddresseeCode2")
idx.fields.Append fld
tdf.indexes.Append idx

Set tdf = dbData.tabledefs("tblArticles")
Set fld = tdf.CreateField("AddresseeCode2", DB_TEXT, 30)
fld.AllowZeroLength = True
tdf.fields.Append fld
tdf.fields("AddresseeCode2").OrdinalPosition = 16

MsgBox "Your PostMaster data has successfully been updated to the new
version.", , "Update Completed"

AddFieldVer30_Exit:
On Error Resume Next
dbData.Close
Exit Function

AddFieldVer30_Err:
Select Case Err
   Case Else
      MsgBox Err & " " & Error$, , "AddFieldVer30 Error"
End Select
Resume AddFieldVer30_Exit

End Function

Function abCurrDataPath (pstrTable As String) As String
On Error GoTo abCurrDataPath_Err
'Inputs:
'  pstrTable -- name of an attached table in the current database
'Outputs:
'  directory path for the data file
Dim db As Database, tdf As TableDef
Dim intI As Integer
Set db = CurrentDB()
Set tdf = db.tabledefs(pstrTable)

For intI = Len(tdf.connect) To 13 Step -1
   If Mid(tdf.connect, intI, 1) = "\" Then Exit For
Next intI

abCurrDataPath = Mid$(tdf.connect, 11, intI - 10)
abCurrDataPath_Exit:
Exit Function

abCurrDataPath_Err:
Select Case Err
   Case Else
      MsgBox Err & " " & Error$, , "abCurrDataPath Error"
End Select
Resume abCurrDataPath_Exit

End Function

 -- Andy