dynamic schema update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a smart client application with an embedded MS Access database.
As we put out auto-updates for the application, at some point we also need
to update the Access database schema that's on the client.

This would involve backing up the user's existing data, installing the new
database schema and then copying user's data back into the new schema.

Can anyone recommend best ways to do this? Either writing our own code or
possibly looking at 3rd party tools who may provide this functionality.

Thank you!
 
You can certainly import the data.

What I normally do is simply write some code to 'update' the structures. For
example, to add a new field to a table, we go:

' add our new default user field

Dim nF As DAO.Field

strFromDB = CurrentProject.FullName
strToDB = strBackEnd

Set db = OpenDatabase(strToDB)

Set nT = db.TableDefs("tblEmployee")
nT.Fields.Append nT.CreateField("DefaultUser", dbText, 25)
nT.Fields.Refresh
Set nT = Nothing

' so, the above simply added a field called Defaultuser of 25, and type
text.

however, to add a complete new table, I find it too much work to add a
zillion createField commands (and, further, the need to create a 'link' to
the back end). So, what I do is simply develop..add the new table to the
back end..Fix, work, add new code. When I am done, I simply import that new
table to THE NEW FRONT END that I distribute to users (I also append the
letter 'C' to table name). Then, during my "upgrade" code, I go:

strFromDB = CurrentProject.FullName
strToDB = strBackEnd

DoCmd.TransferDatabase acExport, "Microsoft Access", strToDB, acTable,
"tblGroupRemindC", "tblGroupRemind", True

The above is only one line of code, and it copies a whole table (obviously
this is a 'new' table we added to our application), from the Front end to
the BE. I have already placed the new link in the Front end..so I don't even
have to add that.

The tricky part is when you have to also add enforced relasonships. You can
do that also. Lets assume we added the above tblGroupRemind, but I want it
to be relational, and have records in this table deleted when I delete a
table "contacts" record. The code would be:

' add relatinsetup

Dim nF As DAO.Field
Dim nT As DAO.TableDef
Dim nR As DAO.Relation


Set nT = db.TableDefs("tblGroupRemind")
Set nR = db.CreateRelation("ContactIDRI", "Contacts",
"tblGroupRemind", dbRelationDeleteCascade + dbRelationLeft)

nR.Fields.Append nR.CreateField("ContactID") ' parent table PK
(Contacts)
nR.Fields("ContactID").ForeignName = "ContactID" ' child table FK
(tblGroupRemind)
db.Relations.Append nR
db.Relations.Refresh

Set nR = Nothing

myPbar.IncOne
db.Close
 
Back
Top