Updating design changes to live database

[David H]

Hi

How can I update a database on another computer with design changes without
overwriting the data.

David H

 

[Douglas J. Steele]

You can use DAO, ADOX or DDL.

For DAO, you create a reference to the "other" database, create a reference
to the table in question, then add or delete fields from it:

Dim dbChange As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbChange = OpenDatabase("E:\Databases\Backend.mdb")
Set tdfCurr = dbChange.TableDefs("Changing")
Set fldCurr = tdfCurr.CreateField("NewField', "Text", 50)
tdfCurr.Fields.Append fldCurr

Set fldCurr = Nothing
Set tdfCurr = Nothing
dbChange.Close
Set dbChange = Nothing

A good reference for this is
http://www.aislebyaisle.com/access/vba_backend_code.htm

ADOX is very similar, except the actual objects with which you're working
are different (sorry, too lazy to type up an example!)

DDL is perhaps a little more understandable:

Dim dbChange As DAO.Database
Dim strDDL As String

strDDL = "ALTER TABLE Changing ADD COLUMN NewField Text(50)"

Set dbChange = OpenDatabase("E:\Databases\Backend.mdb")
dbChange.Execute strDDL, dbFailOnError

dbChange.Close
Set dbChange = Nothing


You can get more information at http://support.microsoft.com/?id=180841

 

[Albert D.Kallal]

Hi

How can I update a database on another computer with design changes
without
overwriting the data.

The best approach is to split your database, and then you can develop like
most systems.

I explain how you can develop on a "copy" of the application, and then
deploy changes to your users without loosing data here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm