Re: Updating a database

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Kahuna" <none@xxxxxxxxxxxx> schrieb im Newsbeitrag
news:un5UWq4sFHA.1284@xxxxxxxxxxxxxxxxxxxxxxx
> "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
> ------------
>
>

Your solution made me thinking....

I will still go with the text file, but I can write a utility that compares
2 db's and creates the relevant SQL script for me :)

Ivan


.

Relevant Pages

  • Re: Error - Error Accessing File. Network Conn..." File Corruption?
    ... > consuming problem receiving the error message "Error Accessing File. ... > truly clean up an mdb? ... Sometimes the app ... > - Does anyone know of a good site dealing with Access file corruption? ...
    (microsoft.public.access.formscoding)
  • Re: Rapidly expanding MS Access data file size
    ... I suggest you use a temporary .mdb file for the temporary tables. ... delete the bloated .mdb file and start the next import-process-upload ... >I have a small Access 2000 app that whilst working is causing some concern. ...
    (microsoft.public.access.externaldata)
  • What is all that junk in the trunk (DB) ...
    ... I have AC2002 app I have been developing and supporting for over a year. ... CAN ANYONE tell me what is taking up 10 plus megs of space in the MDB file. ... I compact and repair it yet I still have a 10 meg mdb. ... I know my custom toolbars are hiding in there somewhere. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Forced disconnecting
    ... If the only users of the MDB are your applications, ... I have each app check for some kind of "semaphore" ... then it tells them to exit the app or it will be closed for them. ... if your "admin" program that needs exclusive ...
    (borland.public.delphi.database.ado)
  • Re: VB 5 app cant open local database
    ... the app also uses a small 97 .mdb in a folder on the user's ... When properly installed on some new Fujitsu workstations, ... Does the local folder have an LDB file left behind? ...
    (microsoft.public.vb.database)