Re: Updating a database


"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


.

Relevant Pages

  • Re: Prevent Downloaded Update from Installing
    ... I don't just run scripts on my machine from just anybody. ... "Download updates for me and let me choose when to install". ...
    (microsoft.public.windowsxp.help_and_support)
  • Full reinstall of database.
    ... Install SP1 Databse ... Install SP2 Database Update ... Install FP2007 Database Updates ... Reinstalling all my custom components. ...
    (microsoft.public.windowsxp.embedded)
  • Re: SQL Script failing when using IF BEGIN END
    ... I am preparing the SQL updates for an application so that the current ... We start with a default database and apply the latest ... all of the scripts would run as a really long ...
    (microsoft.public.sqlserver.tools)
  • 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: how to move location of default database???? plz!
    ... fine, cool about the database, it probably amounts to how much tagging one ... updates - i woulda rolled them all back. ... No recent version of Windows does ... well when you have less than about 50MB to 100MB of free space. ...
    (microsoft.public.windowsmedia.player)