Re: Need technique to programatically update back end structure / relationships

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)


.

Relevant Pages