Need technique to programatically update back end structure / relationships

[Bob Howard]

Hi everyone!

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 (@Marturerg)

 

[Allen Browne]

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.

 

[Bob Howard]

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]

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.

 

[Bob Howard]

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.

 

[peregenem]

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.

You are mistaking

CREATE TABLE Test1
(text_col VARCHAR(10) NOT NULL);
--DDL to allow zero length

INSERT INTO Test1 VALUES ('');
-- success

CREATE TABLE Test2
(text_col VARCHAR(10) NOT NULL,
CHECK (LEN(text_col) > 0));
--DDL to not allow zero length

INSERT INTO Test2 VALUES ('');
--failed

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.)

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.

You are mistaking

CREATE TABLE Test1
(text_col VARCHAR(10) NOT NULL);
--DDL to allow zero length

INSERT INTO Test1 VALUES ('');
-- success

CREATE TABLE Test2
(text_col VARCHAR(10) NOT NULL,
CHECK (LEN(text_col) > 0));
--DDL to not allow zero length

INSERT INTO Test2 VALUES ('');
--failed

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.)

Man, foreign keys in DAO are a pain, creating index where I don't want
them! DDL is much nicer FK code, I feel.