new version update

[JB]

I am building an app with security and the usual FE/BE, dev
workgroup/distribution workgroup files structure. My question is how does
one deal with updating the BE for newer versions of the app that require
design changes in th BE tables? One option is to install a new file based on
the new table designs and then run a bunch of queries to populate the tables
using the old version as the source. However, this seems a lot of overhead
if only a few design changes are needed in a database with lots of tables and
lots of records. Eg. copying several thousand records when all that might be
needed is to add one new table and add a new column to an existing table. Is
there any way to get around the security issue that would allow client users
to run code to modify the tables without creating a superuser in the
distribution workgroup or storing the password in vba code?
--
JB

[Rick Brandt]

I have "one-time" code in the new FE file that uses DAO to apply the necessary
changes to the BE table structures. I use that when the changes are relatively
few. When the changes are significant I copy the data from the old file as you
describe in your post.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[TC]

I use Rick's approach, with a twist.

I version-number the data db by means of a custom property within that
db. Then, for each new version of the data db structure, I write a
seperate procedure which updates the previous db structure,
programatically, to the new one.

For example, if I had 3 released versions of the data db structure -
versions 1, 2 & 3 - I would have two seperate update procedures:
update_v1_to_v2, and update_v2_to_v3.

Each FE knows which version of the data db that it expects. (This is
coded-in to the FE.) So, the FE simply needs to do this, in its startup
code:

code db version = (whatever: hard coded)
get data db version
if code db version > data db version then
run each of the relevant update
procedures, in turn, to update the
data db structure to the version
required for this FE.
then update the data db version
number in the data db
endif

Thus, if you ran a v3 FE against a v1 or v2 BE, it would automagically
update the BE structure to v3. If you ran a v2 FE against a v1 BE, it
would automagically update the BE structure to v2, and so on.

HTH,
TC

[JB]

thanks for the replies. I understand the general idea and that is more or
less the approach I will be using for this app. My question was more towards
how to manage this given that client users don't have modify permissions on
the tables. It would be nice if the client could somehow execute code to add
or modify the tables using Owner's permission like they can for queries.
After thinking about it further, I can see that this isn't going to work
without hard coding the owner's name and password into the vba code. Even
then, the client app manager could muck things up by removing the owner from
the wrkgroup, changing the password, etc

It would seem that the only solution is to install a new file in the new
version format and then copy all the data form the previous verison using
queries that the underpreviliged clients can run.

Thanks
--
JB

[TC]

If the normal users do not have adequate permission to update the table
structures, just do it from the workspace of a user who /does/:

(untested)

dim ws as workspace, db as database
set ws = dbengine.createworkspace ("", "SuperFred", "s3cr3t")
set db = ws.opendatabase ("full path toBE database")

I don't have Access here to check the parameters for the
CreateWorkspace() method. But essentially, it takes a valid user name &
password, and creates a workspace with the specified user's priviliges,
regardless of the priviliges of the currently logged on user. You can
do anything, via that workspace, that the specified user could do if he
logged on manually. By this means, a lowly priviliged user can be
permitted to perform highly priviliged operations, under program
control.

This is /not/ a security hole, because if the programmer knows a
suitable username & password (to place in the code), he could have
logged on with that username/password manually & done whatever he
wanted (within that user's permissions).

Naturally you'd want to be sure that the BE database was closed (not
open by other parts of your application) when you ran that code. You'd
also want to build the "secret" username & password from code, eg.
chr$(53) & chr$(45) & chr$(43) etc., so it wouldn't be visible in a
"displayable strings" scan of the mdb file.

As for the client app manager fiddling with the coded-in user, you'd
just have to tell him that that user is essential for proper operation
of the system & he should not fiddle with in. Indeed, you could check
that that user still existed (& was still a member of the Admins group)
on every system open, regardless of whether you needed to use him, or
not!

Or you could do all user/group maintenance via a form which controlled
exactly whay he could & could not do.

HTH,
TC