Split mdb, Confused User

[tbl]

After splitting an mdb int o "front end/back end", and
playing an extended game of "try this", and reading the FAQ
and several other writeups, I'm still confused.

I *thought* I read that the back end tables could be locked
down solid, and that all a data-entry user needed was use of
the forms. But I seem to be finding that if I disallow
permissions on the querys, and the database object and
tables in the back end, the data-entry user can't use the
forms.

Is that how it's supposed to work?

If so, how to ensure that data-entry users don't gin up a
special shortcut and try to use the tables directly?

 

[Keith Wilby]

After splitting an mdb int o "front end/back end", and
playing an extended game of "try this", and reading the FAQ
and several other writeups, I'm still confused.

I *thought* I read that the back end tables could be locked
down solid, and that all a data-entry user needed was use of
the forms. But I seem to be finding that if I disallow
permissions on the querys, and the database object and
tables in the back end, the data-entry user can't use the
forms.

Is that how it's supposed to work?

If so, how to ensure that data-entry users don't gin up a
special shortcut and try to use the tables directly?

You can't deny access to the db object to your users, they need to get in
somehow!

The classic tried and tested approach is to deny access to the tables and
use forms bound to Run With Owner Permissions (RWOP) queries. That way your
BE tables are protected. You do of course need to deny access to the db
*window* as part of applying user-level security.

HTH - Keith.
www.keithwilby.com

 

[Joan Wild]

Remove permissions on the tables. Set the Run permissions property of your
queries to Owner. You need to set the appropriate permissions on these
queries, and the user will need Open Permission on the database object.

 

[tbl]

You can't deny access to the db object to your users, they need to get in
somehow!

The classic tried and tested approach is to deny access to the tables and
use forms bound to Run With Owner Permissions (RWOP) queries. That way your
BE tables are protected. You do of course need to deny access to the db
*window* as part of applying user-level security.

Thanks Keith, I'll give that a try.

 

[tbl]

Remove permissions on the tables. Set the Run permissions property of your
queries to Owner. You need to set the appropriate permissions on these
queries, and the user will need Open Permission on the database object.

Thanks Joan. I think I read that somewhere, but it must've
fallen out of my head.

 

[tbl]

Remove permissions on the tables. Set the Run permissions property of your
queries to Owner. You need to set the appropriate permissions on these
queries, and the user will need Open Permission on the database object.

"Open permission on the database object", meaning front end
only, or both?

 

[tbl]

Remove permissions on the tables. Set the Run permissions property of your
queries to Owner. You need to set the appropriate permissions on these
queries, and the user will need Open Permission on the database object.

It seems I'm going from stump to bump. If I can bother you
further...

How do I handle form code that changes the visibility of
some fields at different times during the data-entry
process, or changes a default value for a field?

 

[Joan Wild]

It seems I'm going from stump to bump. If I can bother you
further...

How do I handle form code that changes the visibility of
some fields at different times during the data-entry
process, or changes a default value for a field?

You shouldn't need to change your code. What error messages are you
getting?

 

[tbl]

You shouldn't need to change your code. What error messages are you
getting?

Thanks Joan.

Here's one of the bumps:

"Run-time error 2467. The expression you entered refers to
an object that is closed or doesn't exist."

The debugger takes me to:

***
Private Sub Form_Open(Cancel As Integer)
Me.Form![sfrmCountDetail].Controls![txtPage]. _
DefaultValue = 1
End Sub
***

The reason for the above code is to make sure that the
default value for [txtPage] is re-set to "1" after whatever
monkeying around was done the last time the form was used.


The other main form for which I have received the same error
message up attempting to open it under the lesser-rights
account gives this as the line of code causing the error:

***
Me![sfrmIvDetail].Form.Controls!txtMile. _
Visible = True
***

This code is in an "If" statement (procedure? thingy???. I
can never keep the names of these things straight!).

Does that give anyone a toe-hold?

These forms worked fine before losing the table permissions
for the lesser-rights users, and still work fine for admins.

With my limited knowledge and experience with Access
security, it seems to make sense that these forms would get
broken for any user not vetted for modifying forms, but then
I'm lost as to why all was well before tightening table
permissions.

And I should have mentioned that this is using Office/Access
2002, but saving in 2000 format.

 

[Joan Wild]

I'm not convinced these are the result of security settings.

"Run-time error 2467. The expression you entered refers to
an object that is closed or doesn't exist."

Are you certain that you didn't change the name of either the subform
control or the txtPage control? Does the user have open permission on the
Count Detail subform?

Nothing to do with your error, but I would have set this default in the open
event for the subform,not the mainform.
Me!txtPage.DefaultValue = 1

***
Private Sub Form_Open(Cancel As Integer)
Me.Form![sfrmCountDetail].Controls![txtPage]. _
DefaultValue = 1
End Sub
***

***
Me![sfrmIvDetail].Form.Controls!txtMile. _
Visible = True
***

Again, does the user have open permission on the sfrmIVDetail object?
That may be the cause of the errors.

 

[tbl]

I'm not convinced these are the result of security settings.


Are you certain that you didn't change the name of either the subform
control or the txtPage control? Does the user have open permission on the
Count Detail subform?

Certain. If I use one of the admins users "accounts", all
is well.

Nothing to do with your error, but I would have set this default in the open
event for the subform,not the mainform.
Me!txtPage.DefaultValue = 1

Hehe... some days the brain just doesn't want to get
running. Thanks.

***
Private Sub Form_Open(Cancel As Integer)
Me.Form![sfrmCountDetail].Controls![txtPage]. _
DefaultValue = 1
End Sub
***

***
Me![sfrmIvDetail].Form.Controls!txtMile. _
Visible = True
***

Again, does the user have open permission on the sfrmIVDetail object?
That may be the cause of the errors.

I'm off to double check that. Mornings are always better
for trouble-shooting!

 

[tbl]

I'm not convinced these are the result of security settings.

"Run-time error 2467. The expression you entered refers to
an object that is closed or doesn't exist."

Are you certain that you didn't change the name of either the subform
control or the txtPage control?

Certain.


Does the user have open permission on the Count Detail subform?

Yes.

***
Me![sfrmIvDetail].Form.Controls!txtMile. _
Visible = True
***

Again, does the user have open permission on the sfrmIVDetail object?
That may be the cause of the errors.

That was interesting! The lesser-rights group *didn't* have
permission to run this form, so I "fixed" that, thinking
"AHAH!". But alas, the result is the same. So I gave that
group permissions to run all the forms. Same error message.

"Run-time error 2467. The expression you entered refers to
an object that is closed or doesn't exist."

Scratching my chin...

 

[tbl]

I'm not convinced these are the result of security settings.

Finally got it fixed. It turns out that several subforms,
and even a couple of forms, didn't have saved queries to
fetch the data--just SQL. Once I put saved queries in
place, all worked well.

The lights came on after reading your replies over and over,
giving the gold dust time to settle.

Thanks for your help.

 

[Joan Wild]

There's nothing wrong with using SQL statements as a record source for the
form, as long as the users have permissions on the tables/queries used in
that SQL statement. One thing you can do, is use RWOP queries in the SQL
statements, and you don't have to give users any permissions on the
underlying tables.

 

[tbl]

There's nothing wrong with using SQL statements as a record source for the
form, as long as the users have permissions on the tables/queries used in
that SQL statement. One thing you can do, is use RWOP queries in the SQL
statements, and you don't have to give users any permissions on the
underlying tables.

Right. I should have said, "...once I put saved *RWOP*
queries in place, all the "errors" in my code vaporized.

Thanks again, Joan.