Re: Need technique to programatically update back end structure / relationships
- From: "Bob Howard" <Bob@xxxxxxxxxxxx>
- Date: Mon, 18 Jul 2005 22:14:18 -0700
Thanks Allen. I'll be working on it over the coming months. We're in the
early stages of relocating, so this will probably have to be worked "in
between things." Bob.
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uwdi1aBjFHA.132@xxxxxxxxxxxxxxxxxxxxxxx
> Yes, that should work, Bob.
>
> If this is runtime, you probably want to get them to uninstall the old
> version first, so it makes sense to give them the new complete structure,
> and import the data. You would be fully aware of the dependencies (and so
> the order you must execute the import from backup.)
>
> Even a non-technical user should be able to make sense of your approach,
> which they would view as:
> 1. Backup and remove your old program.
> 2. Install the new one.
> 3. Import the data from the old back up into the new program.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Bob Howard" <Bob@xxxxxxxxxxxx> wrote in message
> news:u63ct7AjFHA.3316@xxxxxxxxxxxxxxxxxxxxxxx
> > Allen;
> >
> > Thanks for the quick response....
> >
> > I had thought about the possibility of distributing the new starter
> > database
> > (void of data, but reflecting the new structure) containing a startup
form
> > with some VBA behind it (see more, below). Prior to the install, I
would
> > have the user rename the current production database (after taking a
> > backup)
> > to some other name. Then, the install would also install the new
starter
> > database along with a shortcut pointing to the new starter database. As
a
> > final step of the installation process, I would ask the user to execute
> > that
> > shortcut. When executed, it would then automatically go to the starter
> > database's "startup form" and execute it's OnOpen event in its VBA. All
> > the
> > processing would occur within the OnOpen event, which would first ask
the
> > user to point to the renamed production database. The code would then
> > import the data from each of the tables in the renamed production
database
> > populating the starter database --- which then becomes the new
production
> > database.
> >
> > Do you think this method has merit?
> >
> > Bob.
> >
> > "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
> > news:OsdCCcAjFHA.2852@xxxxxxxxxxxxxxxxxxxxxxx
> >> Hi Bob
> >>
> >> Big question. Hopefully you will get some other responses to see what
> > others
> >> do.
> >>
> >> It's not too difficult to programmatically create new tables, fields,
set
> >> properties, create indexes, and create relations using DAO. (DAO
because
> >> it's the native Access libraray, and is the only way to set all the
> >> useful
> >> properties, bar a couple.) Post back if you need examples of how to do
> > that.
> >>
> >> So the next question is *where* to run this code. One approach is to
> >> embed
> >> the code into the app itself. When it starts, it runs the normal
startup
> >> code to test the back end is available, and then tests that one of the
> >> new
> >> tables is available. If not, it runs the code to create them. IMHO, it
> > seems
> >> a bit top-heavy to permanently embed that kind of code into the app
when
> > it
> >> only needs to run once. (I also don't like the idea of code running
each
> >> time the app starts that has the potential to modify the back end.)
> >>
> >> An alternatve approach is create a little mdb to perform the structural
> >> update. When "updater.mdb" runs, it asks the user to locate the back
end
> > for
> >> their app, and verify they have the right database by using the same
> >> uniquely named table you use for your reconnection-on-startup check.
> >> OpenDatabase() exclusively. Create each table, field, property, index,
> >> and
> >> relation, logging every step to a local table (in updater.mdb). If
> > anything
> >> goes wrong, rollback and send yourself the logging table so you can see
> >> exactly where the process failed, and why. If it all succeeds, commit
and
> >> set whatever property you use to handle versioning of the back end.
> >>
> >> If you are comfortable programming in another language (such as pure
VB),
> >> you can add a reference to the DAO library, and perform the same steps
as
> >> described above from an executable rather than an Access MDB. This
> > approach
> >> is suited to the runtime where Access may not be installed.
> >>
> >> I suggest you don't perform the update with DDL query statements
because
> >> they don't let you set important properties such as AllowZeroLength for
> > text
> >> fields. Likewise, ADOX won't allow you to set a Format or Caption on
the
> >> field - not a big deal, but it can be useful to set the Format of a
field
> > to
> >> percent or currency, and to set the DisplayControl for yes/no fields to
> >> check box.
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Bob Howard" <Bob@xxxxxxxxxxxx> wrote in message
> >> news:OJIVhg%23iFHA.3300@xxxxxxxxxxxxxxxxxxxxxxx
> >> >
> >> > My application is deployed as a front-end / back-end Access 2000
> >> > application
> >> > (on any Windows platforms using Win98 or higher) and is optionally
> >> > packaged
> >> > / distributed with Access 2000 Runtime (the CD version has the
runtime
> >> > library --- the download version does not).
> >> >
> >> > This application is distributed to various clients and uses Inno
Setup
> > for
> >> > installation of all software, documentation, shortcuts, etc. During
> >> > the
> >> > install, the user selects whether this is a full install (in which
case
> > an
> >> > empty back-end database is restored in addition to the programs and
> >> > shortcuts), or the user selects that this is a new version upgrade
(in
> >> > which
> >> > case the back-end database is left alone and eveything else is
> > replaced).
> >> >
> >> > During startup, the front-end MDE locates the back-end MDB and
> >> > refreshes
> >> > all
> >> > the back-end links (if the location has changed since the prior
> >> > execution).
> >> >
> >> > The next version of the application requires an update to the
structure
> > of
> >> > the back-end by adding about 4 (maybe 5 --- the design is not
complete
> >> > yet)
> >> > new tables, adding relationships among these new tables (some will
have
> >> > referential integrity with cascading deletes, and some will have
> >> > referential
> >> > integrity and not cascade deletes), and adding a relationship between
> > one
> >> > of
> >> > the new tables and one of the existing tables in the database
> > (referential
> >> > integrity with cascading deletes).
> >> >
> >> > When previous new versions were distributed, all that was required
was
> > to
> >> > replace the front-end (as all I was releasing was new application
> >> > functionality using the existing structure and data).
> >> >
> >> > But the next version will be different due to the additions to the
> >> > back-end
> >> > structure.
> >> >
> >> > Can someone point me to a decent technique that I might employ to
> >> > update
> >> > the
> >> > back-end MDB's structure while preserving the client's data that's
> > already
> >> > within that database?
> >> >
> >> > Thanks in advance! ! !
> >> >
> >> > Bob (@Martureo.Org)
>
>
.
- References:
- Need technique to programatically update back end structure / relationships
- From: Bob Howard
- Re: Need technique to programatically update back end structure / relationships
- From: Allen Browne
- Re: Need technique to programatically update back end structure / relationships
- From: Bob Howard
- Re: Need technique to programatically update back end structure / relationships
- From: Allen Browne
- Need technique to programatically update back end structure / relationships
- Prev by Date: RE: job applicants data base
- Next by Date: Re: many to many problem
- Previous by thread: Re: Need technique to programatically update back end structure / relationships
- Next by thread: Re: Need technique to programatically update back end structure / relationships
- Index(es):
Relevant Pages
|
