Solved: Upgrade Process

[Movian]

Hey,
i currently have a system setup that allows me to "Fairly quickly" upgrade a previous version of our database to then newest. It does this by systematically going through each table (It dynamically creates a list) and then copying over any data. This way any fields which still exist are copied over any that are removed are not and any new ones will be left empty. It also does a comparison or a table in code to pull over any "Different values".

I was wondering if there is a set method that might make this easier as i have been asked to setup and "Automated" process to do this. E.G. a user can download a patch file, Run it and when they are finished the database is updated. As at the moment the process is a little more complicated .

I should also note that almost all of our users are running the microsoft access run time version of the system.

I have been contemplating setting up the database to run the code on the form to run as an onload event. And to have this upgrade database in a self extracting RAR file that runs the file once it has been extracted. I would also include a copy of the new system in there to pull the information over into then when the system has finsihed copying the data into the new database it would rename the old database and put it into an archive folder then rename the new database and put that into the main folder as the final database. This would all new to be done with database bases that have been compiled into accde format and have been renamed to accdr.

As always any help with this is greatly appreciated.

Here is an example of the code used to update the lookup table.

Private Sub LookupConvert()
'This sub is designed to analyse the new lookup table, then to pull any user options from the old lookup table into the new one

Dim newdb As DAO.database, olddb As DAO.database, NewLook As DAO.Recordset, OldLook As DAO.Recordset
Dim fld As Field, copied As Integer, compared As Integer
Dim findstring As String
Dim ValueString As String, counter As Integer
Dim exist As Boolean, SQlString As String, records As Integer

Set newdb = OpenDatabase(CurrentProject.Path + "\SonoSoft.accdb")
Set olddb = OpenDatabase(CurrentProject.Path + "\Data.accdb")
Set OldLook = olddb.OpenRecordset("tblLookupValues", dbOpenDynaset)

exist = False
copied = 0
compared = 0
OldLook.MoveLast
OldLook.MoveFirst
Me.ProgressBarB.Width = 0

Do Until OldLook.EOF
Screen.Application.Echo True
ProgressBarB.Width = (ProgressBarA.Width / OldLook.RecordCount) * OldLook.AbsolutePosition
Me.percent.Caption = CStr(Round(OldLook.PercentPosition, 0)) & "% Complete"
Me.Repaint
Screen.Application.Echo False

SQlString = "SELECT Form1, Control, Value FROM tblLookupValues WHERE Form1 =""" & OldLook.Fields("Form1") & """ AND Control = """ & OldLook.Fields("Control") & """ AND Value = """ & OldLook.Fields("Value") & """"
Set NewLook = newdb.OpenRecordset(SQlString)
On Error Resume Next
NewLook.MoveLast
NewLook.MoveFirst
On Error GoTo 0
records = NewLook.RecordCount
NewLook.Close
Set NewLook = newdb.OpenRecordset("tblLookupValues")
If Not IsNull(OldLook.Fields("value")) And Not OldLook.Fields("Value") = "" Then
If records < 1 Then
NewLook.AddNew
NewLook.Fields("Form1").Value = OldLook.Fields("Form1")
NewLook.Fields("Control").Value = OldLook.Fields("Control")
NewLook.Fields("Value").Value = OldLook.Fields("Value")
copied = copied + 1
MsgBox CStr(copied) & " - " & OldLook.Fields("Form1") & " - " & OldLook.Fields("Control") & " - " & OldLook.Fields("Value")
NewLook.Update
End If
End If
NewLook.Close
OldLook.MoveNext
compared = compared + 1
Loop
Set NewLook = Nothing
OldLook.Close
Set OldLook = Nothing
newdb.Close
Set newdb = Nothing
olddb.Close
Set olddb = Nothing
MsgBox "System has compared " + Str(compared) + " record(s) and has copied " + Str(copied) + " record(s).", vbInformation, "Complete"
MsgBox "DONE!"
End Sub

A similar system is used to update the tables. The whole process normally takes 5-6 minutes.

I am sure there must be a better way to do this. This is NOT a front end back end solution. The data and the forms are stored in the same .accdr file

[CreganTur]

Have you considered distributing Replicas to your users? Replicas update themselves based on a master copy. I don't know all the ins and outs of how they work, since I've only played with them as an academic excercise. But, they could be what you want.

[Movian]

interesting concept replication. however i found this

http://bytes.com/topic/access/answers/857409-master-replica-dbs-access-2007-a

which states


In point of fact, Replication cannot be used with ACCDB files! Micro$oft removed the function with the new file format. Allen Browne suggests using attached tables, connected to a database that supports replication, i.e.an MDB back end rather than an ACCDB back end, if you absolutely have to have it. But as Nic;o) has said, it is a process fraught with problems.

Linq ;0)>

meaning i can't use it. I guess i will just plod forward with my custom upgrade system.