Migrating to Multiuser use / splitting a database

[Jay]

Hi,

We have a database at work that up until now has been primarily used by one
person, she enters the data and generates the reports. The .mdb file does
reside on a local file server though, and occassionaly will be used by
someone else if our primary user is out sick or something.

We are now thinking of migrating this to a true multiuser situation though
where more than one person will have data entry responsibilities.

My plan was to split the database using the database splitter utility
included with Access, then once it is split letting each person who needs
it to open the front end interface file over the network.

However, I got to thinking that this would still mean that more than one
person might have the same file on the same machine open simultaneously
which doesn't sound like a good situation to be in.

Would this likely cause any problems ? If so what would be the recommended
alternative(s) ? Could I proceed with splitting the database and then make
multiple copies of the front end file, one for each person who would be
using the database ? That way no one would actually have the same file
open at the same time.



Thanks,

Jay

 

[John Vinson]

However, I got to thinking that this would still mean that more than one
person might have the same file on the same machine open simultaneously
which doesn't sound like a good situation to be in.

Would this likely cause any problems ? If so what would be the recommended
alternative(s) ? Could I proceed with splitting the database and then make
multiple copies of the front end file, one for each person who would be
using the database ? That way no one would actually have the same file
open at the same time.

By much the latter is to be preferred.

See http://www.granite.ab.ca/access/splitapp/ for a thorough
discussion of techniques, problems, and recommendations.

John W. Vinson[MVP]

 

[Tom Wickerath]

Hi Jay,

Each user should definately have their own copy of the FE database installed
on their local hard drive.

You may be interested in this article that I have written:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

Each user should definately have their own copy of the FE database
installed on their local hard drive.

You may be interested in this article that I have written:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

Nice article.

May I suggest that you might want to alter two sections:

1. persistent connections: if the performance is dropping because of
the LDB file creation issue, you don't need to open a table (either
in a form or a recordset). All you need do is initialize a DAO
database variable pointing to the back end database using
DBEngine.OpenDatabase. This creates the LDB file without needing to
worry about maintaining a 1-record table or or hidden form. Try it
and see. Of course, it could be that the performance problems stem
from something more than just the LDB file creation issue. In that
case, the recordset/hidden form methods might do more to solve it,
though I'm not sure why.

2. folder privileges: while read/write/create/delete permission is
necessary for everyone to be able to create and delete the LDB file,
if you have *some* users in an admin group who have full
permissions, you can give read/write/change and NO DELETE
permissions to your regular users. This protects your back end
folder from having items deleted by nosy employees. Access will try
to delete the LDB when they exit, but it will fail silently with no
downside (except for the time the retries take, which is quite
minimal). I've run several clients in this configuration for years
with no problems whatsoever. If you're concerned about deletion of
your back end and don't want to apply DELETE DENY permissions on the
back end itself (and then re-apply them every time you compact it)
then this can work.

 

[Tom Wickerath]

Hi David,

Nice article.

Thank You.

May I suggest that you might want to alter two sections:

The first link that I show in the Persistent Connections section points to
Tony Toews web site. Tony includes this method, in the section subtitled
"Global Recordset which is always open". Luke Chung's tip also includes the
method that you mentioned.

In any case, I have a slight problem these days....I have been unable, since
July 12, to contact the owner of QBuilt.com. Thus, I have no way of getting
any changes made to this article. I am expecting the QBuilt site to just go
down any day now, and when or if that happens I will have a bigger incentive
to get going on a site hosted on MVPS/Access.

if you have *some* users in an admin group...

On your second point, I personally avoid ULS (User Level Security) like the
plague.
You can also remove Delete privileges for the .mdb file, using Windows
security.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

The first link that I show in the Persistent Connections section
points to Tony Toews web site. Tony includes this method, in the
section subtitled "Global Recordset which is always open". Luke
Chung's tip also includes the method that you mentioned.

No, Tony does *not* include my method. I say you don't have to open
the recordset at all -- just initialize the database pointer to the
back end.

In any case, I have a slight problem these days....I have been
unable, since July 12, to contact the owner of QBuilt.com. Thus, I
have no way of getting any changes made to this article. I am
expecting the QBuilt site to just go down any day now, and when or
if that happens I will have a bigger incentive to get going on a
site hosted on MVPS/Access.

That's terrible. I hope you have all your source files.

On your second point, I personally avoid ULS (User Level Security)
like the plague.

This doesn't have anything to do with Jet user-level security. I
meant "admins" in terms of those with permissions to delete in the
share on the server.

You can also remove Delete privileges for the .mdb file, using
Windows security.

But, as I said, that won't survive a compact if you have the folder
settings to ALLOW DELETE, unless you change it manually or do your
compact through a method that compacts to a different location and
then copies over top of the existing file using an account that
*has* DELETE permission on the file.

 

[Tony Toews]

No, Tony does *not* include my method. I say you don't have to open
the recordset at all -- just initialize the database pointer to the
back end.

I've updated the page at
http://www.granite.ab.ca/access/performanceldblocking.htm to include
your method.

However I prefer the recordset connection or bound form method as the
database method requires some means of determining the path and
location of the back end MDB. Which is more code than a table.

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

 

[Tom Wickerath]

Hi David,

I missed your reply on Nov. 6. I just noticed it after Tony replied, and I
received a notification message.

That's terrible. I hope you have all your source files.

Yes, I have made backup copies of every page.

This doesn't have anything to do with Jet user-level security.

I misunderstood. I just went back and looked at what you wrote.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[David W. Fenton]

I've updated the page at
http://www.granite.ab.ca/access/performanceldblocking.htm to
include your method.

However I prefer the recordset connection or bound form method as
the database method requires some means of determining the path
and location of the back end MDB. Which is more code than a
table.

Not for me, since I have code that returns the back end for me.

It's certainly less work than creating and managing a hidden form!

 

[Tony Toews]

Not for me, since I have code that returns the back end for me.

As do I but for users who aren't that familiar with VBA this is a bit
more troublesome.

It's certainly less work than creating and managing a hidden form!

Again for those of us who are comfortable in VBA then I would agree
with you. Although I use hidden forms for other things.

Oh yes, and don't variables get lost when you hit stop in the debug
window? That's why I've never liked using global variables.

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

 

[David W. Fenton]

As do I but for users who aren't that familiar with VBA this is a
bit more troublesome.


Again for those of us who are comfortable in VBA then I would
agree with you. Although I use hidden forms for other things.

Oh yes, and don't variables get lost when you hit stop in the
debug window? That's why I've never liked using global variables.

Huh? What does global variables have to do with it? Who said it was
a global?