How to Update Add Tables to Backend

[EddyLLC]

I plan on distributng an Access2003 database system to a number of users. The system is split, backend with tables front end with everything else. What is the best means to update the backend of a users system? If I need to add a table or put additional fields in an existing table I can't simply replace the backend, users would lose data. Any suggestions are appreciated.

 

[dhookom]

I have generally used DAO code. You can check Help on "CreateTableDef". From Help"

Sub CreateTableDefX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create a new TableDef object.
    Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

    With tdfNew
        ' Create fields and append them to the new TableDef 
        ' object. This must be done before appending the 
        ' TableDef object to the TableDefs collection of the 
        ' Northwind database.
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)

        Debug.Print "Properties of new TableDef object " & _
            "before appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

        ' Append the new TableDef object to the Northwind 
        ' database.
        dbsNorthwind.TableDefs.Append tdfNew

        Debug.Print "Properties of new TableDef object " & _
            "after appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

    End With

    ' Delete new TableDef object since this is a 
    ' demonstration.
    dbsNorthwind.TableDefs.Delete "Contacts"

    dbsNorthwind.Close

End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trevil]

To build on what Duane has already mentioned, we have a similar situation where we sometimes make structure changes on the backend. Depending on the type of changes, it may require at least the table links in the frontend get either relinked or refreshed to pick up the changes.

We have a "master" copy of our frontend on the server and all users initially get their local copy from there. We have a table in the backend that is used to identify structure changes and the code that applies those changes also goes in and relinks the tables in the master, then sets a flag in the master to indicate it is a 'newer' version. We then have a 'version control' program that is executed when the user clicks their desktop icon to launch their application. It checks if a newer version is on the server and if so, will copy to workstation and launch. Otherwise, it just launches the local version.

"Hmmm, it worked when I tested it....