Re: Updating a database

"Ivan Debono" <ivanmdeb@xxxxxxxxxxx> wrote in message
news:e05N1j3sFHA.3908@xxxxxxxxxxxxxxxxxxxxxxx
>
> "Kahuna" <none@xxxxxxxxxxxx> schrieb im Newsbeitrag
> news:e9Ni$hvsFHA.3164@xxxxxxxxxxxxxxxxxxxxxxx
>> "Ivan Debono" <ivanmdeb@xxxxxxxxxxx> wrote in message
>> news:On6f1SrsFHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
>> >
>> > "Stefan Berglund" <keepit@xxxxxxxxxxxx> schrieb im Newsbeitrag
>> > news:5kaph1l6v29maoqqqq8e2hltvb552anft2@xxxxxxxxxx
>> >> On Mon, 5 Sep 2005 16:29:32 +0200, "Ivan Debono"
>> >> <ivanmdeb@xxxxxxxxxxx>
>> > wrote:
>> >> in <uhKE1VisFHA.2936@xxxxxxxxxxxxxxxxxxxx>
>> >>
>> >> >Hi all,
>> >> >
>> >> >This problem is not indirectly related to VB only because my
> application
>> > is
>> >> >written in VB6, whilst the backend is an Access database. The
>> >> >database
>> >> >is
>> >> >also versioned, ie. has a table 'id' with a column 'version'
>> >> >
>> >> >I have the following scenario:
>> >> >User A and User B install first version of the application. Version
> 1.0
>> >> >I do changes to the app and the database (add fields, change queries,
>> >> >etc...). Version 1.1
>> >> >User A updates to Version 1.1
>> >> >I do changes to the app and the database (add fields, change queries,
>> >> >etc...). Version 1.2
>> >> >User A updates to Version 1.2
>> >> >User B updates to Version 1.2 from Version 1.0
>> >> >
>> >> >Updates/patches will be downloaded for the internet or emailed.
> Updating
>> > the
>> >> >application is easy with InstallShield.
>> >> >
>> >> >But how can I implement a method to make updating a database from
>> >> >version
>> > x
>> >> >to version y as easy as possible? Are there standard patch methods
> used
>> > or
>> >> >do people develop their own?
>> >> >
>> >> >Thanks,
>> >> >Ivan
>> >> >
>> >>
>> >> I've developed my own and although I use MSDE the approach applies
>> >> just
>> >> as
>> > well
>> >> to an Access backend.
>> >>
>> >> My entire database including stored procedures, functions, triggers,
> and
>> > tables
>> >> are all scripted so that creating a database for a new installation
> just
>> >> involves running the appropriate scripts. Updates are accomplished in
> a
>> > similar
>> >> manner by a helper program which gets the updates from the Internet
>> >> and
>> > directs
>> >> the update by running the appropriate scripts.
>> >>
>> >> Static data such as zip code tables are also represented in text files
> in
>> > tab
>> >> delimited format. The static data and script text files are then
>> > compressed and
>> >> stored in a resource. The update program then just retrieves the
>> > appropriate
>> >> resource file, decompresses it and runs the script.
>> >>
>> >> This approach requires that you have an intelligent installer/updater
> but
>> > I got
>> >> the feeling that you'd pretty much already decided on that path. I'll
> be
>> > glad
>> >> to share any code that you might find useful.
>> >>
>> >> ---
>> >> Stefan Berglund
>> >
>> > I guess that's the only approach possible in order to update a
>> > database.
>> > MSDE is more flexible than an Access DB though. I hope I don't need to
>> > resort to ADOX to do the updates. I'd prefer just SQL statements.
>> >
>> > The databases: I have a minimum of 4 databases that could be updated.
> The
>> > first 3 are reference databases and I know their location from the
>> > registry.
>> > The fourth is a database, and the user can create as many databases of
>> > this
>> > type and he can save them wherever he wants. Each of these databases
> have
>> > an
>> > 'id' table with a 'version' column in the form of 'Major.Minor'. In
>> > this
>> > way
>> > the databases are versioned and I can determine and compare versions
> with
>> > updates.
>> >
>> > I was thinking that db updates will delivered in the same way as app
>> > updates, ie. packaged with installshield. But db updates will contain a
>> > custom action that will run either a vbscript file or an exe.
>> > Alternatively
>> > there would be a simpler process for the user which is:
>> >
>> > I already have a component that connects with an internet db to check
> for
>> > latest news & alerts and displays them in a form. I can implement a
>> > similar
>> > method that will check for:
>> > a) app updates
>> > b) db updates
>> >
>> > App Updates: User will be notified and he will need to follow a link,
>> > download the patch and install.
>> > DB Updates: User will be notified, update will be downloaded and
> databases
>> > will be patched (updater must scan pc for all databases that the user
> has
>> > though). The problem is that the resource must always include updates
> from
>> > the first to the last versions, just in case 1 db is older than the db
>> > that
>> > is currently opened.
>> >
>> > I would really appreciate it if you would share the code where scripts
>> > &
>> > data and compressed in a resource, decompressed and run, and ideas
> (maybe
>> > with source too) on how you have implemented your intelligent updater!!
>> >
>> > Thanks,
>> > Ivan
>> >
>> >
>> Ivan - I am not terribly experienced in VB (as some will attest here) but
>> when talking about a patch I assume you're talking about the VB App. You
>> cant patch the Access mdb. One method I 've found useful is to have a
>> routine in my App that can take an 'Update mdb' and use the information
>> contained therein to modify the current App mdb.
>>
>> For example; my App.mdb has V1.0 and the Update.mdb has V1.1 and 2 new
>> tables and 10 new fields.
>>
>> The Update.mdb contains a dictionary of the tables, fields and field
>> properties to be added, dropped or modified. The App loads the Update.mdb
>> and the App.mdb, steps through the Update.mdb dictionary and makes the
>> necessary changes / additions / deletions to App.mdb.
>>
>> Once the updates are complete the version is changed in the App.mdb and a
>> flag is set in the App (perhaps an ini file) with the version. Each time
> the
>> App starts it compares the mdb and App versions, or check in the
>> AppDirectory for a potential Update.mdb, and acts accordingly.
>>
>> The benefit of doing this is that from within your App you can control
>> any
>> Security Features you need to set for the App.mdb. All this requires of
> your
>> users is the download of the mdb (will likely need to be zipped to get
>> through most firewalls these days) and to place it in the AppDirectory -
> so
>> your App can find it easily. Then he just runs the App and it updates
>> automatically (or on command as per your programme)!
>>
>> Hope this helps
>>
>> Kahuna
>>
>>
>
> I was also considering that approach but it would be time consuming to
> keep
> the update.mdb up to date!! The easiest solution is a text file with SQL
> script that will be executed through the updater... and it will be much
> smaller than an mdb.
>
> Ivan
>

The text file is one option but you still need to build / keep that up to
date. I use a routine in my dev App to keep the dictionary up to date. It
essentially builds a complete dictionary of my App.mdb and each time I
change the App.mdb I run it to get a snapshot - takes seconds. The
Update.mdb is very small as it only has 1 table in it. It does the same as a
text file would and yet offers all of the functionality of SQL. You don't
then have to work-out ahead of time what commands to use to build the new
tables / fields properties etc.

The Update.mdb library is simply compared to the App.mdb, if there is a
table name in the Update.mdb and not in the App.mdb it's added, same for
fields. If the 'Delete Column' of the Update.mdb is true the corresponding
Table / Field etc is deleted from the App.mdb and so on.

Works really well for me.

--
Kahuna
------------


.

Relevant Pages

  • Re: Windows XP auto updates stinks!!
    ... just use one exploit whereas you have to test all patches. ... you needn't install them. ... so I ignore any updates to it. ... you install an incompatible app ...
    (microsoft.public.windowsxp.general)
  • Re: WindowsXP memory error
    ... An app not shutting down correctly may be a problem with that particular ... Check for any App updates ... > I am have a problem with my Windows XP. ... > else or access the internet. ...
    (microsoft.public.windowsxp.hardware)
  • Re: Updating a database
    ... whilst the backend is an Access database. ... >>>I do changes to the app and the database (add fields, change queries, ... >>>User A updates to Version 1.1 ... >> the update by running the appropriate scripts. ...
    (microsoft.public.vb.general.discussion)
  • Re: Question about "updating" an .MDE remotely
    ... "Joey" wrote in message ... > app has been running over a year with no problems. ... Of course sometime the updates involve changes to tables as well but since ... for the first time. ...
    (microsoft.public.access.formscoding)
  • Re: Whats your strategy for updating your mobile app?
    ... > I have the app check for a new version on every sync with the server. ... >> My initial feel is to develop a baseline bootstrapper type application ... >> launcher that could handle checking for updates, ... It in turn then applies those updates (probably just replacing> old ...
    (microsoft.public.dotnet.framework.compactframework)