How to lock tables with vba code?

deko

Now that users are handy at importing tables from an Excel spreadsheet, I've
discovered a very dangerous problem.

If the user decides to name the imported table the same name as an existing
table in the database, the Spreadsheet Import Wizard will happily overwrite
the table - and bye-bye database.

Is there a way to prevent this with code? Must I now read the Access
Security FAQ?

Nov 13 '05 #1

Scott McDaniel

Access ULS is the most secure method you could use. ULS will allow you full
control over what your users can and cannot do, and you can control exactly
what user can import into your application.

You could try removing menus and toolbars (click Tools - Startup to review
the possibilities), thereby making it more difficult to do this. If your
users depend on the builtin menus, however, you'll have to build your own
toolbars for them to use.This is pretty easy for semi-experienced (or
determined) users to get around, of course - if you want to be sure, then
use ULS.

--
Scott McDaniel
InfoTrakker Software
"deko" <de**@nospam.com> wrote in message
news:O-********************@comcast.com...

Now that users are handy at importing tables from an Excel spreadsheet,
I've discovered a very dangerous problem.

If the user decides to name the imported table the same name as an
existing table in the database, the Spreadsheet Import Wizard will happily
overwrite the table - and bye-bye database.

Is there a way to prevent this with code? Must I now read the Access
Security FAQ?

Nov 13 '05 #2

deko

> Access ULS is the most secure method you could use. ULS will allow you

full control over what your users can and cannot do, and you can control
exactly what user can import into your application.

You could try removing menus and toolbars (click Tools - Startup to review
the possibilities), thereby making it more difficult to do this. If your
users depend on the builtin menus, however, you'll have to build your own
toolbars for them to use.This is pretty easy for semi-experienced (or
determined) users to get around, of course - if you want to be sure, then
use ULS.

Looking at the Security FAQ, I see these DAO security constants:

Can create new tables / queries
dbSecCreate
Can read table definitions
dbSecReadDef
Can modify or delete table definitions
dbSecWriteDef
Can read records
dbSecRetrieveData
Can add records
dbSecInsertData
Can edit records
dbSecReplaceData
Can delete records
dbSecDeleteData

I want users to be able to modify and delete table definitions, and
everything else on the list - I just don't them overwriting existing tables.
I'm mostly worried about mistakes rather than malice, so It would be nice to
somehow (temporarily) put a lock on all internal tables with code to prevent
the overwrite. Is this possible?

When importing from another Access database, as you know, a table name is
indexed with a number if it has the same name as an existing table - not so
with the spreadsheet import wizard. I believe this is a serious oversight
on the part of the MS Access team.

Nov 13 '05 #3

Scott McDaniel

Your best bet is to implement ULS and use RWOP queries for all data access
(the FAQ has info on RWOP queries), then remove all pemissions to tables ...
with RWOP queries, users don't need to interact with the table, only the
query. Of course, you probably wouldn't be able to use the builtin methods
to import your files, and would instead have to build your own utiltiy to
control the process. If you control the process, you can easily determine if
a desired table name is already present and take appropriate action. Even if
you implement security, if you still allow users to import items via the
builtin Access methods, you'll stand the chance of a user overwriting.

I don't really agree that this is a serious oversight ... seems perfectly
reasonable to me to overwrite a table, if the user requests it ... after
all, the software should respond to the User's request. I would agree that
the Import wizard should ask them if they want to overwrite, but I'd also
argue that a properly designed program should NEVER allow users to directly
import data to the live tables without first providing some form of backup
.... my thinking is that the application should import to temporary tables
and then allow users to append, at best, information to existing tables, but
only after making sure the original data is safe ... just my .02 worth ...
--
Scott McDaniel
InfoTrakker Software

"deko" <de**@nospam.com> wrote in message
news:eN********************@comcast.com...

Access ULS is the most secure method you could use. ULS will allow you
full control over what your users can and cannot do, and you can control
exactly what user can import into your application.

You could try removing menus and toolbars (click Tools - Startup to
review the possibilities), thereby making it more difficult to do this.
If your users depend on the builtin menus, however, you'll have to build
your own toolbars for them to use.This is pretty easy for
semi-experienced (or determined) users to get around, of course - if you
want to be sure, then use ULS.

Looking at the Security FAQ, I see these DAO security constants:

Can create new tables / queries
dbSecCreate
Can read table definitions
dbSecReadDef
Can modify or delete table definitions
dbSecWriteDef
Can read records
dbSecRetrieveData
Can add records
dbSecInsertData
Can edit records
dbSecReplaceData
Can delete records
dbSecDeleteData

I want users to be able to modify and delete table definitions, and
everything else on the list - I just don't them overwriting existing
tables. I'm mostly worried about mistakes rather than malice, so It would
be nice to somehow (temporarily) put a lock on all internal tables with
code to prevent the overwrite. Is this possible?

When importing from another Access database, as you know, a table name is
indexed with a number if it has the same name as an existing table - not
so with the spreadsheet import wizard. I believe this is a serious
oversight on the part of the MS Access team.

Nov 13 '05 #4

deko

> Your best bet is to implement ULS and use RWOP queries for all data access

(the FAQ has info on RWOP queries), then remove all pemissions to tables
... with RWOP queries, users don't need to interact with the table, only
the query. Of course, you probably wouldn't be able to use the builtin
methods to import your files, and would instead have to build your own
utiltiy to control the process. If you control the process, you can easily
determine if a desired table name is already present and take appropriate
action. Even if you implement security, if you still allow users to import
items via the builtin Access methods, you'll stand the chance of a user
overwriting.

I don't really agree that this is a serious oversight ... seems perfectly
reasonable to me to overwrite a table, if the user requests it ... after
all, the software should respond to the User's request. I would agree that
the Import wizard should ask them if they want to overwrite, but I'd also
argue that a properly designed program should NEVER allow users to
directly import data to the live tables without first providing some form
of backup ... my thinking is that the application should import to
temporary tables and then allow users to append, at best, information to
existing tables, but only after making sure the original data is safe ...
just my .02 worth ...

The app does not need security so I'm reluctant to start dealing with ULS,
and the built-in utilities are definitely needed for importing data.
Personally, I think the import wizards are fantastic. The users of this app
need to import form both text and Excel files and the import wizard makes it
a snap. The barf bag is that they can destroy the database if they
overwrite an internal table. I should not have to implement ULS to prevent
this. If users want to overwrite a table, they can rename the table in the
database window. Letting them overwrite a table from an import wizard is
just bad design as far as I'm concerned.

In any case, I found a work around. If I open a recordset on a table before
the import wizard is opened, that table cannot be overwritten. I tested
this with a couple of tables and it worked great. What I'll need to do is
loop through all internal tables and open a recordset selecting one record
from each table, and then close them all when the wizard closes. It may
take some experimentation to get things just right, but I'm confident this
will serve my purposes.

Nov 13 '05 #5

Salad

deko wrote:

Now that users are handy at importing tables from an Excel spreadsheet, I've
discovered a very dangerous problem.

If the user decides to name the imported table the same name as an existing
table in the database, the Spreadsheet Import Wizard will happily overwrite
the table - and bye-bye database.

Is there a way to prevent this with code? Must I now read the Access
Security FAQ?

Why don't you create a form that has a FileOpen dialog that selects the
spreadsheet name. You can then set your own rules in the form for
importing. You can even import the data on your own conditions.

Basically you are letting the user have access to your data where there
are no rules. They could open up any table and input/modify data
without following the rules of the app.

You do know that you don't need forms or code to update tables. You can
open up tables and enter data however you want whther correct or not.
That is why we have jobs as developers...to create applications that
follow rules defined by the project.

Create the form and tell the users to use the form. If they don't use
the form and mess up the database fire them.

Nov 13 '05 #6

Scott McDaniel

Glad you found a workaround ... good luck
--
Scott McDaniel
InfoTrakker Software

"deko" <de**@nospam.com> wrote in message
news:q5********************@comcast.com...

Your best bet is to implement ULS and use RWOP queries for all data
access (the FAQ has info on RWOP queries), then remove all pemissions to
tables ... with RWOP queries, users don't need to interact with the
table, only the query. Of course, you probably wouldn't be able to use
the builtin methods to import your files, and would instead have to build
your own utiltiy to control the process. If you control the process, you
can easily determine if a desired table name is already present and take
appropriate action. Even if you implement security, if you still allow
users to import items via the builtin Access methods, you'll stand the
chance of a user overwriting.

I don't really agree that this is a serious oversight ... seems perfectly
reasonable to me to overwrite a table, if the user requests it ... after
all, the software should respond to the User's request. I would agree
that the Import wizard should ask them if they want to overwrite, but I'd
also argue that a properly designed program should NEVER allow users to
directly import data to the live tables without first providing some form
of backup ... my thinking is that the application should import to
temporary tables and then allow users to append, at best, information to
existing tables, but only after making sure the original data is safe ...
just my .02 worth ...

The app does not need security so I'm reluctant to start dealing with ULS,
and the built-in utilities are definitely needed for importing data.
Personally, I think the import wizards are fantastic. The users of this
app need to import form both text and Excel files and the import wizard
makes it a snap. The barf bag is that they can destroy the database if
they overwrite an internal table. I should not have to implement ULS to
prevent this. If users want to overwrite a table, they can rename the
table in the database window. Letting them overwrite a table from an
import wizard is just bad design as far as I'm concerned.

In any case, I found a work around. If I open a recordset on a table
before the import wizard is opened, that table cannot be overwritten. I
tested this with a couple of tables and it worked great. What I'll need
to do is loop through all internal tables and open a recordset selecting
one record from each table, and then close them all when the wizard
closes. It may take some experimentation to get things just right, but
I'm confident this will serve my purposes.

Nov 13 '05 #7