Re: Updating a database

"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


.

Relevant Pages

  • 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: Like I said...
    ... > rich apps running a unidirectional database connection. ... > Can you access regular $50 USB scanners via TWAIN in an app shared ... > updates to the clients. ... you can only do this with middleware ...
    (borland.public.delphi.non-technical)
  • Re: Updating a database
    ... whilst the backend is an Access database. ... >>User A and User B install first version of the application. ... >>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)
  • Re: sql server express connection
    ... is that though the data has been refreshed and the win app ... reconnects to the database to collect the updates it does not ... better way of 2 apps using the same database without having to attach it. ...
    (microsoft.public.dotnet.languages.csharp)