Need to Alter tables and add new columns remotely.

[wadey]

Don't know if this question belongs here, but having had a look around thought maybe this forum is the closest.

Going to deploy new desk top app with access backend to approx 40 users. In the future might need to change the table structure by adding new columns. Is this possible remotely without having to deploy new Db etc. A few ways I can think of but don't know if they would work:-

1. Mail out sql scripts to all users with instructions on how to back the data up and run the scipts.

2. build small VB app that has some sql code that does the job.

3. Instruct users to navigate to secure web site and ask users to download scripts and instructions.

What do you think? anyone know the most efficient way of doing this? does the above ideas sound ok or way of the mark?

Regards.

 

[danvlas]

Build a front-end/back-end solution :tables in a mdb file, all other objects in another mdb/mde file (with the tables from first file linked to the second)

Include a Version table in the back-end, with one row and one column and store the version in it

In the front-end, create a public constant CON_VERSION

In the front-end, startup form, check the constant against the value in the Version table

If CON_VERSION > TBL_VERSION, then run the code that makes the structure changes in the back-end, then update the table with CON_VERSION.

Be sure to trap all possible errors within your code, especially because of security, otherwise you may make only half of your changes.

Distribute the front-end to your users. The first who opens it would make the changes, all others will not be affected.

Something like the following code (it's not complete and definitely not usable by copy/paste):

Sub UpdateBackEnd()
Const conUpdate = 41

Dim blnVersionCheck As Boolean
Dim rst As DAO.Recordset
Dim strremote As String
Dim fld As DAO.Field
Dim rel As DAO.Relation
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Dim idx As DAO.Index
Dim strSql As String
Dim datData As Date
Dim i As Long
On Error GoTo errhandler
start:

strremote = Nz(DLookup("Database", "MSysObjects", "Database Is Not Null"), "")
If strremote = "" Then strremote = CurrentDb.Name
On Error Resume Next
Set db = DBEngine.OpenDatabase(strremote)
On Error GoTo errhandler
'open the version table
blnVersionCheck = True
Set rst = CurrentDb.OpenRecordset("Select * From USysVersion", dbOpenDynaset, dbPessimistic)
blnVersionCheck = False
If rst.Fields("Versiune") >= conUpdate Then GoTo ExitHere
If MsgBox("Structura bazei de date trebuie schimbata!" & vbCrLf & "Continuati?", vbYesNo + vbQuestion) = vbNo Then
    Quit
End If

rst.Edit
rst.Fields("Versiune") = conUpdate

If conUpdate = 41 Then
    db.Execute "Create Unique Index idxCodUnic On tblParteneri(prtCodPart) with disallow null"
End If


If conUpdate = 35 Then
    db.Execute "Alter table Fevechi add column fvcValidatDe Text(100)"
End If
If conUpdate = 34 Then
    db.Execute "Create Table tblErori" & _
    "(errID Counter, " & _
    "errTabel Text(60), " & _
    "errCamp Text(60), " & _
    "errValoareVeche Text(254), " & _
    "errValoareNoua Text (254), " & _
    "errData DateTime, " & _
    "errUser Text(50), " & _
    "errModificat YesNo, " & _
    "errDataModificat DateTime)"
    
    DoCmd.TransferDatabase acLink, "Microsoft Access", strremote, acTable, "tblErori", "tblErori"
    
    db.Execute "Create Index PrimaryKey On tblErori (errID) with primary"
    db.TableDefs("tblerori").Fields("errData").DefaultValue = "Date()"
    db.TableDefs("tblerori").Fields("errModificat").DefaultValue = False
    
End If


If conUpdate = 32 Then
    db.Execute "Alter Table Grupe Add Column grpCoef Double"
    db.Execute "Update Grupe Set grpCoef = 0.006165 Where Grupa in('BM', 'TR', 'PR')"
    db.Execute "Update Grupe Set grpCoef = 0.00785 Where Grupa ='TP'"
    db.Execute "Alter Table FactProd Add Column Metri Double"

End If



If conUpdate = 29 Then
    db.Execute "Create Index UserUnic On tblUsers(usrpass) with primary"
End If

If conUpdate = 28 Then
    db.Execute "Create Table tblUsers(" & _
    "usrUser Text(20), usrPass Text(20), usrNivel Long)"
    DoCmd.TransferDatabase acLink, "Microsoft Access", strremote, acTable, "tblUsers", "tblUsers"
End If



If conUpdate = 27 Then
    db.Execute "Update tblPrimitFaraFact Set pffIDFurnizor = 1 Where pffIDAviz=1887"
    db.Execute "Alter Table tblParteneri Add Column prtIndexPart Long"
    db.Execute "Alter Table tblParteneri Add Column prtTextBalanta Text(100)"
End If



If conUpdate = 19 Then
    On Error Resume Next
    DoCmd.DeleteObject acTable, "tblEditNIR"
    db.Execute "Drop Table tblEditNIR"
    On Error GoTo errhandler
    db.Execute "Create Table tblEditNIR (NrNir Long)"
    DoCmd.TransferDatabase acLink, "Microsoft Access", strremote, acTable, "tblEditNIR", "tblEditNIR"
    db.Execute "Insert into tblEditNIR (NrNir) Values(" & DMax("fvcNRDOC", "FEVECHI") & ")"
End If

If conUpdate = 18 Then
    On Error Resume Next
    DoCmd.DeleteObject acTable, "tblData"
    db.Execute "Drop table tbldata"
    On Error GoTo errhandler
    db.Execute "Create table tblData(dtData DateTime)"
    db.Execute "Create Index PrimaryKey On tbldata(dtData) with primary"
    DoCmd.TransferDatabase acLink, "Microsoft Access", strremote, acTable, "tblData", "tblData"
    datData = DateValue("01-Jan-2004")
    While Year(datData) = 2004
        db.Execute "Insert Into tblData(dtData) Values (#" & Format(datData, "dd-mmm-yyyy") & "#)"
        datData = datData + 1
    Wend
End If



If conUpdate = 15 Then
    
db.Execute "Update fevechi set DataValidat = fvcdataintrat where fvcdataintrat<#01-jun-2004#"
    
    db.Execute "Create Table tblBonConsum (" & _
    "bcsID Counter, " & _
    "bcsData DateTime, " & _
    "bcsBonConsum Long, " & _
    "bcsValidat YesNo, " & _
    "bcsDataIntrodus DateTime, " & _
    "bcsDataValidat DateTime, " & _
    "bcsIntrodusDe Text(20), " & _
    "bcsValidatDe Text(20)" & _
    ")"
    db.Execute "Create Index PrimaryKey on tblBonConsum(bcsID) With Primary"
    db.Execute "Create Unique Index DataUnica On tblBonConsum(bcsdata) with disallow null"
    db.TableDefs("tblBonConsum")("bcsDataIntrodus").DefaultValue = "Now()"
    db.TableDefs("tblBonConsum")("bcsValidat").DefaultValue = False
    datData = #6/1/2004#
    strSql = "SELECT tblSarje.srjDataSarja, Sum(tblBene.bnKgIncarcate) AS SumOfbnKgIncarcate " & _
    "FROM tblSarje INNER JOIN tblBene ON tblSarje.srjSarjaID = tblBene.bnIDSarja " & _
    "GROUP BY tblSarje.srjDataSarja " & _
    "HAVING (((Sum(tblBene.bnKgIncarcate)) > 0)) " & _
    "ORDER BY tblSarje.srjDataSarja;"
    
    Dim rst1 As DAO.Recordset
    Set rst1 = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
    Dim lngNrBon As Long
    
    While Not rst1.EOF
        db.Execute "Insert Into tblBonConsum(bcsData, bcsBonConsum) Values(#" & Format(rst1.Fields("srjDataSarja"), "dd-mmm-yyyy") & "#, " & lngNrBon + 1 & ")"
        lngNrBon = lngNrBon + 1
        rst1.MoveNext
    Wend
Set rst1 = Nothing
    'cream camp srjIDDoc in sarje
    db.Execute "Alter Table tblSarje Add Column srjIDDoc Long"
    'actualizam tblSarje cu IDDoc
    DoCmd.TransferDatabase acLink, "Microsoft Access", strremote, acTable, "tblBonConsum", "tblBonConsum"
    Call fChangeRelation(strremote, "tblBonConsum", "tblSarje", "bcsID", "srjIDDoc")
    CurrentDb.Execute "UPDATE tblSarje INNER JOIN tblBonConsum ON tblSarje.srjDataSarja = tblBonConsum.bcsData SET tblSarje.srjIDDoc = [bcsid];"



If conUpdate = 7 Then
'==============================
'versiunea 7
'==============================
db.Execute "Create Unique index NCIUnic on detaliiComenzi(IdComanda, NCI)"

sImportPVC
End If


If conUpdate = 8 Then

    db.Execute "Alter Table FEVECHI Add Column fvcDataIntrat DateTime Long"
    db.Execute "Update FEVECHI set fvcDataIntrat = DataIntrodus"
    Call fChangeRelation(strremote, "tblParteneri", "FEVECHI", "prtpartenerid", "fvcSubfurnizor")

End If
rst.Update
MsgBox "Version " & conUpdate

ExitHere:
Set rst = Nothing
Set fld = Nothing
Set db = Nothing
Exit Sub

errhandler:
    Select Case Err.Number
        Case 3219
            Resume Next
        Case Else
            MsgBox Err.Number & Err.Description  'Call fErrorManagement(Err.Number, Err.Description, "UpdateBackend")
        'Resume Next
        Resume ExitHere
    End Select
End Sub

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

All Access applications should be divided into a front-end and back-end. I generally distribute a new front-end that might expect new tables/fields in the back-end. Code is run at the start up of the application that checks for the new fields in the back-end. If there is something missing in the back-end, DAO code will add the missing stuff and maybe perform and update query.

This is old code from many years ago and may have old constants or whatever but you should be able to modify it for your needs.

Function UpdateVersion3a() As Integer
    On Error GoTo UpdateVersion3a_Err
    UpdateVersion3a = True
    'IsUpgradedV3a checks for a field in a table
    If Not IsUpgradedV3a() Then
        MsgBox "The data tables attached to this application need to be updated. This may take several minutes to create a new field and load data.", 48, "Version 3 Upgrade"
        
        Dim db As DAO.Database
        Dim tdef As DAO.TableDef
        Dim strDataDB As String
        Dim fldNew As Field
        Set db = CurrentDb()
        Set tdef = db.TableDefs("tblCases")
        strDataDB = Mid(tdef.Connect, 11)
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDataDB)
        Set tdef = db.TableDefs("tblCases")

        Set fldNew = tdef.CreateField("ActualTH", DB_DOUBLE)
        tdef.Fields.Append fldNew
        
        Set fldNew = tdef.CreateField("BudgetTH", DB_DOUBLE)
        tdef.Fields.Append fldNew

        Set tdef = Nothing
        Set db = Nothing
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qupdActualTHBudgetTH"
        DoCmd.SetWarnings True
    End If


UpdateVersion3a_Exit:
    On Error Resume Next
    Exit Function

UpdateVersion3a_Err:
    UpdateVersion3a = False
    MsgBox Err & ":  " & Error$, 16, "Error in UpdateVersion3a"
    Resume UpdateVersion3a_Exit:

End Function

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]

 

[danvlas]

Looks like another way to skin the same cat, just with different tools




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html