Multiple Users - One Server - No BE/FE Required - Help

[bhipwell via AccessMonster.com]

Hello,

Here is the situation:

- We have a server in our office (blazing fast might I add) that is going to
house our Access Database
- The database is to be accessed by our clients (100+, but can't see more
than 10 or so simultaneously)
- Our clients will login into our server which will auto fire the the access
program.
- We don't want our client to do anything technical besides to have a
shortcut to our server IP (this elimnates installing FEs)

Questions
How do I provide multiple user access to the same database (it seems mutliple
users can access the same form, but not the tables).

Any help is appreciated!

B

 

['69 Camaro]

Hi.

- We don't want our client to do anything technical besides to have a
shortcut to our server IP (this elimnates installing FEs)

If you plan to use 100+ Access databases, one for each user, then you won't
need those front ends you've eliminated from your database design. The
users can't all open the same Access database file over the network without
severe consequences.

How do I provide multiple user access to the same database (it seems
mutliple
users can access the same form, but not the tables).

You need to design the database application for multiusers, not a single
user -- which means you need a split database (front end and back end), with
a copy of the front end on each user's workstation. Sharing a networked
database designed for a single user causes numerous problems, the most
obvious one you have already encountered, and that's record locking issues.
One problem that is going to become obvious eventually is the increased
likelihood of database corruption, since the Access development team has
determined that sharing an Access database across the network is the number
one cause of database corruption. But you'll also run into file update
problems, since everyone will have to get out of the database application
whenever a new version of the database application is ready to be deployed.

For more information on implementing a multiuser Access database, please see
Access MVP Tom Wickerath's article, "Implementing a Successful Multiuser
Access/JET Application" on the following Web page:

http://www.Access.QBuilt.com/html/multiuser_applications.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[bhipwell via AccessMonster.com]

Perhaps I was unclear.

The end users are going to be customers of ours. Thus, we don't want to
install any FEs on their systems, which all would be different anyway.
Instead, what I intend to do it have one server host the entire database at
my office. The customers will login as a network user. When they login,
access will automatically open the access form start page. I have plenty of
MS licenses for multiple people to login at once. Can I do this and have
mulitple people access this single database?

(My other idea would be to have each user server sign and load it their own
FE, all of which would be on my server, not there desktop. Would this be a
potential solution?)

Thanks!

B

 

['69 Camaro]

Hi.

Can I do this and have
mulitple people access this single database?

No. However, if you have 100+ directories, each with a copy of the front
end linking to the tables in the back end on your server, and the user logs
into your server, opens their own front end (no sharing whatsoever of this
front end file with any other user at the same time) from their own
directory on your server, then all of the concurrent users will be using the
multiuser database the way it was designed.

If the database isn't split, and more than one customer opens the same
database file on your server at the same time, then you will have to deal
with the problems caused by not deploying a mulituser database the way it
was designed. Would you like me to give you the E-mail address of a
reputable Access database repair service and the phone number of an
insurance company who insures against computer/software application down
time when your Access database is offline due to corruption or necessary
updates and bug fixes? They're both really, really expensive but it will
make your customers happy to be paid a tidy sum for having to wait to use
your Access database. (It's always best to be prepared with a contingency
plan one knows is going to be used sooner or later.) ;-)

(My other idea would be to have each user server sign and load it their
own
FE . . . Would this be a
potential solution?)

I'm having trouble understanding your other idea. Each customer logs into
your server and, as part of the user sign-in/startup process, a unique copy
of the Access front end is opened (so each user has his very own copy that
no one else opens), and the customer sees the Access startup form on his
monitor, even though the front end database file is located on your server.
Is this correct? If so, yes. This is a good alternative.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[George Nicholson]

No matter what you do, you need to have a FE/BE setup. It's not clear that
you have that.

If you don't want your clients to have their own front ends, then you
probably need to recreate your app as web-based (ASP, Data Access pages,
etc.) so that your clients can use a web browser as their front end,
connecting to your server.

Multiple users opening a shared front end file on a network is simply
begging for problems. Access was not designed for that, it doesn't like it
and it *will* eventually let you know that. Probably at the worst possible
time, with the worst possible client.

You could, i suppose, put 100 copies of the FE on a server and have each
client open their own copy, all of which link to a single BE data file.
*But* if more than one user from the same client tries to login to the same
FE file at the same time, you have the same problem... This would be very
unconventional and i have no idea how it would work in practice. I also
doubt you'll find anyone who has experience with a setup like this, but
anything's possible I guess.

Usually the biggest "drawback" to local FE's is that it means
providing/installing local copies of Access. Moving the FE to the network
won't change the requirement for local copies of Access. Unless you go the
web-enabled FE route, each client WILL need a copy of Access on their
machines. The location of the FE is irrelevant to that, they'll need some
version of Access installed (retail or runtime) on their local machines to
open your FE. The only way to avoid that requirement is to create a new user
interface to access your back-end data and pretty much consign your current
forms, reports & other Access-engine-specific functionality to the dustbin.


Good Luck & HTH,

 

[Tony Toews [MVP]]

The end users are going to be customers of ours. Thus, we don't want to
install any FEs on their systems, which all would be different anyway.

Why would each of your customer's FEs be different?

Instead, what I intend to do it have one server host the entire database at
my office. The customers will login as a network user. When they login,
access will automatically open the access form start page. I have plenty of
MS licenses for multiple people to login at once. Can I do this and have
mulitple people access this single database?

However msaccess.exe has to execute somewhere. EIther your users
install it on their own systems and transfer data back and forth which
is *not* recommended on a WAN. Or see the next paragraph.

(My other idea would be to have each user server sign and load it their own
FE, all of which would be on my server, not there desktop. Would this be a
potential solution?)

It's called Terminal Server. Where your client sends keyboard strokes
and mouse entries to the Terminal Server and it sends back screen
updates.

This would be the sane approach for your environment.

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

 

[bhipwell via AccessMonster.com]

Thanks to all of you for your input. Here is the solution that thus far
appears to be working very well.

Each client has a unique log in to our server that contains our access db.
Each user also has their own FE which fires open automatically when they
login into our server under their unique username and password.

All the FE's are stored in on folder on our server while the BE is stored in
another folder. So, for example, CLIENT1 has a unique user login/password to
our server that when they login, fires the FE called CLIENT1FE. CLIENT2 will
log in under a unique user name and password and it fires the FE called
CLIENT2FE.

So far access performs very well. Basically, instead of having an FE run on
indivual's destop, it is running on our server. Access doesn't know that the
FEs and BE are housed on the same system (I have already confimed that our
server can handle many multiples of FEs running simulatenously from a
hardward standpoint, and I hope so, the server wasn't cheap).

A nice side bonus is the fact that I can continue to work on the FE with my
own FE (DEVELOPFE)without interupting anyone elses access to the database.
When it is time to update everyones FE with the new version, all FEs are
located in one spot, no need to visit or email all user, it will appear
seemless to our clients.

Hope this makes sense to everyone. You input was well received and
beneficial for me working the best possible paths.

(As a side note, having users login to our server vs an internet portal
provides better security and control for the functions our clients will be
handling. We maintain HIPAA compliant information that has substantial rules
and regs by the fed.)

B.

 

[Norman Yuan]

Thanks to all of you for your input. Here is the solution that thus far
appears to be working very well.

Each client has a unique log in to our server that contains our access db.
Each user also has their own FE which fires open automatically when they
login into our server under their unique username and password.

All the FE's are stored in on folder on our server while the BE is stored
in
another folder. So, for example, CLIENT1 has a unique user login/password
to
our server that when they login, fires the FE called CLIENT1FE. CLIENT2
will
log in under a unique user name and password and it fires the FE called
CLIENT2FE.

So, the server you are talking is Terminal Server, not a file server or web
server. You should have said that in the first place. Not every servers are
the same "server". If so, I believe, you need MS Access lisence for each
user/client that runs your application on the TS.

 

[Tony Toews [MVP]]

So, the server you are talking is Terminal Server, not a file server or web
server. You should have said that in the first place. Not every servers are
the same "server".

Agreed. That certainly wasn't at all clear from B's postings.

If so, I believe, you need MS Access lisence for each
user/client that runs your application on the TS.

Unless he uses the Access runtime.

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 Toews [MVP]]

Each client has a unique log in to our server that contains our access db.
Each user also has their own FE which fires open automatically when they
login into our server under their unique username and password.

Please confirm that you are indeed using a Terminal Server system.
And not just a file server.

A nice side bonus is the fact that I can continue to work on the FE with my
own FE (DEVELOPFE)without interupting anyone elses access to the database.
When it is time to update everyones FE with the new version, all FEs are
located in one spot, no need to visit or email all user, it will appear
seemless to our clients.

Furthermore the Auto FE Updater handles this situation very nicely as
it is designed to work on Terminal Server as well.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

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

 

[bhipwell via AccessMonster.com]

Tony,

I am running into a problem (mind you, I am not a guru on anything related to
Access or networks). The database is on the server. My intent is to also
house the multiple FE's on the server as well as we don't want to install
items on our clients computers. When clients login to the server, there
specific FE fires open.

As I test, the following occurs:

The good: I can open multiple FE's on the server itself (without loggin in
as the user) and the database responds just fine allowing edits, deletes, etc.


The bad: If I login as the various users, the first individual can get in,
but once some tries to access the same table, I get the "cannot lock table
"XXX" another user" error.

I have verified that the users have full permissions. Any thoughts?

(PS, I sent an email to your in regards to an Ostrosoft attachment error).

Thanks!

Brandon

 

[David W. Fenton]

Here is the situation:

- We have a server in our office (blazing fast might I add) that
is going to house our Access Database
- The database is to be accessed by our clients (100+, but can't
see more than 10 or so simultaneously)
- Our clients will login into our server which will auto fire the
the access program.
- We don't want our client to do anything technical besides to
have a shortcut to our server IP (this elimnates installing FEs)

Questions
How do I provide multiple user access to the same database (it
seems mutliple users can access the same form, but not the
tables).

Are your clients on your LAN?

Or are they coming in over the Internet?

If they are on the LAN, split your app and give each client users
the front end.

If they are *not* on your LAN, then set up Windows Terminal Server
and have them run the app there. For 100+ simultaneous users, you'll
likely need more than one server, and might consider a Citrix shared
server solution (i.e., multiple boxes working in concert
transparently so that the users don't know or care which box they
are actually running their Terminal Server session on).

Another approach might be to not use Access at all and to create a
browser-based app. When you're at 100 simultaneous users, the costs
for implementing Terminal Server can be significant enough to
justify the high development costs of a browser-based application.

 

[David W. Fenton]

(As a side note, having users login to our server vs an internet
portal provides better security and control for the functions our
clients will be handling. We maintain HIPAA compliant information
that has substantial rules and regs by the fed.)

Then I would assume your data is not stored in a Jet MDB, but in a
secure server-based database, such as SQL Server. If not, you're
probably *not* in HIPAA compliance because you simply can't secure a
Jet back end sufficiently to meet HIPAA rules.

 

[Rick Brandt]

Then I would assume your data is not stored in a Jet MDB, but in a
secure server-based database, such as SQL Server. If not, you're
probably *not* in HIPAA compliance because you simply can't secure a
Jet back end sufficiently to meet HIPAA rules.

Why would network security not meet HIPAA rules?

 

[bhipwell via AccessMonster.com]

Then I would assume your data is not stored in a Jet MDB, but in a

secure server-based database, such as SQL Server. If not, you're
probably *not* in HIPAA compliance because you simply can't secure a
Jet back end sufficiently to meet HIPAA rules.

David,

Appreciate your feedback. In regards to HIPAA compliance, we are receiving
strong advice from a legal group who handles these matters. In terms of 100+,
we have 100+ clients, but we cannot imagine more than 10-20 simulataneous
logins. The program we are developing will only require clients to login a
couple times a week for the busiest clients, a couple times a quarter for our
smaller clients. Finally, I am looking at using the server/access model
versus the web based solution as myself, nor another person here has strong
enough knowledge on the web access sort of stuff.

Can you elaborate on the JetMDB and how to get users logging over the
internet to be able to use an FE stored on our server?

Thanks!

B

 

[bhipwell via AccessMonster.com]

Why would network security not meet HIPAA rules?

It can. However, my lack of knowledge on how to build a web based program
currently prevents me from doing a web based design. We currently have a web
based design (non access based), but I am not strong enough to improve better
on.

Thanks!

B

 

[bhipwell via AccessMonster.com]

If they are *not* on your LAN, then set up Windows Terminal Server

and have them run the app there. For 100+ simultaneous users, you'll
likely need more than one server, and might consider a Citrix shared
server solution (i.e., multiple boxes working in concert
transparently so that the users don't know or care which box they
are actually running their Terminal Server session on).

They are not on a LAN, they will connect via internet. Thus, it is easier to
distribute one connection shortcut than what will be multiple FE
distributions over the next 12 months. We 100+ customers, but, we only
forsee 10-20 simulataneous users. Some clients will login in 1-2 a week.
Others will login 1-2 times a quarter. And when they do, they probably won't
be on longer than a few minutes at a time. Right now, I foresee our server
to be more than robust.

Since they are not on LAN, but rather the internet, any thoughts on why my
FEs open locally but when multiples login from the internet, the BE database
is locked? I have checked user security and the right read, write, edits,
etc. are checked.

Thanks!

B

 

[bhipwell via AccessMonster.com]

Solutions found!

Thanks for all your help!

B

 

[Tony Toews [MVP]]

Why would network security not meet HIPAA rules?

I'm putting words in David's keyboard however anyone with network
privileges can take a copy of the backend home and review the data at
thier leisure.

Not possible with SQL Server unless you have network access to the
folder on the server containing the SQL Server files. Which should
*not* be happening.

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 Toews [MVP]]

Why would network security not meet HIPAA rules?

My previous posting wasn't appropriate for bhipwell's environment as
it would appear he is running Terminal Server. Thus the user doesn't
have access to the network share.

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