Need a little advice on frontend setup

[DawnTreader]

Hello All

currently i have a database project where we have a backend that stores all
our data and 3 frontends with one reporting "front"end.

the frontends are seperated by the type of user that uses them. we have set
up a system for international reps to login to a terminal server and use a
front end designed for them. internally this frontend is also used by those
who we dont want to give full editing access to. in the internal cases we
have each user have it locally on thier harddrives.

the second frontend is for the accounting person who needs access to create
warranty credit invoices. this small frontend is only allowed to see very
specific data and then only do one part of a larger process concerning
warranty claims.

the third is the "master" frontend. it is here the my department manages all
sorts of data that the system holds. we use this frontend to have full access
to all the editing of every record.

the other "frontend" is really a bunch of queries that use the linked tables
to generate data for a few excel spreadsheets where we have pivot tables and
charts to get the kinds of reports we need.

my question is this: would it be possible to merge all these systems into
one frontend that would look at the data in a table as to the type of user
that is using the database, and thier rights? we currently have each frontend
look for the user that is logged in and set certain options depending on
information stored in the employee table already, but that code works off of
the appropriate startup form in each frontend. i have thought about creating
a hidden startup form that does the user finding and then opens the
appropriate startup form, but i am wondering if there is a way to find the
user in code and then make decisions in code at startup without opening any
forms.

there are all sorts of security decisions being made at startup and i would
like to move them off of the startup forms and have them happen upon opening
the frontend. is this doable?

any and all help is appreciated.

 

[George Nicholson]

If their Windows login is sufficient:

http://mvps.org/access/api/api0008.htm
API: Get Login name

It would certainly be possible to automatically identify the user and then
display a specific form when they login, based on values stored in your
already existing employee table.

 

[DawnTreader]

Hello George

yeah that is the code that we are using for login capture.

part of what i wanted to know is can i do things like:

CommandBars("Menu Bar").Controls("File").Controls("Close").Visible = False

before any form opens based on the user "settings" and how would i do that?
would i still need a hidden startup form or can it all be decided in code? i
am just thinking of where would i get the information, i know it is in the
table, but should i dlookup, sql or what? i suppose the whole startup would
need a function to determine the user settings and pass that to the forms
that would start up based on the user.

the main reason for doing this is that having multiple frontends with
multiple forms, reports and queries in each that i have to duplicate based on
the frontend that it is in and this is becoming totally unmanagable and
trying to document everything is nuts to say the least. i think that merging
all the frontends and then setting up the interface based on the user might
pull together a lot of duplicated effort and make the whole system more
managable and more thoroughly documented and easier to follow. plus there are
features that i want to be available to the 2 main frontends, but duplicating
things sometimes causes confusion on which was the one that i "fixed" and
where i should be copying it to.

its all a question of managing the project better.

thanks for your feed back!

 

[Klatuu]

Sure can. Use the each form's Load event and set it up as necessary
depending on who the user is.

 

[DawnTreader]

Hello Klatuu

i want to thank you both, Klatuu and George, for your help.

i dont know if it is my communication style or i am just not good at writing
about what my questions are, i am feeling that i am not getting the full
answer that i am seeking.

to quote myself:
"i have thought about creating a hidden startup form that does the user
finding and then opens the appropriate startup form, but i am wondering if
there is a way to find the user in code and then make decisions in code at
startup without opening any forms."

the most important question for me right now in that statement is the last
part.

can i find a user as they open the db with no forms open, then look at
information about that user and then still without any forms open cause all
the necessary settings like "CommandBars("Menu
Bar").Controls("File").Controls("Close").Visible = False" to be set from code?

the reason i am asking these questions is 2 fold.

first i am considering merging all my frontends and second the major concern
here is security. if i merge my frontends can i be near absolutely sure that
the users are not going to be able to somehow get into another "frontend"
module? if the international users log in is there something that they can do
that might cause the interface to allow them to open another part of the now
merged frontend?

i have in the international frontend set the db window to hidden, the
special keys is unchecked, the rightclick menu is custom, toolbars and menus
all locked down and the default menu is being cleaned of things that can open
holes in the security of the interface, like the save or save as and the help
menus very unsecure sample databases choice, but i am unsure i am catching
all security "holes". i dont even like the ability for the end user to change
the printer, but there is no solution that i have found to allowing the
international people to print reports without the abilty to change the
printer to thier local printer. the problem is that they can use this dialog
to go browsing the networks that the terminal server is part of and see our
network here at headquarters. makes me wonder if they will be able to hack
something.

and just because it hasnt happened yet, doesnt mean it wont happen. i would
rather be ahead of the security issues than dealing with the repercussions of
an attack.

so, if anyone has any suggestions, insights and things they know that need
to be done in a situation like this i would sure appreciate the help. is
there any kind of guide to access thin client security out there?

 

[DawnTreader]

Hello Klatuu

one question i thought about after rereading your post was, is the load
event before the open event? where is the best place to do enabling and
diabling of controls, checking for the form settings before the user sees it
and determining what subforms and subreports should be visible?

currently i have been using both and finding that somethings work in some
and not the other, as well i have had the order of code within an event cause
problems as well.

do you know of where i can get an order of events guide?

 

[George Nicholson]

part of what i wanted to know is can i do things like:

CommandBars("Menu Bar").Controls("File").Controls("Close").Visible = False

before any form opens based on the user "settings" and how would i do
that?

One approach to consider (variations are endless):
- Don't set a startup form (or if you do, make it a simple "Loading,
Please Wait..." splash screen)
- Create a macro called AutoExec. This will run automatically when the
file opens (assuming Shift isn't pressed or ShiftBypass is turned off). The
only thing this macro does is call a code procedure, lets call it
"DatabaseStartup". (Alternative: call DatabaseStartup from the SplashScreen
Form_Open event)
- DatabaseStartup is where all your ID checking, UI setup, version
checking, table relinking, and other initialization takes place. At the end
of DatabaseStartup, close SplashScreen (if used) and open the appropriate
form, based on the user's identity.

would i still need a hidden startup form or can it all be decided in code?
i
am just thinking of where would i get the information, i know it is in the
table, but should i dlookup, sql or what?

I've never found a use for a hidden startup form myself, but that's personal
preference. Sounds to me like you can do pretty much everything you have
described so far in code. To pull a single value from a table based on the
user's login, I'd use Dlookup myself. Just remember to 1) wrap it in NZ() to
avoid an error if no matching record is found, and 2) include an appropriate
response in code if the result tells you that no match was found:

strStatus = NZ(DLookup("UserStatus","tblEmployees","UserID = '" &
GetUserLoginAPI & "'"),"")
Select Case strStatus
Case ""
'No matching record is found (or possibly, no UserStatus value
in table)
Msgbox "Who are you and what are you doing in my database????",
vbCritical,"UNAUTHORIZED USER"
Application.Exit
Case "SimpleUser"
'Continue processing
Case "Manager"
'Continue processing
Case "UberUser"
'Continue processing
Case Else
' Unexpected (?) UserStatus value
End Select

 

[Klatuu]

I recommend the Load event because at that time, the form is not yet visible,
but all the control lreferences have been established. That is not always
true in the open event. The open event is useful if you make a determination
at that time that you don't want the form to open. For example, if a user
tried to open a form they were not allowed to use, you would use Cancel =
True and the form would not load at all.

As to your question regarding a startup form. I don't think it would be
necessary or even that useful provided you put code in the Load or if
necessary the Open event to set control permissions, hide controls, etc.

As to security. If you deliver an mde file rather than an mdb and keep the
database window invisible, that will keep them out of most things. The other
thing that is very useful is if you set the AllowBypassKey property to False,
that will prevent a user from holding down the shift key when they open the
database to bypass the startup form. It is not something you can do without
some code or a utility to do it extenally. I have the Developers Extensions
and you can set startup options and lock it down pretty hard. It would take
someone who really knows Access to be able to open it back up, but with an
mde, the user can't change anything. If you go to this site:

http://www.jamiessoftware.tk/downloads.html

And look for the "Download Property Editor", it is a freeware utility that
will allow you to externally modify the startup properties and create an mde.

I hope I have answered all your questions. If not, please post back.

 

[Fenix Bird]

Hello Klatuu

i want to thank you both, Klatuu and George, for your help.

i dont know if it is my communication style or i am just not good at writing
about what my questions are, i am feeling that i am not getting the full
answer that i am seeking.

to quote myself:
"i have thought about creating a hidden startup form that does the user
finding and then opens the appropriate startup form, but i am wondering if
there is a way to find the user in code and then make decisions in code at
startup without opening any forms."

the most important question for me right now in that statement is the last
part.

can i find a user as they open the db with no forms open, then look at
information about that user and then still without any forms open cause all
the necessary settings like "CommandBars("Menu
Bar").Controls("File").Controls("Close").Visible = False" to be set from code?

the reason i am asking these questions is 2 fold.

first i am considering merging all my frontends and second the major concern
here is security. if i merge my frontends can i be near absolutely sure that
the users are not going to be able to somehow get into another "frontend"
module? if the international users log in is there something that they can do
that might cause the interface to allow them to open another part of the now
merged frontend?

i have in the international frontend set the db window to hidden, the
special keys is unchecked, the rightclick menu is custom, toolbars and menus
all locked down and the default menu is being cleaned of things that can open
holes in the security of the interface, like the save or save as and the help
menus very unsecure sample databases choice, but i am unsure i am catching
all security "holes". i dont even like the ability for the end user to change
the printer, but there is no solution that i have found to allowing the
international people to print reports without the abilty to change the
printer to thier local printer. the problem is that they can use this dialog
to go browsing the networks that the terminal server is part of and see our
network here at headquarters. makes me wonder if they will be able to hack
something.

and just because it hasnt happened yet, doesnt mean it wont happen. i would
rather be ahead of the security issues than dealing with the repercussions of
an attack.

so, if anyone has any suggestions, insights and things they know that need
to be done in a situation like this i would sure appreciate the help. is
there any kind of guide to access thin client security out there?

Hello Dawn...

I'm not an expert but I might have a few suggestions for you.

Here it goes...

You can absolutely combine all of your front end db. If all the data
you are talking about is all related then, one back end and one front
end. There's a lot of discussion about this; my opinion is that is the
best option, provided you are the developer of the application.
There's so much benefit that I'll be here all night, so in short I
recommend you go with that option.

Basically, what you need is a "Log In" form on your application.

Once the user log on, you set the user to a registry value or a ini
file.

You will also need a table for holding your users information, some
thing like UserId, UserName, UserPass, etc. This table can also be use
to set users permissions to be use on forms; like AddEmp, DelEmp,
ViewEmp, etc. This table will also be used with the "Log In" form to
select user id.

Finally, you create a function to read the user id from the registry
or ini file then compare it to the "Users" table.

Then simply from the Form's Open event you run your function and
either set the hidden property to true or close the form.

My users can "view only", "Add/Modify" or "Nothing (close the form)"
on all of my forms. Plus, if there's a button on a form that "launch"
any custom process, I use the function I mentioned above to "check for
valid access". I'm 100% sure that they can't see any form that they
are not supposed to. So I think your data will be save.

This might sound confusing but believe me, is easier than you think.
I'm running several applications at work using the above idea and they
work flawlessly.

Latter on, I'm planning on stripping all the "Legal Stuff" on one of
my DB's and share it. I believe it would help a lot of people.

There's quite a lot of good sites for ms access with tons of examples,
try google.

Well, hope this helps...

 

[Klatuu]

There are two good uses for a hiddend startup form.
You can maintain a presistent connection to your back end which will help
with response time in the application.
It is also useful if you put a timer event in the form and periodically
check a field in the form's record source. Based on the field value, you can
warn the user the system is being taken down and cause the app to close. You
can also use it to check for a period of inactivity (the user went home
without closing it) and quit the app.

 

[George Nicholson]

Sorry, should have said that my apps are all designed around one main form
that never closes until the app does, so I've never had a compelling reason
for a hidden startup form.

 

[DawnTreader]

Hello All

WOW. thanks. this is great stuff.

i actually have my current situation set up the same way george. i have one
main form that doesnt close and this maintains the persistent connection. but
klatuu's ideas are interesting to me as well.

for me to merge the frontends and put all the user checking in place is
going to take sometime but it will be well worth it.

 

[Klatuu]

In that case, you don't need one. You can do the same thing with that form.

 

[Klatuu]

You bet it will. It is one of those "Pay me now or pay me later" situations.
Granted it will be a lot of work, but think of the maintenance hassles you
will avoid in the future.

And, we haven't even discussed a user automatically updating to a new
version of the application yet <g>

 

[Klatuu]

Good info, Fenix, but I do disagree on one point.
An ini means you have another file to deal with.
Using the registry has issues. The biggest of which is you have to be
running as an administrator in newer versions of Windows to be able to write
to the registery.

A better solution is to use an application level property. That keeps it
internal to your application. Of course, you have to test to see if the
property exists and create it if it doesn't exist, but once created, it is
presistent.

Here is an example:

Function ChangeProperty(strPropName As String, varPropType As Variant,
varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

 

[DawnTreader]

Hello again

yeah. i already have maintenance hassles. there are so many queries in all
the different frontends that i have no idea what half of them are for and if
i make a change in one that is already there it may screw something else up.
so at this point i dont change queries that already exist, i make new ones,
sometimes from scratch to do something i could have swore i already did once
before.

i am looking into the whole autoupdate thing, the one problem i forsee is
the international users. when they log in the terminal server allows them to
only use the frontend file and when that file is close it tosses them off the
server. there is no start, windows explorer or other programs that can be
accessed by the users. this is to maximize our security.

but if the database has to close to update itself, how do i keep the user
logged in to keep the terminal session?

 

[Klatuu]

Since I am not familiar with using terminal services, I can't say how this
would work, but the utility I use (downloaded and modified for my purposes
from http://www.granite.ab.ca/access/autofe.htm) does this:

The app and the updater both reside on the user's computer.
The app has a one record, one field local table that has the version number
in it. The be has a similar table that identifies the most current version.
When the app launches, it compares the version numbers. If the app version
is < the be version, it launches the updater and quits.

The update then renames the app.mde to app_backup.mde, the copies the new
app fe from the server to the user's computer, launches the app and quits.

It all looks seamless to the user, so I don't know if that would work in a
terminal services environment.

 

[Fenix Bird]

Good info, Fenix, but I do disagree on one point.
An ini means you have another file to deal with.
Using the registry has issues. The biggest of which is you have to be
running as an administrator in newer versions of Windows to be able to write
to the registery.

A better solution is to use an application level property. That keeps it
internal to your application. Of course, you have to test to see if the
property exists and create it if it doesn't exist, but once created, it is
presistent.

Here is an example:

Function ChangeProperty(strPropName As String, varPropType As Variant,
varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Hello Klatuu..

You are absolutely right.

Ini files is a no-no for me. I'm using the registry now, and it's
working because all my users are on the Administrator group (I know,
bad Idea but there's a reason.. LOLOL).

I'll look to your suggetion and most likely adapt it.

Many, many thanks, it's nice to learn something new everyday!

 

[Fenix Bird]

Since I am not familiar with using terminal services, I can't say how this
would work, but the utility I use (downloaded and modified for my purposes
from http://www.granite.ab.ca/access/autofe.htm) does this:

The app and the updater both reside on the user's computer.
The app has a one record, one field local table that has the version number
in it. The be has a similar table that identifies the most current version.
When the app launches, it compares the version numbers. If the app version
is < the be version, it launches the updater and quits.

The update then renames the app.mde to app_backup.mde, the copies the new
app fe from the server to the user's computer, launches the app and quits.

It all looks seamless to the user, so I don't know if that would work in a
terminal services environment.

Hello Klatuu...

I use the same concept but I developed it completely from the ground
up.

I have a mde called xxxUpdater.mde, it contains all the coding for the
update and it's located on the same path as the BE. When the user runs
the FE it detects the new version, run the mde, close the FE, do
update, open new FE !

Basicaly, I move the current FE to a backup dir on the user pc then
copy the new FE from the server to the user pc.

It's been working flawlessly. Actualy, when there's a new version the
user have to perform the update or the app just quit.

 

[Klatuu]

Same here, but mine doesn't give them the option. It just notifies the user
it is updating and does it.