How to Prevent Table Updates in a Form

[Smartin]

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

[amstelburger]

Set the locked property of the subform to True

Smartin wrote:

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

[Bob Quintal]

Smartin <sm********@yahoo.com> wrote in
news:6c********************@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

--
Bob Quintal

PA is y I've altered my email address.

[Smartin]

Bob Quintal wrote:

Smartin <sm********@yahoo.com> wrote in
news:6c********************@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

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

[Bob Quintal]

Smartin <sm********@yahoo.com> wrote in
news:nu********************@giganews.com:

Bob Quintal wrote:

Smartin <sm********@yahoo.com> wrote in
news:6c********************@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

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

Lots of questions: What happens when you try to create the .med
of your report file?
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):
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,
=== 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.

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.

[Smartin]

Bob Quintal wrote:

Smartin <sm********@yahoo.com> wrote in
news:nu********************@giganews.com:

Bob Quintal wrote:

Smartin <sm********@yahoo.com> wrote in
news:6c********************@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
Thank you for the suggestions. I can't get the .mde solution
to work for me. Any thoughts?

Lots of questions: What happens when you try to create the .med
of your report file?

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

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):

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,

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.

=== 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.

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.

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

[Bob Quintal]

Smartin <sm********@yahoo.com> wrote in
news:d6********************@giganews.com:

Bob Quintal wrote:

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

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.

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.

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


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.

--
Bob Quintal

PA is y I've altered my email address.

[Smartin]

Bob Quintal wrote:

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

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

[Bob Quintal]

Smartin <sm********@yahoo.com> wrote in
news:X_********************@giganews.com:

Bob Quintal wrote:

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
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.

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
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?
see above or below.

3. Is there a way to capture the current user's name? It's not
required at the moment, but may be useful later.
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


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

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

--
Bob Quintal

PA is y I've altered my email address.