Re: Updating a database


"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


.

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
    ... >>> resort to ADOX to do the updates. ... >>> first 3 are reference databases and I know their location from the ... >>> I was thinking that db updates will delivered in the same way as app ... >> cant patch the Access mdb. ...
    (microsoft.public.vb.general.discussion)
  • 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: 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)