Yes, I do this all the time....
In fact, when I send clients a new front end (actually, they update it via
the following process).
http://www.kallal.ca/ridestutorialp/upgrade.html
Anyway, all the above does is un-packs (un-zips) a new front end
to the users machine. When the user launches the new front end, it
first re-links using the clients path name to the back end..
Next, my start-up code then runs updates to tables etc.
There is typical several operations that are quite common
1) - adding a new field to a existing table
2) - increasing the length of a existing fields
3) - adding a whole new table to the back end...
#1 is quite easy. Here is what my code will look like:
' add default fields
' check table defaults..and add default Tour Type field...
Set rst = CurrentDb.OpenR ecordset("tblDe faults")
On Error GoTo AddDefaultTourT ype
Temp = rst!DefaultTour Type.Name
rst.Close
Set rst = Nothing
Exit Sub
AddDefaultTourT ype:
rst.Close
GoSub AddDefaultTourT ypes
Resume Next
AddDefaultTourT ypes:
strToDB = strBackEnd
Set db = OpenDatabase(st rToDB)
Set nT = db.TableDefs("t blDefaults")
nT.Fields.Appen d nT.CreateField( "DefaultTourTyp e", dbLong)
nT.Fields.Refre sh
db.Close
Set db = Nothing
Set rst = CurrentDb.OpenR ecordset("tblDe faults")
Return
End Sub
So, the above gives you an idea.....
So, my upgrade code actually runs every time on startup. This ensures that
if they client grabs a older data file (say from a backup), version, then
the upgrade code always runs...
Here is an example to upgrade the size of a field
' check size of Anotes in tblGroupRemind
Set db = CurrentDb
Set dbTable = db.TableDefs("t blGroupRemind")
Dim intSize As Integer
intSize = dbTable("ANotes ").Size
Set dbTable = Nothing
If intSize = 50 Then
Dim nField As DAO.Field
Set db = OpenDatabase(st rBackEnd)
db.Execute "ALTER TABLE tblGroupRemind ALTER COLUMN Anotes text(255)",
dbFailOnError
End If
db.Close
Now, to add a new table, I actually simply include the table in the front
end, but with a "C" appended to the end.
I also have the link already made in the front end...so, I do NOT have to
add this link via code...since it already
exist!! So, adding a whole new table to the back end is not hard if you take
this approach....
strFromDB = CurrentProject. FullName
strToDB = strBackEnd
DoCmd.TransferD atabase acExport, "Microsoft Access", strToDB, acTable,
"tblRemindDefau ltsC", "tblRemindDefau lts", False
Set db = CurrentDb
strLink = ";DATABASE= " & strBackEnd
Set dbTable = db.TableDefs("t blRemindDefault s")
dbTable.Connect = strLink
dbTable.Refresh Link
In fact, you can see it is only 3 lines of code to add the whole table to
the back end (since I just copy it from the front end). By the way, that
table is never deleted in the front end..but, just stays there.....you never
know when the client might open a older data file....
the next few lines are needed, since the re-link code in start-up would have
FAILED for the links in the front end..since NO table in the back end
existed...So, after you add the table, you must re-fresh the table link.
However, since the table link IS PROVIDED in my new front end...at least I
don't have to write code. So, having a table, and the link already done in
the front end saves a LOT of coding to create a new table....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com 
