How to Prevent Table Updates in a Form

I have a form in Access97 which facilitates a query against a production
Access database. Users will enter some search terms and see a datasheet
view of the results in a subform. The prod table is linked (the form is
not included in the production mdb file). I need to ensure that an
unexperienced user will not accidentally change the prod table. What
measures should I take?

So far I have done the following:

In the subform, set Allow Edits, Allow Deletions, Allow Additions to NO.
In the database, set Tools.Startup.Display Form to show the search form
and unchecked "Display Database Window".

Is there anything else I should consider? I expect the average user to
have very limited knowledge of Access.

Regards,
--
Smartin

re: How to Prevent Table Updates in a Form

Set the locked property of the subform to True

Smartin wrote:[color=blue]
> I have a form in Access97 which facilitates a query against a production
> Access database. Users will enter some search terms and see a datasheet
> view of the results in a subform. The prod table is linked (the form is
> not included in the production mdb file). I need to ensure that an
> unexperienced user will not accidentally change the prod table. What
> measures should I take?
>
> So far I have done the following:
>
> In the subform, set Allow Edits, Allow Deletions, Allow Additions to NO.
> In the database, set Tools.Startup.Display Form to show the search form
> and unchecked "Display Database Window".
>
> Is there anything else I should consider? I expect the average user to
> have very limited knowledge of Access.
>
> Regards,
> --
> Smartin[/color]

re: How to Prevent Table Updates in a Form

Smartin <smartin108@yahoo.com> wrote in
news:6cSdnaW0hO772s3eRVn-rA@giganews.com:
[color=blue]
> I have a form in Access97 which facilitates a query against a
> production Access database. Users will enter some search terms
> and see a datasheet view of the results in a subform. The prod
> table is linked (the form is not included in the production
> mdb file). I need to ensure that an unexperienced user will
> not accidentally change the prod table. What measures should I
> take?
>
> So far I have done the following:
>
> In the subform, set Allow Edits, Allow Deletions, Allow
> Additions to NO. In the database, set Tools.Startup.Display
> Form to show the search form and unchecked "Display Database
> Window".
>
> Is there anything else I should consider? I expect the average
> user to have very limited knowledge of Access.
>
> Regards,
> --
> Smartin[/color]

1) If the production database has security enabled, consider
creating a read-only user and accessing the table through that
user's login.

2) Create a .mde and distribute the mde to your users instead of
the .mdb





--
Bob Quintal

PA is y I've altered my email address.

re: How to Prevent Table Updates in a Form

Bob Quintal wrote:[color=blue]
> Smartin <smartin108@yahoo.com> wrote in
> news:6cSdnaW0hO772s3eRVn-rA@giganews.com:
>
>[color=green]
>>I have a form in Access97 which facilitates a query against a
>>production Access database. Users will enter some search terms
>>and see a datasheet view of the results in a subform. The prod
>>table is linked (the form is not included in the production
>>mdb file). I need to ensure that an unexperienced user will
>>not accidentally change the prod table. What measures should I
>>take?
>>
>>So far I have done the following:
>>
>>In the subform, set Allow Edits, Allow Deletions, Allow
>>Additions to NO. In the database, set Tools.Startup.Display
>>Form to show the search form and unchecked "Display Database
>>Window".
>>
>>Is there anything else I should consider? I expect the average
>>user to have very limited knowledge of Access.
>>
>>Regards,
>>--
>>Smartin[/color]
>
>
> 1) If the production database has security enabled, consider
> creating a read-only user and accessing the table through that
> user's login.
>
> 2) Create a .mde and distribute the mde to your users instead of
> the .mdb[/color]

Thank you for the suggestions. I can't get the .mde solution to work for
me. Any thoughts?

My form is in an Access database file which doesn't actually contain any
data. The only "table" is linked from the prod .mdb file. I tried making
an .mde out of the prod file and linking to that instead but the result
is the same.

The result is the entire linked table is returned regardless of the
search terms. When I put some search terms in and launch the query, I
still get the entire table back. Since I am setting properties of the
subform object at runtime I'm guessing my problem is related to the
following caveat (found in help under About MDE files):

=== quote
[MDE files prevent] Changing code using the properties or methods of the
Microsoft Access or VBA Object models--an MDE file contains no source code.
=== end quote

Maybe what I really should do is rewrite this as a stand-alone front end
application.
--
Smartin

re: How to Prevent Table Updates in a Form

Smartin <smartin108@yahoo.com> wrote in
news:nuadnXJpC9gK9czeRVn-gA@giganews.com:
[color=blue]
> Bob Quintal wrote:[color=green]
>> Smartin <smartin108@yahoo.com> wrote in
>> news:6cSdnaW0hO772s3eRVn-rA@giganews.com:
>>
>>[color=darkred]
>>>I have a form in Access97 which facilitates a query against a
>>>production Access database. Users will enter some search
>>>terms and see a datasheet view of the results in a subform.
>>>The prod table is linked (the form is not included in the
>>>production mdb file). I need to ensure that an unexperienced
>>>user will not accidentally change the prod table. What
>>>measures should I take?
>>>
>>>So far I have done the following:
>>>
>>>In the subform, set Allow Edits, Allow Deletions, Allow
>>>Additions to NO. In the database, set Tools.Startup.Display
>>>Form to show the search form and unchecked "Display Database
>>>Window".
>>>
>>>Is there anything else I should consider? I expect the
>>>average user to have very limited knowledge of Access.
>>>
>>>Regards,
>>>--
>>>Smartin[/color]
>>
>>
>> 1) If the production database has security enabled, consider
>> creating a read-only user and accessing the table through
>> that user's login.
>>
>> 2) Create a .mde and distribute the mde to your users instead
>> of the .mdb[/color]
>
> Thank you for the suggestions. I can't get the .mde solution
> to work for me. Any thoughts?[/color]

Lots of questions: What happens when you try to create the .med
of your report file?[color=blue]
>
> My form is in an Access database file which doesn't actually
> contain any data. The only "table" is linked from the prod
> .mdb file. I tried making an .mde out of the prod file and
> linking to that instead but the result is the same.
>
> The result is the entire linked table is returned regardless
> of the search terms. When I put some search terms in and
> launch the query, I still get the entire table back. Since I
> am setting properties of the subform object at runtime I'm
> guessing my problem is related to the following caveat (found
> in help under About MDE files):
>[/color]
That should not cause a problem. You can change the recordsource
of a subform in an .mde. But the code has to generate the sql
statement entirely,
[color=blue]
> === quote
> [MDE files prevent] Changing code using the properties or
> methods of the Microsoft Access or VBA Object models--an MDE
> file contains no source code. === end quote
>
> Maybe what I really should do is rewrite this as a stand-alone
> front end application.[/color]

You may also want to try the trick of importing the table from
the production databsae, so that even if some hacker messes it
up, the next time the application is opened, it reloads the
data.

--
Bob Quintal

PA is y I've altered my email address.

re: How to Prevent Table Updates in a Form

Bob Quintal wrote:[color=blue]
> Smartin <smartin108@yahoo.com> wrote in
> news:nuadnXJpC9gK9czeRVn-gA@giganews.com:
>
>[color=green]
>>Bob Quintal wrote:
>>[color=darkred]
>>>Smartin <smartin108@yahoo.com> wrote in
>>>news:6cSdnaW0hO772s3eRVn-rA@giganews.com:
>>>
>>>
>>>
>>>>I have a form in Access97 which facilitates a query against a
>>>>production Access database. Users will enter some search
>>>>terms and see a datasheet view of the results in a subform.
>>>>The prod table is linked (the form is not included in the
>>>>production mdb file). I need to ensure that an unexperienced
>>>>user will not accidentally change the prod table. What
>>>>measures should I take?
>>>>
>>>>So far I have done the following:
>>>>
>>>>In the subform, set Allow Edits, Allow Deletions, Allow
>>>>Additions to NO. In the database, set Tools.Startup.Display
>>>>Form to show the search form and unchecked "Display Database
>>>>Window".
>>>>
>>>>Is there anything else I should consider? I expect the
>>>>average user to have very limited knowledge of Access.
>>>>
>>>>Regards,
>>>>--
>>>>Smartin
>>>
>>>
>>>1) If the production database has security enabled, consider
>>>creating a read-only user and accessing the table through
>>>that user's login.
>>>
>>>2) Create a .mde and distribute the mde to your users instead
>>>of the .mdb[/color]
>>
>>Thank you for the suggestions. I can't get the .mde solution
>>to work for me. Any thoughts?[/color]
>
>
> Lots of questions: What happens when you try to create the .med
> of your report file?[/color]

I assume you meant .mde? The .mde's are generated without any complaint
from Access. No error messages. This is true of both my search file and
the prod database. However

Search MDB + Prod MDB = Works fine
Search MDB + Prod MDE = Works fine
Search MDE + Prod MDB = Fails to query correctly
Search MDE + Prod MDE = Fails to query correctly
[color=blue]
>[color=green]
>>My form is in an Access database file which doesn't actually
>>contain any data. The only "table" is linked from the prod
>>.mdb file. I tried making an .mde out of the prod file and
>>linking to that instead but the result is the same.
>>
>>The result is the entire linked table is returned regardless
>>of the search terms. When I put some search terms in and
>>launch the query, I still get the entire table back. Since I
>>am setting properties of the subform object at runtime I'm
>>guessing my problem is related to the following caveat (found
>>in help under About MDE files):
>>[/color]
>
> That should not cause a problem. You can change the recordsource
> of a subform in an .mde. But the code has to generate the sql
> statement entirely,[/color]

When the "Search" button is clicked the SQL statement is generated on
the fly as "SELECT {columns} FROM {table} WHERE {conditions};" and
assigned to the subform's RecordSource property.
[color=blue][color=green]
>>=== quote
>>[MDE files prevent] Changing code using the properties or
>>methods of the Microsoft Access or VBA Object models--an MDE
>>file contains no source code. === end quote
>>
>>Maybe what I really should do is rewrite this as a stand-alone
>>front end application.[/color]
>
>
> You may also want to try the trick of importing the table from
> the production databsae, so that even if some hacker messes it
> up, the next time the application is opened, it reloads the
> data.[/color]

Do I understand you correctly... Can I import the data table at runtime
and "disconnect" from the production database? That sounds like a good
way to go. If you can point me in the right direction for how to do this
with VBA I'd appreciate it.

The prod table is updated once a day more or less. I'd like for the
search to always be looking at current data without having to manually
create an .mde every time the prod database is updated.

Thanks again.
--
Smartin

re: How to Prevent Table Updates in a Form

Smartin <smartin108@yahoo.com> wrote in
news:d6qdnVzSpLAQA8zeRVn-vA@giganews.com:
[color=blue]
> Bob Quintal wrote:[color=green][color=darkred]
>>>Maybe what I really should do is rewrite this as a
>>>stand-alone front end application.[/color]
>>
>>
>> You may also want to try the trick of importing the table
>> from the production databsae, so that even if some hacker
>> messes it up, the next time the application is opened, it
>> reloads the data.[/color]
>
> Do I understand you correctly... Can I import the data table
> at runtime and "disconnect" from the production database? That
> sounds like a good way to go. If you can point me in the right
> direction for how to do this with VBA I'd appreciate it.
>[/color]
Yes, create the code for a delete query and an insert query
using the general form of

DeleteSQL = "Delete * from table1;"
AppendSQL ="INSERT into table1 Alias Target .... SELECT ...
FROM Table1 IN C:\OtherDir\Production.mdb Alias Source;

And execute them from code.

sql.execute strDeleteSQL
sql.execute AppendSQL




[color=blue]
> The prod table is updated once a day more or less. I'd like
> for the search to always be looking at current data without
> having to manually create an .mde every time the prod database
> is updated.
>
> Thanks again.[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: How to Prevent Table Updates in a Form

Bob Quintal wrote:
[color=blue]
> Yes, create the code for a delete query and an insert query
> using the general form of
>
> DeleteSQL = "Delete * from table1;"
> AppendSQL ="INSERT into table1 Alias Target .... SELECT ...
> FROM Table1 IN C:\OtherDir\Production.mdb Alias Source;
>
> And execute them from code.
>
> sql.execute strDeleteSQL
> sql.execute AppendSQL[/color]

Thanks for that! OK I've got this working. I copied the prod table into
my database, set up a table to record when refreshes are done, and wrote
some routines fired by the form's open event that check the last refresh
time to see if it's time to refresh again.

More questions?

1. The delete/insert queries take a few minutes to complete. Potentially
other users will try to open the database while updates are in process.
Is there a way to lock out other users while the update is underway and
signal to them to wait a few minutes? This app will deployed on a shared
network resource.

2. What if the prod database is currently in use when a user's instance
determines it's time to refresh? Can I detect this somehow so I can code
around it?

3. Is there a way to capture the current user's name? It's not required
at the moment, but may be useful later.

I seem to be drifting away from my original topic. Thanks for all your help!
--
Smartin

re: How to Prevent Table Updates in a Form

Smartin <smartin108@yahoo.com> wrote in
news:X_udnRBJZe7JU8zeRVn-jA@giganews.com:
[color=blue]
> Bob Quintal wrote:
>[color=green]
>> Yes, create the code for a delete query and an insert query
>> using the general form of
>>
>> DeleteSQL = "Delete * from table1;"
>> AppendSQL ="INSERT into table1 Alias Target .... SELECT ...
>> FROM Table1 IN C:\OtherDir\Production.mdb Alias Source;
>>
>> And execute them from code.
>>
>> sql.execute strDeleteSQL
>> sql.execute AppendSQL[/color]
>
> Thanks for that! OK I've got this working. I copied the prod
> table into my database, set up a table to record when
> refreshes are done, and wrote some routines fired by the
> form's open event that check the last refresh time to see if
> it's time to refresh again.
>
> More questions?
>
> 1. The delete/insert queries take a few minutes to complete.
> Potentially other users will try to open the database while
> updates are in process. Is there a way to lock out other users
> while the update is underway and signal to them to wait a few
> minutes? This app will deployed on a shared network resource.
>[/color]
I have my routines fire at 2:00 AM. You can write a command
line that starts a macro, which runs the code and quits the
application. Use the command in Start->settings->scheduled
tasks. It's the only macro in the database, everything else is
in modules
[color=blue]
> 2. What if the prod database is currently in use when a user's
> instance determines it's time to refresh? Can I detect this
> somehow so I can code around it?
>[/color]
see above or below.

[color=blue]
> 3. Is there a way to capture the current user's name? It's not
> required at the moment, but may be useful later.[/color]

Yes, see http://www.mvps.org/access/general/gen0034.htm if you
have security enabled, and
http://www.mvps.org/access/api/api0008.htm if you don't

[color=blue]
>
> I seem to be drifting away from my original topic. Thanks for
> all your help![/color]

That's databse development.... one thing leads to another.

--
Bob Quintal

PA is y I've altered my email address.