Moving backend to secure folder

[Trish]

I have an Access 2003 database application consisting of a frontend on
each user's desktop and a backend on a network drive. I have been
asked to move the backend into a folder that the users can't actually
see from Windows Explorer - a folder for which only a few of us have
privileges. The fear is that someone will accidentally delete or
damage the file. Is this possible or do we need to simply give
everyone read/write privileges, but not delete capability? Many
thanks for your help.

Trish

[Granny Spitz via AccessMonster.com]

Trish wrote:

I have been
asked to move the backend into a folder that the users can't actually
see from Windows Explorer - a folder for which only a few of us have
privileges.

Only the users who have permissions on that folder will be able to open the
database.

Is this possible or do we need to simply give
everyone read/write privileges, but not delete capability?

If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and some of the
users will be blocked from opening the database.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[Trish]

Granny Spitz via AccessMonster.com wrote:

Trish wrote:

I have been
asked to move the backend into a folder that the users can't actually
see from Windows Explorer - a folder for which only a few of us have
privileges.

Only the users who have permissions on that folder will be able to open the
database.

Is this possible or do we need to simply give
everyone read/write privileges, but not delete capability?

If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and some of the
users will be blocked from opening the database.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

How do you keep people from deleting the file altogether?

[Trish]

Granny Spitz via AccessMonster.com wrote:

Trish wrote:

I have been
asked to move the backend into a folder that the users can't actually
see from Windows Explorer - a folder for which only a few of us have
privileges.

Only the users who have permissions on that folder will be able to open the
database.

Is this possible or do we need to simply give
everyone read/write privileges, but not delete capability?

If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and some of the
users will be blocked from opening the database.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

How do you keep people from deleting the file altogether?

[Granny Spitz via AccessMonster.com]

Trish wrote:

How do you keep people from deleting the file altogether?

Take away the box of Krispy Kremes and say "No more for you," and restore the
database from last night's backup. (Or you can tell them they'll be fired if
they delete the database, but that's not as effective a punishment.)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[David W. Fenton]

"Granny Spitz via AccessMonster.com" <u26473@uwewrote in
news:689a769bd70e4@uwe:

If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and
some of the users will be blocked from opening the database.

This is simply not true.

The users need READ/CHANGE permission. They don't need DELETE. The
result of that is that the LDB file will not be deleted when users
without DELETE permission exit the database, but as long as some of
the administrative users have that permission, it's OK. I have had
many clients who run with users having no DELETE permissions on the
folder where their back end data file is stored.

Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for setting
permissions, so perhaps one could set up an Access compact that
would restore the DELETE deny setting to the compacted file.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"Granny Spitz via AccessMonster.com" <u26473@uwewrote in message
news:689aaf6c20e96@uwe...

Trish wrote:

>How do you keep people from deleting the file altogether?

Take away the box of Krispy Kremes and say "No more for you," and restore
the
database from last night's backup. (Or you can tell them they'll be fired
if
they delete the database, but that's not as effective a punishment.)

The "official" way to state that is: "Not deleting the database is a
condition of continued employment." But, if you get an employee who's
really ticked off, that's not a deterrent.

:-)

Larry

[Trish]

LOL

I think I will quote you verbatim if my manager asks what I found out.

Thanks so much for your help.

Trish

[Granny Spitz via AccessMonster.com]

David W. Fenton wrote:

>If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and
some of the users will be blocked from opening the database.

This is simply not true.

The users need READ/CHANGE permission. They don't need DELETE. The
result of that is that the LDB file will not be deleted when users
without DELETE permission exit the database, but as long as some of
the administrative users have that permission, it's OK. I have had
many clients who run with users having no DELETE permissions on the
folder where their back end data file is stored.

And the sky parted the tall cumulonimbus clouds, and a thunderous voice said,

"Thou hast proof my son David hast lifted thy plonk, ... so don't screw it up
again!"

[Granny Spitz via AccessMonster.com]

David W. Fenton wrote:

>If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and
some of the users will be blocked from opening the database.

This is simply not true.

The users need READ/CHANGE permission. They don't need DELETE. The
result of that is that the LDB file will not be deleted when users
without DELETE permission exit the database, but as long as some of
the administrative users have that permission, it's OK. I have had
many clients who run with users having no DELETE permissions on the
folder where their back end data file is stored.

And the sky parted the tall cumulonimbus clouds, and a thunderous voice said,

"Thou hast proof my son David hast lifted thy plonk, ... so don't screw it up
again!"

>:
.
:

... so I was sitting there eating my jelly filled Krispy Kreme, two of my
coworkers were chatting on the phone to their boyfriends, and three of the
young men were playing Nerf basketball in the cube and roared "SCORE!" The
boss walked in and looked around with a confused look on his face. "Why
aren't you people working?" He turned to me and asked "Why aren't you
telling these people to get back to work?" I answered "My mouf iff toof
fuwell," and one of my coworkers answered "We can't work. We're locked out
of the database because no one here today has admin privs to delete the LDB
file when the database crashed."

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[David W. Fenton]

"Trish" <ph*******@mdanderson.orgwrote in
news:11*********************@b28g2000cwb.googlegro ups.com:

How do you keep people from deleting the file altogether?

Two things:

1. Obscurity: make the sharename hidden from Windows Explorer by
naming it with a $ on the end. That way, you have to know the exact
share name to browse to it.

2. Set DELETE DENY permissions on the file itself. This will have to
be reset each time the file is compacted.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Granny Spitz via AccessMonster.com]

Trish wrote:

I think I will quote you verbatim if my manager asks what I found out.

Thanks so much for your help.

You're welcome, Trish. David gave the right answer though. Put the database
in a hidden share on the server. If you're really paranoid (and many people
have good reason!) have the windows admins remove users' ability to map
network drives on their computers and navigate network neighborhood. That
makes it very difficult for most people to ever find the file if it's not in
a window they can navigate to with their mouse.

--
Message posted via http://www.accessmonster.com

[Trish]

Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for setting
permissions, so perhaps one could set up an Access compact that
would restore the DELETE deny setting to the compacted file.

This raises another question -- How often should one compact the back
end file? I'm guessing that it depends on the level of activity. How
do you quantify that?

[David W. Fenton]

"Trish" <ph*******@mdanderson.orgwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

[quoting me:]

>Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those
permissions, since it's actually a new file. I'm sure there's an
API for setting permissions, so perhaps one could set up an
Access compact that would restore the DELETE deny setting to the
compacted file.

This raises another question -- How often should one compact the
back end file? I'm guessing that it depends on the level of
activity. How do you quantify that?

It depends on whether or not the back end is bloating a lot, and
whether you're seeing performance degradation. As a file is used,
the data and index pages get scattered all over the place, and when
you compact, the indexes and data pages get written in order. This
improves performance because the index and data pages are contiguous
(just like defragging your hard drive, though it's somewhat
different in other respects).

I have one client who uses the FMS agent to compact. We originally
had it compacting once a week, but they started having corruption
problems and so I changed it to compacting every night. It makes a
backup before the compact and retains a set number of backups,
according to my choice. It's expensive, but it's pretty reliable.
But you do have to have code in your applications to shut down any
instance of the app that could have the back end opened and prevent
the compact.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

>Another approach is to deny DELETE on the back end file itself.

>However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for setting
permissions, so perhaps one could set up an Access compact that would
restore the DELETE deny setting to the compacted file.

I achieved that result by compacting the database to a temp folder and
then copying the compacted copy back over the original. Because the
original already exists, its ACL information is preserved. Essentially,

Set objAccess = CreateObject("Access.Application.11")
objAccess.DbEngine.CompactDatabase "I:\data\foo.mdb, "C:\tmp\temp.mdb"
fso.CopyFile "C:\tmp\temp.mdb", "I:\data\foo.mdb
Gord
David W. Fenton wrote:

"Granny Spitz via AccessMonster.com" <u26473@uwewrote in
news:689a769bd70e4@uwe:

If it's a multiuser database, every database user should have
read/write/create/delete permissions on the folder. Any less and
some of the users will be blocked from opening the database.

This is simply not true.

The users need READ/CHANGE permission. They don't need DELETE. The
result of that is that the LDB file will not be deleted when users
without DELETE permission exit the database, but as long as some of
the administrative users have that permission, it's OK. I have had
many clients who run with users having no DELETE permissions on the
folder where their back end data file is stored.

Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for setting
permissions, so perhaps one could set up an Access compact that
would restore the DELETE deny setting to the compacted file.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11**********************@k70g2000cwa.googlegr oups.com:

[quoting me:]

>>Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for
setting permissions, so perhaps one could set up an Access compact
that would restore the DELETE deny setting to the compacted file.

I achieved that result by compacting the database to a temp folder
and then copying the compacted copy back over the original.
Because the original already exists, its ACL information is
preserved. Essentially,

I didn't know that. I thought it would inherit the folder
permissions.

Set objAccess = CreateObject("Access.Application.11")
objAccess.DbEngine.CompactDatabase "I:\data\foo.mdb,
"C:\tmp\temp.mdb" fso.CopyFile "C:\tmp\temp.mdb", "I:\data\foo.mdb

Why use the file system object for an operation that Access provides
a command for?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

Why use the file system object for an operation that Access provides

a command for?

Just lazy. I copied the lines from an old VBScript.
David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11**********************@k70g2000cwa.googlegr oups.com:

[quoting me:]

>Another approach is to deny DELETE on the back end file itself.
However, after a compact, you'd have to restore those permissions,
since it's actually a new file. I'm sure there's an API for
setting permissions, so perhaps one could set up an Access compact
that would restore the DELETE deny setting to the compacted file.

I achieved that result by compacting the database to a temp folder
and then copying the compacted copy back over the original.
Because the original already exists, its ACL information is
preserved. Essentially,

I didn't know that. I thought it would inherit the folder
permissions.

Set objAccess = CreateObject("Access.Application.11")
objAccess.DbEngine.CompactDatabase "I:\data\foo.mdb,
"C:\tmp\temp.mdb" fso.CopyFile "C:\tmp\temp.mdb", "I:\data\foo.mdb

Why use the file system object for an operation that Access provides
a command for?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

[I wrote:]

>Why use the file system object for an operation that Access
provides a command for?

Just lazy. I copied the lines from an old VBScript.

But that meant you had to add a reference to the FSO to your
database.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

But that meant you had to add a reference to the FSO to your

database.

The lines were copied from a stand-alone VBScript for the Usenet post.
It wasn't used in VBA module code.
Gord
David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

[I wrote:]

Why use the file system object for an operation that Access
provides a command for?

Just lazy. I copied the lines from an old VBScript.

But that meant you had to add a reference to the FSO to your
database.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Trish]

Thanks so much, everyone, for your help.

Trish

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

[I wrote:]

>But that meant you had to add a reference to the FSO to your
database.

The lines were copied from a stand-alone VBScript for the Usenet
post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

[I wrote:]

But that meant you had to add a reference to the FSO to your
database.

The lines were copied from a stand-alone VBScript for the Usenet
post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

I wasn't asking a question at all. I following-up to *your* post on how
one might get Access to compact a back-end database while preserving
the permissions on the .mdb file. VBA code does not necessarily have to
be involved.

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegro ups.com:

David W. Fenton wrote:

>"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googleg roups.com:

[I wrote:]

>But that meant you had to add a reference to the FSO to your
database.

The lines were copied from a stand-alone VBScript for the
Usenet post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

I wasn't asking a question at all. I following-up to *your* post
on how one might get Access to compact a back-end database while
preserving the permissions on the .mdb file. VBA code does not
necessarily have to be involved.

In an Access newsgroup?

Why didn't you mention that your destination (as opposed to source)
was not Access when I pointed out that Access offered its own
commands for copying files?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegro ups.com:

David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

[I wrote:]
But that meant you had to add a reference to the FSO to your
database.

The lines were copied from a stand-alone VBScript for the
Usenet post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

I wasn't asking a question at all. I following-up to *your* post
on how one might get Access to compact a back-end database while
preserving the permissions on the .mdb file. VBA code does not
necessarily have to be involved.

In an Access newsgroup?

Why not? It was an Access database that was being compacted.

Why didn't you mention that your destination (as opposed to source)
was not Access when I pointed out that Access offered its own
commands for copying files?

I thought my original response (that the lines of code were copied from
a VBScript) was sufficient. Apparently it wasn't, and for that I
apologize.
Gord

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11**********************@f16g2000cwb.googlegr oups.com:

David W. Fenton wrote:

>"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegr oups.com:

David W. Fenton wrote:
"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googleg roups.com:

[I wrote:]
But that meant you had to add a reference to the FSO to
your database.

The lines were copied from a stand-alone VBScript for the
Usenet post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

I wasn't asking a question at all. I following-up to *your*
post on how one might get Access to compact a back-end database
while preserving the permissions on the .mdb file. VBA code
does not necessarily have to be involved.

In an Access newsgroup?

Why not? It was an Access database that was being compacted.

Well, if you're not using Access VBA, then you're not using any of
the Access features, so you're really talking about a Jet database,
as the Access-specific features are not available in any other
circumstance (except via automation).

I think my assumption that an Access command would make more sense
was valid. And I think it odd that you would first respond to that
suggestion by pointing out that you'd copied the code from a
non-Access context. Why not just say you working in a completely
non-Access context? Then I could have saved my breath about the
reference.

>Why didn't you mention that your destination (as opposed to
source) was not Access when I pointed out that Access offered its
own commands for copying files?

I thought my original response (that the lines of code were copied
from a VBScript) was sufficient. Apparently it wasn't, and for
that I apologize.

Well, you see the reason, I hope -- I copy VB and VBScript code into
my Access apps all the time. I convert them to use Access-specific
conventions, and I'd assume that were you to do that, you'd do the
same thing.

To me, something just doesn't add up.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Gord]

David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11**********************@f16g2000cwb.googlegr oups.com:

David W. Fenton wrote:

"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegro ups.com:

David W. Fenton wrote:
"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

[I wrote:]
But that meant you had to add a reference to the FSO to
your database.

The lines were copied from a stand-alone VBScript for the
Usenet post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question about
programming in Access.

I wasn't asking a question at all. I following-up to *your*
post on how one might get Access to compact a back-end database
while preserving the permissions on the .mdb file. VBA code
does not necessarily have to be involved.

In an Access newsgroup?

Why not? It was an Access database that was being compacted.

Well, if you're not using Access VBA, then you're not using any of
the Access features, so you're really talking about a Jet database,
as the Access-specific features are not available in any other
circumstance (except via automation).

....but that's exactly what I was doing. The first and second lines used
COM automation to have Access compact an MDB file to a temporary
location. The third line simply copied the compacted file back over the
original. That's all I was trying to show.

I think my assumption that an Access command would make more sense
was valid. And I think it odd that you would first respond to that
suggestion by pointing out that you'd copied the code from a
non-Access context. Why not just say you working in a completely
non-Access context?

VBA may not have been involved, but Access certainly was.

Then I could have saved my breath about the
reference.

It's probably a good thing that you mentioned it in case somebody
following the thread was led to believe that using the FileSystemObject
was the only way to copy a file using VBA.
Gord

[David W. Fenton]

"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegro ups.com:

>
David W. Fenton wrote:

>"Gord" <gd*@kingston.netwrote in
news:11**********************@f16g2000cwb.googleg roups.com:

David W. Fenton wrote:
"Gord" <gd*@kingston.netwrote in
news:11*********************@f16g2000cwb.googlegr oups.com:

David W. Fenton wrote:
"Gord" <gd*@kingston.netwrote in
news:11**********************@h48g2000cwc.googleg roups.com:

[I wrote:]
But that meant you had to add a reference to the FSO to
your database.

The lines were copied from a stand-alone VBScript for the
Usenet post. It wasn't used in VBA module code.

Well, pardon me for assuming you were asking a question
about programming in Access.

I wasn't asking a question at all. I following-up to *your*
post on how one might get Access to compact a back-end
database while preserving the permissions on the .mdb file.
VBA code does not necessarily have to be involved.

In an Access newsgroup?

Why not? It was an Access database that was being compacted.

Well, if you're not using Access VBA, then you're not using any
of the Access features, so you're really talking about a Jet
database, as the Access-specific features are not available in
any other circumstance (except via automation).

...but that's exactly what I was doing. The first and second lines
used COM automation to have Access compact an MDB file to a
temporary location. The third line simply copied the compacted
file back over the original. That's all I was trying to show.

Well, I vaguely remember thinking you were automatic Access from
Access. That makes little sense for a compact, but using automation
makes no sense, either, as you don't need to automate Access to
compact an MDB -- all you need is to use DAO directly.

So, I was working under a mistaken impression.

>I think my assumption that an Access command would make more
sense was valid. And I think it odd that you would first respond
to that suggestion by pointing out that you'd copied the code
from a non-Access context. Why not just say you working in a
completely non-Access context?

VBA may not have been involved, but Access certainly was.

But there was no reason it needed to be.

>Then I could have saved my breath about the
reference.

It's probably a good thing that you mentioned it in case somebody
following the thread was led to believe that using the
FileSystemObject was the only way to copy a file using VBA.

In the end, I don't think your question had anything at all to do
with Access. It could have been done in VBScript using DAO, which
means you're using nothing but Jet.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/