Local Copy: Will It Solve Read Only Problem?

[Robert T]

Hello:

I work for a govt. agency and developed a database that is split into an
application.mdb and a table.mdb. Obviously all of the tables are stored in
table.mdb. Both of them are on the same Network drive and each member of our
team has Access installed on their computer. However, if someone else opens
the database from the Network drive, everyone else gets READ ONLY access. I
thought splitting the database would resolve this problem, but it didn’t.
I’ve also followed everyone else’s advice to avoid this but it still happens.

Every Access book I read also said I should also install the front end
[application.mdb] on each local machine, however, no one explains how to do
such. Is that simply copying the application.mdb from the network drive to
each team member’s local hard drive [as opposed to a network drive]? Will
this resolve our READ ONLY problem when someone opens the application on
their local hard drive as opposed to the network?

And while I’m at it, let me ask a related question on splitting the
database. If I Compact and Repair the database while working in
Application.mdb, will that also compact all of the tables located in
Table.mdb? Or do I also have to run Compact and Repair from the database
where the tables are stored?

Robert

 

[Jerry Whittle]

All users of the database must have at least read, write, and modify
privileges to the folder holding the database file. Not just the database
..mdb file, but the entire folder. This is probably the problem. Also open the
BE database and go to Tools, Options, and Advanced Tab. Set the Default Open
mode to Shared and the Default Record Locking to Edited Record. (While you
are in the Options area, go over to the General tab and turn off Compact on
Close and Name AutoCorrect. Trust me on this.)

Copying the FE file to the user's computer probably won't fix the read only
problem. That's a network security issue. However putting the FE on the user
computers can really speed up the database as all the forms and reports are
on the hard drive and do not need to be pulled across the network.

You must run compact and repair in the BE if you want it compacted and
repaired. Don't go nuts over C&R. Once a month should be more than enough. A
few dozen MB's of bload is NOT a problem.

 

[Robert T]

Hi Jerry:

Thanks for the prompt response. I saw your suggestions on the Acess
setttings in another thread and previously changed them to comply with your
recommendation. That did not solve the problem.

In terms of read, write, and modify to a specific folder, the entire team
has the same exact rights so I don't think that's the issue.

Robert

All users of the database must have at least read, write, and modify
privileges to the folder holding the database file. Not just the database
.mdb file, but the entire folder. This is probably the problem. Also open the
BE database and go to Tools, Options, and Advanced Tab. Set the Default Open
mode to Shared and the Default Record Locking to Edited Record. (While you
are in the Options area, go over to the General tab and turn off Compact on
Close and Name AutoCorrect. Trust me on this.)

Copying the FE file to the user's computer probably won't fix the read only
problem. That's a network security issue. However putting the FE on the user
computers can really speed up the database as all the forms and reports are
on the hard drive and do not need to be pulled across the network.

You must run compact and repair in the BE if you want it compacted and
repaired. Don't go nuts over C&R. Once a month should be more than enough. A
few dozen MB's of bload is NOT a problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello:

I work for a govt. agency and developed a database that is split into an
application.mdb and a table.mdb. Obviously all of the tables are stored in
table.mdb. Both of them are on the same Network drive and each member of our
team has Access installed on their computer. However, if someone else opens
the database from the Network drive, everyone else gets READ ONLY access. I
thought splitting the database would resolve this problem, but it didn’t.
I’ve also followed everyone else’s advice to avoid this but it still happens.

Every Access book I read also said I should also install the front end
[application.mdb] on each local machine, however, no one explains how to do
such. Is that simply copying the application.mdb from the network drive to
each team member’s local hard drive [as opposed to a network drive]? Will
this resolve our READ ONLY problem when someone opens the application on
their local hard drive as opposed to the network?

And while I’m at it, let me ask a related question on splitting the
database. If I Compact and Repair the database while working in
Application.mdb, will that also compact all of the tables located in
Table.mdb? Or do I also have to run Compact and Repair from the database
where the tables are stored?

Robert

 

[Pat Hartman]

If your application modifies objects or allows users to modify objects, that
will also cause the database to be read-only. You should NEVER work on the
shared copy of the application. ALWAYS work in a separate copy preferably
located on your own hard drive. Overlay the shared copy when your changes
have been tested.

Hi Jerry:

Thanks for the prompt response. I saw your suggestions on the Acess
setttings in another thread and previously changed them to comply with
your
recommendation. That did not solve the problem.

In terms of read, write, and modify to a specific folder, the entire team
has the same exact rights so I don't think that's the issue.

Robert

All users of the database must have at least read, write, and modify
privileges to the folder holding the database file. Not just the database
.mdb file, but the entire folder. This is probably the problem. Also open
the
BE database and go to Tools, Options, and Advanced Tab. Set the Default
Open
mode to Shared and the Default Record Locking to Edited Record. (While
you
are in the Options area, go over to the General tab and turn off Compact
on
Close and Name AutoCorrect. Trust me on this.)

Copying the FE file to the user's computer probably won't fix the read
only
problem. That's a network security issue. However putting the FE on the
user
computers can really speed up the database as all the forms and reports
are
on the hard drive and do not need to be pulled across the network.

You must run compact and repair in the BE if you want it compacted and
repaired. Don't go nuts over C&R. Once a month should be more than
enough. A
few dozen MB's of bload is NOT a problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello:

I work for a govt. agency and developed a database that is split into
an
application.mdb and a table.mdb. Obviously all of the tables are stored
in
table.mdb. Both of them are on the same Network drive and each member
of our
team has Access installed on their computer. However, if someone else
opens
the database from the Network drive, everyone else gets READ ONLY
access. I
thought splitting the database would resolve this problem, but it didn't.
I've also followed everyone else's advice to avoid this but it still
happens.

Every Access book I read also said I should also install the front end
[application.mdb] on each local machine, however, no one explains how
to do
such. Is that simply copying the application.mdb from the network drive
to
each team member's local hard drive [as opposed to a network drive]?
Will
this resolve our READ ONLY problem when someone opens the application
on
their local hard drive as opposed to the network?

And while I'm at it, let me ask a related question on splitting the
database. If I Compact and Repair the database while working in
Application.mdb, will that also compact all of the tables located in
Table.mdb? Or do I also have to run Compact and Repair from the
database
where the tables are stored?

Robert

 

[Tony Toews [MVP]]

However putting the FE on the user
computers can really speed up the database as all the forms and reports are
on the hard drive and do not need to be pulled across the network.

Well, I'm not so sure about that. A client insisted on running
individual copies of the FE stored on the file server on a Terminal
Server/Citrix system. I can't say that I noticed it was very much
slower.

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/

 

[Tony Toews [MVP]]

Every Access book I read also said I should also install the front end
[application.mdb] on each local machine, however, no one explains how to do
such. Is that simply copying the application.mdb from the network drive to
each team member’s local hard drive [as opposed to a network drive]?

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Robert T]

Pat:

I don't know if there are too many applications that don't modify objects.

I'm not sure what you mean by working on a separate copy. I thought Access
was a multi-user application. And I don't know what you mean by "Overlay the
Shared Copy"

Tony:

Where and how did Citrix get into this conversation? I'm still confused.

Robert

:

 

[Pat Hartman]

Was the database on the same server as the terminal session? If not, you
still would have network latency.

 

[Pat Hartman]

In fact it is just the opposite. Very few multi-user applications allow
users to create or modify objects. And, those that allow it, are always
distributed locally so as to not interfere with concurrent access by other
users. Users in a multi-user application should only be changing data, they
should never be modifying forms/reports/queries/etc. The other issue with
allowing users to modify or add objects is the difficulty this causes when
you need to release a new version of the front end. What happens to the
local changes made by the user? When I have a requirement for a user to
create his own reports and queries, I create a separate database that is
linked to the shared back end. That way he is in complete control over what
he does and he does not interfere with the distribution of features to the
general community.

In some situations, on some networks, you may be able to get by with having
the FE and the BE both on the server. However, you run the risk of
corruption due to Access' sensitivity to network blips and freezes on an
individual PC. The safest method is to distribute a copy of the FE to each
user to be used from his C: drive.

When I advised you to work on a separate copy of the FE, that is just
exactly what I meant. Copy the FE and place it on your C: drive. You can
leave it linked to the production BE on the server if you are careful or you
can also copy the BE and have your local, working copy also link to a test
copy of the data. Then you would make changes to your own version and test
them. When the change is complete you can distribute the updated version to
your users. Tony is suggesting a tool he created to manage the distributed
copies. When you have a number of users, you don't want to get into the
business of having to visit each PC to load an update if you change a form
or report. Tony's tool solves that problem.

Citrix got into the mix because Citrix is a tool that allows you to work
with shared databases in a remote environment. For example, my current
client's main office is in Farmington, CT but we also have offices in a half
dozen cities across the country. Running client/server applications such as
Access or WinForms over a WAN provides dismal performance regardless of how
fast your network connection. NO internet connection comes even close to
the speed of a leased line or LAN. The solution to this is to use a
product like Citrix. Citrix runs on a server at the headquarters. Each
person logs into his own session which is actually running on the server,
which in our case is in Farmington right next to the database server so the
data never leaves the computer room. What you see on your remote connection
is simple a screen image of your remote session. So with Citrix, you can
achieve responsiveness very close to a local connection. We even have some
Citrix users in London at our parent company office and for them it is as if
they are sitting at a desk in the room with the server.

 

[Tony Toews [MVP]]

Was the database on the same server as the terminal session?

No, the file server and TS/Citrix were separate boxes.

If not, you still would have network latency.

I'm sure there was but it wasn't significant.

That said they may very well have had a Gigabit LAN in the server room
and a 100 mpbs LAN elsewhere in the buildings. So things might've
been a bit more sluggish out at the workstations.

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/

 

[Tony Toews [MVP]]

Where and how did Citrix get into this conversation? I'm still confused.

That particular client was using Citrix. Citrix runs on top of
Terminal Server and adds some functionality to Terminal Server.
However bringing up Citrix and Terminal Server in this thread is
confusing.

My point was that giving each user their own copy of the FE on a file
server, rather than downloading it to the client workstation, might
not be as sluggish as everyone thinks it would be. As always
everyone's mileage might vary significantly.

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/

 

[Robert T]

Hello Pat:

Yesterday I was stressed out and doing several things simultaneously,
someting I don't do well. When I QUICKLY read your response, I thought you
were referring to data as opposed to database object s. I didn't understand
why someone would design a DB where you couldn't modify the data. Obviously I
misread your comments.

I truly appreciate your comments about placing a copy of the FE on
everyone's local hard drive. People write about that in books but never go
into any details. They just say something such as, place a copy of the FE on
everyone's local hard drive, but they don't give any specifics. In addition,
they usually don't explain the logic behind the move.

Do you think this will eliminate the problem of one user logging into the DB
and then the next user unable to do anything other than READ data?

Note: We do have Citrix which is used by employees who are traveling and
obviously out of the office.

Thanks so much for your input,

Robert

 

[Tony Toews [MVP]]

I truly appreciate your comments about placing a copy of the FE on
everyone's local hard drive. People write about that in books but never go
into any details. They just say something such as, place a copy of the FE on
everyone's local hard drive, but they don't give any specifics. In addition,
they usually don't explain the logic behind the move.

See the following for specifics. <smile>

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

Do you think this will eliminate the problem of one user logging into the DB
and then the next user unable to do anything other than READ data?

No, because the users need to have read, write, create and delete
privileges to the folder on which the backend MDB resides.

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/