Assuming the database is split into front and back ends, which is highly
desirable in any case, with the front end objects (forms, reports queries
etc) its merely a case of replacing each user's current front end with the
new one. The front ends links can be refreshed by the user either by using
the built in linked table manager, or preferably automated at start-up by
checking the validity of the existing Connect properties for each linked
tabledef object.
With tables in the back end you can add columns with VBA from another Access
database, e.g. with:
Dim wrk As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set wrk = DBEngine.Workspaces(0)
Set dbs = wrk.OpenDatabase("F:\MyFolder\MySubFolder\MyDatabase.mdb")
Set tdf = dbs.TableDefs("MyTable")
With tdf
.Fields.Append .CreateField("MyNewColumn", _
dbText, 50)
.Fields("MyNewColumn").Required = True
End With
to add a text column named MyNewColumn to a table MyTable in the file
F:\MyFolder\MySubFolder\MyDatabase.mdb, setting its Required property to True
and its max length to 50 characters.
You could call this either from the front end at start-up, either after
checking whether the column already exists, or calling it regardless and
trapping the error if it does already exist, or you could provide the
customer with a separate update 'utility' database to do so. In either case
you'd not hard code the path to the file of course. In the case of a separate
utility database you could provide a means for the customer to browse to it;
Bill Wilson's class module for opening a common dialogue to return the path
can be downloaded from:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps
If the code is called in the front end you can get the path from any linked
tabledef object's Connect property AFTER the links have been refreshed. For
code for checking and refreshing links take a look at:
http://community.netscape.com/n/pfx/forum.aspx?msg=19444.1&nav=messages&webtag=ws-msdevapps
where you can download a demo file. I posted it way back in 1999, but I
think it should still work with later versions of Access.
Ken Sheridan
Stafford, England
