How to lock tables with vba code?

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?

re: How to lock tables with vba code?

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" <deko@nospam.com> wrote in message
news:O-ednbFpWJSR6ZneRVn-3A@comcast.com...[color=blue]
> 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?
>
>[/color]

re: How to lock tables with vba code?

> Access ULS is the most secure method you could use. ULS will allow you[color=blue]
> 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.[/color]

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.

re: How to lock tables with vba code?

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" <deko@nospam.com> wrote in message
news:eNOdnb8OT8AE4JneRVn-pw@comcast.com...[color=blue][color=green]
>> 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.[/color]
>
> 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.
>
>[/color]

re: How to lock tables with vba code?

> Your best bet is to implement ULS and use RWOP queries for all data access[color=blue]
> (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 ...[/color]

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.

re: How to lock tables with vba code?

deko wrote:
[color=blue]
> 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?[/color]

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.

re: How to lock tables with vba code?

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

"deko" <deko@nospam.com> wrote in message
news:q5GdnfAKtPTHDpneRVn-pw@comcast.com...[color=blue][color=green]
>> 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 ...[/color]
>
> 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.
>
>[/color]