Replication vs. FE Auto Update

[David Portwood]

Maybe some background first: I designed and deployed a split application
which is currently being used by the office in which I am working. It is a
big hit, and the boss in my office has suggested to Corporate that our other
offices around the country should use the app as well.

Apparently some Corporate big shots have run the app and were impressed
enough to consider the matter. I overheard the term "replication" being used
in a phone call between the boss and the Corporate IT department. (IT
department? So who am I? Just a clerical temp with delusions of grandeur.
But I digress...)

I think what the IT department has in mind is replicating my app in each
branch office throughout the country (there are five or six other offices)
and counting on replication to distribute code updates as well as data
updates. I am to participate in a conference call next week to discuss the
situation.

With help from this group, I developed my own FE auto update code (simple
stuff, but it works quite nicely). I had in mind distributing my auto update
code to each branch office. Long story short - using this code and the
common network I could easily maintain and administer each version of the
app from my own office.

However, in reading about replication there was mention that code changes in
the design master would be automatically incorporated into all replicated
databases. If so, this would replace my FE auto update code and we begin
heading into uncharted waters.

Would the replication process distribute code updates as advertised? Are
there any issues that we should be aware of? Remember that my app is split
FE/BE.

I'm tending toward arguing against replication primarily because there is no
real need for the offices to share data. Each office processes the data in a
similar manner, and therefore each office would benefit from having their
own version of my app, but there is no need for one office to know what
specific data another office has in-house. True, Corporate may want to
compare productivity and performance between offices and could use the data
from each app to do so, but in this case I was thinking each office could
export their data to an Excel spreadsheet. The Corporate office could then
run a separate application to import that data and analyze it however they
like. I believe I could design and code such an application without too much
difficulty.

Ok, I'm talking too much. I am hoping to get some ideas concerning the pros
and cons of the replication approach -particularly as it pertains to passing
along design updates -so I can make the boss sound like she knows what she
is talking about during the conference call next week. Oh yes, in spite of
the fact that the boss knows nothing about Access, all of this is somehow
her doing, and my assistance has been "greatly appreciated".

Comments, please?

 

[Albert D. Kallal]

Apparently some Corporate big shots have run the app and were impressed
enough to consider the matter. I overheard the term "replication" being
used in a phone call between the boss and the Corporate IT department. (IT
department? So who am I? Just a clerical temp with delusions of grandeur.
But I digress...)

It is a big significant difference between replicating your work, or
duplicating your work in each office, and that of the database technology of
replication.

Keep in mind that replication is not designed for one to distribute their
code. In fact since you likely are distributing an mde for the front end,
than replication will accomplish nothing for you anyway (you are using a mde
for the front end...right -- if not, you should be).

I think what the IT department has in mind is replicating my app in each
branch office throughout the country (there are five or six other offices)
and counting on replication to distribute code updates as well as data
updates.

As mentioned, replication cannot be used for the code part. If you need
something to automatic update your code each person's WorkStation, then Tony
has a great free FE updater here:

http://www.granite.ab.ca/access/autofe.htm

With help from this group, I developed my own FE auto update code (simple
stuff, but it works quite nicely). I had in mind distributing my auto
update code to each branch office. Long story short - using this code and
the common network I could easily maintain and administer each version of
the app from my own office.

Ah, very good it seems you already have something that functions and does
the job. Then that's what you should use. What I do for customers that I
don't have access to the physical machines is simply build a custom install
(I use the free open source inno installer). I then simply place that
executable inno install as a link on a web page, and then I simply have a
udpate button in the access application. (I simply launch the link via
application.FollowHyperlink, and the udpate then installs).

The resulting sequence of steps to for a user to thus to update their
software looks like:

http://www.kallal.ca/ridestutorialp/upgrade.html

Would the replication process distribute code updates as advertised? Are
there any issues that we should be aware of? Remember that my app is split
FE/BE.

As mentioned, replication is not intended for code updates, and it seems
that you've built a system anyway. Remember, your FE is a separate update,
and you could probably simply e-mail a person a zipped up new front end. Of
course using an inno script is a bit cleaner, but really all I'm doing is
copying the file (FE) to the person's folder where are the front end
resides. I used to use a paid version of WinZip to send files to my users. I
now use inno because it lets me do things like setup desktop shortcuts etc.

It sounds like you have pretty much a good process now, and were wondering
if replication could solve this better than what you have now (and the
answer is no, it can't)

 

[Larry Daugherty]

Fire the boss!

On the conference call, field the questions that will arise about the
design and explain why you did what you did, etc. Don't apologize to
your boss for that either during the call or after. Don't join her in
her lie.

Your biases regarding your implementation and Replication are in the
correct direction. Replication should not be part of your
implementation. Replication is for keeping the *data* synchronized
between the master and the replicas. Since you don't intend to
synchronize data between all of the offices, Replication doesn't play
here at all.

I recommend that you convert your FrontEnd into an MDE and just send
that MDE as the FrontEnd with your data MDB. Never send your FE.MDB
to the other sites. That will save you untold grief that you might
otherwise have if the other offices were able to alter your design.
In cases where people modify designs and break things, they report
their broken application to management and without mentioning their
own meddling. As you enhance the design you can create a new MDE and
ship that out to the other offices. If you have a good FE Updater
than everyone will get their new copy from their server the next time
they try to use the application.

HTH

 

[boblarson]

I recommend that you convert your FrontEnd into an MDE and just send

that MDE as the FrontEnd with your data MDB. Never send your FE.MDB
to the other sites. That will save you untold grief that you might
otherwise have if the other offices were able to alter your design.

A common misconception is that design changes cannot occur in an MDE. The
reality is that the forms, reports, macros, and modules are protected, but
users with a FULL Access version can still make changes (and therefore screw
it up) to the tables and queries in an MDE file.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[David Portwood]

Thanks for the info, Albert. By the way, your update procedure is
interesting but I don't think it is applicable to my situation.

At present, I don't know much about coding for the web. This is an area that
I would very much like to learn more about.

 

[David Portwood]

I wish I could fire the boss. The boss is a crazy person. My conversations
with her seem to belong in a Dilbert comic strip.

I don't think the boss would accept changing to an MDE file. Both my FE and
BE are secured, and only she and I have privileges to make code changes.
Yes, it scares me that she could change the code, but she requires it.

 

[Tony Toews [MVP]]

A common misconception is that design changes cannot occur in an MDE. The
reality is that the forms, reports, macros, and modules are protected, but
users with a FULL Access version can still make changes (and therefore screw
it up) to the tables and queries in an MDE file.

And said user can change macros. Not that there should be any more
other than autoexec.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Daugherty]

You can roll out your application with the Access Runtime and then run
some code early in the startup to detect Retail Access and, if it's
running, simply put up a warning message and Quit the application.

see MSKB Q103182

There are also several steps that can be taken to make it more
difficult for someone to hack the BE.

HTH

 

[Tony Toews [MVP]]

You can roll out your application with the Access Runtime and then run
some code early in the startup to detect Retail Access and, if it's
running, simply put up a warning message and Quit the application.

see MSKB Q103182

However if you install the runtime on a system with the same version
of Access retail it won't install the runtime. So now the user can't
run your app.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

replication cannot be used for the code part.

This is simply false.

Jet Replication *can* be used, be the Access objects in an MDB are
stored in Jet tables.

But it *shouldn't* be used, as it never works in the long run and
can lead to corruption and loss of the entire Access project.

Jet Replication works very well with pure Jet objects (tables and
queries), and thus is not for front ends, but only for back ends.

 

[David W. Fenton]

I don't think the boss would accept changing to an MDE file. Both
my FE and BE are secured, and only she and I have privileges to
make code changes. Yes, it scares me that she could change the
code, but she requires it.

You are confusing two issues:

1. should the boss be able to change the MDB code?

2. should the users be given an MDE or an MDB?

You can give the boss access to the MDB that is the source for the
MDEs and distribute the MDEs to the users.

 

[Larry Daugherty]

Which definitely protects from users with retail Access mucking with
your design.

The user can install the application into an environment in which
retail Access is not present and run it there.

Microsoft has provided Access with those limitations, not the
developer.

There is rarely a solution that cannot express some unintended
consequences. In this case, it can be a setup option or even a
purchase option as to whether the code runs to disallow retail Access.
Give the client the information to make an informed choice.

HTH

 

[David Portwood]

As it stands now, my mdb files are fully secured and the only Admins are my
boss and myself. No other users can change the code or table/query designs.

With my own auto update code I can make quick updates to the FE at any time
and the changes are automatically distributed locally to all users when they
launch the app. This is very convenient for me since I tend to make small
changes pretty much every time I go into the code. I suppose that sounds
bad.

It's partly because of my neurotic personality, partly because I only
started with Access VBA about eight months ago and as I look back at prior
code I often realize there is a better way it could have been done - so I
change it. And I'm getting constant feedback from users, asking for
additional features. So, lots of little changes, and more than a few big
ones.

I actually don't know much about what it would take to convert and
distribute an mde versus mdb file, but I have the impression that code
updates would not be made as easily. Since everything is fully secured, I
don't see the advantage of mde over mdb and I certainly don't want to lose
the convenience of making quick updates.

I am not a contractor delivering a finished product to a client. I am just a
clerical temp who did some work on spec in the hope of future rewards from a
grateful company. So far, no future rewards. But that's the great thing
about the future - there's always more of it.

 

[David W. Fenton]

As it stands now, my mdb files are fully secured and the only
Admins are my boss and myself. No other users can change the code
or table/query designs.

That's all completely irrelevant to the question of whether or not
to distribute and MDE.

With my own auto update code I can make quick updates to the FE at
any time and the changes are automatically distributed locally to
all users when they launch the app. This is very convenient for me
since I tend to make small changes pretty much every time I go
into the code. I suppose that sounds bad.

You mean you implement changes by coding the changes to the objects?
Why not just copy the new MDB/MDE over top of the old and avoid all
that tedious programming?

It's partly because of my neurotic personality, partly because I
only started with Access VBA about eight months ago and as I look
back at prior code I often realize there is a better way it could
have been done - so I change it. And I'm getting constant feedback
from users, asking for additional features. So, lots of little
changes, and more than a few big ones.

I've been programming Access professionally (i.e., people pay me for
my work) since 1996 and I've never once distributed an app to
clients except by copying over top of the old one.

I actually don't know much about what it would take to convert and
distribute an mde versus mdb file,

Well, then why in the world are you rejecting it out of hand?

but I have the impression that code
updates would not be made as easily. Since everything is fully
secured, I don't see the advantage of mde over mdb and I certainly
don't want to lose the convenience of making quick updates.

1. MDEs can't decompile, which improves performance.

2. MDEs don't lose in-memory variables when there's a code reset
from a bug.

3. MDEs can't be accidentally edited by a user who types through an
error message and ends up in the code window.

I am not a contractor delivering a finished product to a client. I
am just a clerical temp who did some work on spec in the hope of
future rewards from a grateful company. So far, no future rewards.
But that's the great thing about the future - there's always more
of it.

Sounds to me like you're making a lot of extra work for yourself,
whether you distribute an MDB or MDE. I'd never use code to update a
local front end.

If that's not what you mean, then for the life of me I can't figure
out what your objection to an MDE may be.

 

[Tony Toews [MVP]]

With my own auto update code I can make quick updates to the FE at any time
and the changes are automatically distributed locally to all users when they
launch the app. This is very convenient for me since I tend to make small
changes pretty much every time I go into the code. I suppose that sounds
bad.

So do I. See Ultra Frequent Application Deployment
http://www.granite.ab.ca/access/ufad.htm
But I use the Auto FE Updater, link in that article, to replace the
complete FE.

I actually don't know much about what it would take to convert and
distribute an mde versus mdb file, but I have the impression that code
updates would not be made as easily.

Not at all possible. You distribute a few FE MDE though.

I am not a contractor delivering a finished product to a client. I am just a
clerical temp who did some work on spec in the hope of future rewards from a
grateful company. So far, no future rewards. But that's the great thing
about the future - there's always more of it.

Yeah, well good luck. It sounds like your supervisor is taking all
the credit. OTOH this will look good on your resume.

Speaking of which I'd suggest creating an online page somewhere and
link to your Google newsgroup profile. In your resume put a link to
that online page. If I was a prospective employer I'd be impressed
that you're out and about in the online world asking questions and
implementing same.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Portwood]

You mean you implement changes by coding the changes to the objects?

No, of course not.

Why not just copy the new MDB/MDE over top of the old and avoid all
that tedious programming?

My auto update code does exactly that. Essentially, the user double clicks
an icon which he thinks launches my app. In actuality, it launches a version
checker applet which checks the datetime of the most current FE stored in a
"Download" folder on a shared network drive versus the datetime of the FE
which the user is currently using. If the Download folder contains a later
version, the version checker applet copies (overwrites) this later version
to the user's local drive, launches the main app, and closes itself.

This is very simple to code and it looks professional. The version checker
applet opens a form with a text box which displays the messages "Checking
for new version..." followed by "Downloading new version..." followed by
"Current version is up-to-date". My only problem was slowing down the
process so the user had time to read the messages.

I'll look further into the advantages of mde over mdb. If converting to an
mde is a simple compilation process (that is to say, not much extra labor)
which does not affect security, then I will probably go ahead with it.

 

[John W. Vinson]

I'll look further into the advantages of mde over mdb. If converting to an
mde is a simple compilation process (that is to say, not much extra labor)
which does not affect security, then I will probably go ahead with it.

It's a few seconds' work, and can be automated, and it does enhance security
in that users won't be able to open forms or reports in design mode, and will
not be able to see any VBA code.

John W. Vinson [MVP]

 

[David W. Fenton]

No, of course not.

Then I don't understand your objection to the MDE, which I thought
was based on the fact that the code in an MDE couldn't be altered.

My auto update code does exactly that. Essentially, the user
double clicks an icon which he thinks launches my app. In
actuality, it launches a version checker applet which checks the
datetime of the most current FE stored in a "Download" folder on a
shared network drive versus the datetime of the FE which the user
is currently using.

Each time you open the front end, the date/time changes, so how
would your updater ever work?

If the Download folder contains a later
version, the version checker applet copies (overwrites) this later
version to the user's local drive, launches the main app, and
closes itself.

I presume this would work as long as the user never bypasses your
startup app. It would be better, I'd think, to have a version
property in your MDB/MDE and check that, instead of the date/time of
the files involved.

[]

I'll look further into the advantages of mde over mdb. If
converting to an mde is a simple compilation process (that is to
say, not much extra labor) which does not affect security, then I
will probably go ahead with it.

It's essentially a SAVE AS operation. It can be automated, too.

 

[Tony Toews [MVP]]

I'll look further into the advantages of mde over mdb. If converting to an
mde is a simple compilation process (that is to say, not much extra labor)
which does not affect security, then I will probably go ahead with it.

Two mouse clicks and you've created an MDE. Unless you have a error in
your code which could take a few minutes to fix.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Portwood]

Essentially, the user double clicks an icon which he thinks launches my

Each time you open the front end, the date/time changes, so how
would your updater ever work?

The user doesn't open the master FE stored on the network drive. He runs a
copy of the master FE which my version checker downloads to his local drive.