Making mde fe read only

[Stephen J. Levine MD]

Just tried another experiment where I made my mde front
end read only through its windows file properties. I had
to add code in the error handling routine for the startup
properties to ignore a 2057 error and resume next. I did
not consider this a problem because I executed the mde
prior to making it read only to ensure proper setting of
the startup properties.

Other than the warning at the beginning of execution,
stating "The database 'OBI Event Management and Tracking'
is read-only. You won't be able to save changes made to
data or object definitions in this database", the software
seemed to function fine. This included adding records,
editing records, and even deleting an improper
relationship and table in the backend database that was
interfering with the saving of certain records due to
referential integrity violations.

In order to use the front end this way, I would need to
eliminate the warning message because of the confusion it
would create -- setting fe read only does not interfere
with the manipulation of data in the back end database.

Might this be a possible way of avoiding corruption of the
fe during heavy usage if it were centrally located and
linked by shortcut?

The reason I am still pursuing this is in part due to the
wishes of our Regulatory Affairs people who are also
concerned about version control when you have individual
copies of a front end. They agree that you would have to
prove that your method of installing updates would either
eliminate all outdated copies of the front end or render
them inoperable.

sjl

 

[Albert D. Kallal]

The reason I am still pursuing this is in part due to the
wishes of our Regulatory Affairs people who are also
concerned about version control when you have individual
copies of a front end. They agree that you would have to
prove that your method of installing updates would either
eliminate all outdated copies of the front end or render
them inoperable.

I don't know about you, but for the last 20 years of using software, most
software has had some version number built in. Are you telling me that you
never used word, and upgraded it? You never used Excel, and upgraded to a
new version. All these types of software check the file version when
loading.

You kind would have to be hard pressed to not have used, or heard of putting
in a version number in your software? (you do have gathered a good deal of
experience in using software over the years...right? So, lets put that
knowledge to use!).

Why not just put in a version number, and use that? That is what practically
every piece of software on the planet that has corresponding data files
does. From using a appleII many years ago right on up to today, I can't
think of a piece of software that does not check the version of the data
file....can you?

Try opening a old excel document, or old word document. Software always nags
and checks the data file to see if it matches the appellation. I can assure
you that in 99% of the cases, what is done is a version number is checked.
This is not a issue just for ms-access, but just about any software written
for any system. You too should check a version number like everyone else
does.

Worse, according to your comments, this is not only a good idea, but a
requirement in your case!


Right before you deploy a new version, you simply update that version number
in a table in the back end, and then also update this version number in the
front end. I mean, what 1, or two lines of code in your start up routines.

Heck, some air code that should work could be:

if dlookup("Vnum","tblLocalStuff") <> dlookup("vnum",tblBackEndStuff") then
msgbox "wrong version"
application.Quit
end if

What exactly is the problem we are trying to solve above?


Further:

Might this be a possible way of avoiding corruption of the

fe during heavy usage if it were centrally located and
linked by shortcut?

Well, gee it might prevent damage to the front end, but then again, is that
our main problem we trying solve? This solution most certainly will NOT
reduce network traffic by much, which is the primary reason for increased
reliability.

So, it still means your back end data file can be damaged, and we are simply
trying to reduce damage to the data. I don't really think that damage to the
front is the real problem here (is it?). I would be worried about the data
part.

On the other hand, as mentioned, most software has a version number to check
against the data file. I would suggest that regardless of whatever you do
with the front end, you still should put some code in that check the
version. In fact, even if you want, and continue to risk running a shared
front end, I would STILL put in something to check the version number since
that is what most software out there does now.

 

[Stephen J. Levine MD]

Albert

I think that you may be oversimplifying the problem.

The problem, as I and the regulators see it, is not
related to seeing the version number on the computer where
I know it is installed. The problem is the computers that
you may not know about where the software is installed.
In that situation, you cannot depend on checking the
version number.

Anyway, the regulators specifically exempt Word, Excel and
other general software like that because they are not
involved directly in process control. On the other hand,
they do not exempt access applications because they
are "intelligent" and as such can be used in process
control. Where the application is used to store and
retrieve data critical to control of a process, then you
must be able to assure the regulators that your data is
reliable. Use of an outdated version of a piece of
software can jeopardize that reliability. Thus you must
be able to insure that an outdated version of the software
cannot be used.

Have you done any projects for pharmaceutical, medical
device, or blood banking customers or any other regulated
entity?

sjl

 

[Stephen J. Levine MD]

How have you guaranteed that no outdated version of an fe
is available for use?

How have you validated that any scripts, etc., that you
use to manage your fe versions ensure that only current
versions are in use and not previous versions?

Have you ever done any projects for a pharmaceutical
company, medical device manufacturer, blood bank or other
regulated entity?

sjl

 

[Stephen J. Levine MD]

Albert

I am rereading your response and I think the suggestions
are good.

Certainly, one can require that a version number be
confirmed by a user as part of a Standard Operating
Procedure and this has been done in software written for
our business.

The version check for compatibility has also been used in
our business and would be very easy for me to do as I have
made the version a part of the database properties of both
front end and . I have not required that the version
numbers match in all three components of my system but
that would be easy to do (I already have experience
reading database properties from another database <g>).
Your suggestion to do this will be adopted with the next
version as a double check). I would just change the
version number property of the back end each time I
released a new version of the front end.

I am still not sure how having a front end on the client
reduces network traffic. When you bring up a form, do you
not work with that form loaded in your front-end
processor's memory? You still are dealing with tables at
the back end.

Anyway, I do want to say that I have learned a great deal
from this discussion and thank you for your time.

sjl

 

[Stephen J. Levine MD]

Impressive credentials.

Apparently the use of a version check is very common. I
like that solution and can implement it very easily. I
already store the version in both the database and fe in
custom properties I created. The only change I would
have to make would be to update the version of the
database to be that of the fe and then compare the two.
And that I would have no problem doing. It is an
excellent solution that meets my requirement that
outdated versions, whereever they are, not be
functional. In fact, by adjusting the emailed error
reports that the software currently puts out to identify
the PC on which the application is being executed, we can
identify those outdated versions where they are at and
retrieve them from that PC. That will be the route we
take.

I want to thank you and your colleagues for all of your
help. I have learned a lot.

sjl

 

[Stephen J. Levine MD]

Actually, I was hoping to use windows NT/2000-level
security to lock down access to the database, other than
by the application. I have to experiment with this. If
this does not work, then I will have to look at other
security options.

For the front end, I would like to advantage myself of
the windows logon. The reason for this is that the user
has to remember so many different logon and password
systems that I do not wish to add another. Since I
capture the windows NT logon, I believe I can use this to
secure forms, etc. I already have experience, through
the use of triggers, securing Oracle tables through the
operating system logon (osuser from the v$session dynamic
view) and interestingly, when I use Access as a front end
to Oracle tables on a unix database, the NT logon is what
Oracle sees as osuser.

In fact, I may end up substituting Oracle for the backend
database as I can secure the tables easily from
additions, changes and deletions using oracle triggers.
I already have a system that I wrote that does this.
What stops me right now from doing this with the
application under discussion is that via ODBC, I believe
that you cannot take advantage of commits and rollbacks.
I would like to see whether this is true with ole db.
The attractiveness of Oracle for doing this is that,
like Access, we have licenses because our main vendor
application runs on it and we have to do reporting off of
it anyway.

All of this is fluid and subject to change. Any
suggestions would be welcome.

sjl

-----Original Message-----
Are you using user level security provided by Access? If not ..

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb; [LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Albert

I think that you may be oversimplifying the problem.

The problem, as I and the regulators see it, is not
related to seeing the version number on the computer where
I know it is installed. The problem is the computers that
you may not know about where the software is installed.
In that situation, you cannot depend on checking the
version number.

Anyway, the regulators specifically exempt Word, Excel and
other general software like that because they are not
involved directly in process control. On the other hand,
they do not exempt access applications because they
are "intelligent" and as such can be used in process
control. Where the application is used to store and
retrieve data critical to control of a process, then you
must be able to assure the regulators that your data is
reliable. Use of an outdated version of a piece of
software can jeopardize that reliability. Thus you must
be able to insure that an outdated version of the software
cannot be used.

Have you done any projects for pharmaceutical, medical
device, or blood banking customers or any other regulated
entity?

sjl

.

 

[Albert D. Kallal]

Albert

I think that you may be oversimplifying the problem.

The problem, as I and the regulators see it, is not
related to seeing the version number on the computer where
I know it is installed. The problem is the computers that
you may not know about where the software is installed.
In that situation, you cannot depend on checking the
version number.

Yes, actually you can!

It is not that you the user sees a version number, but the software CHECKS
the version number!

The trick is to always put a version number in the front end software. And,
then have a table in the back end. When the front end is launched, you check
for a version match. As mentioned, this is what most software does, and you
can do this also.

In fact, I do this all the time with my software.

If a old version of the software is run, you can code in a message that
prevents the application from loading. However, since you did not put in a
version number check now, you WILL HAVE to make sure that no old copies are
lying around! However, you have that problem right now anyway. You don't
know if some users did copy that front end! If you had put in a version
number check in your software, then this problem would not exist!

Have you done any projects for pharmaceutical, medical
device, or blood banking customers or any other regulated
entity?

The issue here is simply to put in some code in your application that checks
the version. Trying to only allow one front end DOES NOT FIX this problem.
As mentioned, it is only a few lines of code.

You may need to further explain why a version number in the software will
not fix, and prevent old versions of the software from working with that
back end data?

And, I see no or little reason to implement ms-access security, as that is
again a some what of a different issue as to having the front end check, and
match a version number in the back end.

You don't need ms-access security to have the front end check the back end
data for a version match.

 

[Albert D. Kallal]

I am still not sure how having a front end on the client

reduces network traffic. When you bring up a form, do you
not work with that form loaded in your front-end
processor's memory? You still are dealing with tables at
the back end.

Yes, the form is loaded into memory...but where did the form load come from?
If the form resides in a front end on the server, then that form has to be
send down the network! (how else can it get to your pc). This will cause
network traffic.

Each form is only loaded when needed. Golly, it would terrible if all forms
got loaded at once! So, if that form is in a front end that is on the
server, then that form has to be sent down the network wire to be loaded and
run on your pc. This will cause network traffic.

So, in fact all reports, forms, code etc will be transmitted down the
network wire. Thus, any form, report or code loading will cause additional
network traffic. This is no different then placing a document on your local
hard drive vs placing it on the network. If the stuff you need is on the
network share, then that is where it will come from, and of course this
causes network traffic.

If you put the front end on your pc, then none of the application (forms,
code etc) is sent down the network wire. All of that application will reside
on your pc. The only thing you now will be loading from the network is the
data, but not the forms, reports, and code. In other words, the application
part stays on your computer, and only data is sent.

If you place the front end on the server, then it has to be send down the
wire to your pc to load that form. This causes extra network traffic.

And, as mention, right now you always install the appcltions on your pc
anyway...and should do the same with your applications you develop also.

And, also as mentioned, there are some con currency issues that can crop up.
So, placing a front end means that it don't have to be loaded across the
network, and you also get rid of possible concurrency problems that may also
exist (like using temp tables etc.).

This also of course means that a well written application will cause less
network traffic, and generally behave better also. For example, it is
considered poor practice to simply open up a form connected to a table. That
form should ONLY be opened with some restrictions as to what record needs to
be loaded. What this means is that you generally should prompt the user as
to what record they want BEFORE YOU load the form that is attached to a
large table. So, if for example you launch a form that is attached to a
table without FIRST determining what record to load for the form, then your
designs are going to waste precious network bandwidth. Your network is the
most valuable and weak link in the system. You have to treat it with velvet
gloves. For some ideas on searching, and prompting the user *BEFORE* you
load the form, you can read the following of mine:

http://www.attcanada.net/~kallal.msn/Search/index.html

 

[Stephen J. Levine MD]

Actually, I have already made version information custom
properties of both the back end and front end. And I
have had quite a bit of experience accessing, reading,
and even manipulating properties in one mdb, or even an
mde, from another. Unfortunately, some of this
experience came from having to "rescue" applications by
resetting their startup properties. But I am now very
comfortable with these techniques.

The bottom line is that I am now comfortable with
automatic front end/backend version validation as a
methodology as effective a means of version control (if
not more so) than centralizing the fe. Since my
application already emails me error reports, I will have
such a report be mailed to me when a computer fails fe/be
version validation and am researching how to include the
full computer name on this report so that I can identify
the computer on which the outdated version resides. I
can then dispatch our "e-marines" (AKA network
administrators) to rectify the problem. This will be
especially important in that we intend to deploy a future
release of this application organization wide within the
year.

sjl