RWOP query with parameters

[Tom Stoddard]

In order to use an RWOP query to insert or delete data from certain tables
which users have read only permission on I find myself needing to use
parameters in my query. The user has to have open/run permission on the
query itself and my intention is to set the value of the parameters in code
by using values from a form whose data is restricted so that the user can
only insert or delete records related to records meeting certain criteria.

This works fine but then I realized that the user could just open the query
directly (if they can get to it) and fill in the parameters manually,
thereby bypassing the security that I've tried to provide. Is a RWOP query
with parameters considered secure? It seems to me that it opens a security
hole. Is there some other way to accomplish what I need to do?

[Chris Mills]

What sort of parameters?
You can refer to a field on a form in SQL by, say:
WHERE something = [Forms]![MyForm]![MyField]

Then you change or set the data in that form field and requery.

I'm not sure if you'd call that a "parameter query", since I take "parameter
query" to mean asking the operator directly for something when the query is
run. (per the Help)

The SQL statement therefore doesn't need "changing". It's very dangerous to
change or write RWOP queries by code, because the "owner" becomes whoever is
logged on at the time the code writes the RWOP, which kinda defeats the
purpose of it.

I don't believe "parameters" are appropriate, or not the right term, unless
you DID want to request some entry direct from the operator, as against the
operator entering it somewhere on a form/unbound field and the query picking
that up.

Chris

"Tom Stoddard" <tsto...@andrewspaper.com> wrote in message
news:e9EiS93...@TK2MSFTNGP15.phx.gbl...

[Tom Stoddard]

> What sort of parameters?
> You can refer to a field on a form in SQL by, say:
> WHERE something = [Forms]![MyForm]![MyField]

In your example, [Forms]![MyForm]![MyField] is a parameter of sorts. If a
user ran your query when the form was not open, they would be prompted for
that value. They could type it in directly and the query would run even when
the form is not open. When the form is open, Access is able to provide the
value itself, if it can't evaluate the parameter itself it will prompt the
user. You can provide those values in code before you execute the query so
that Access won't prompt the user. The idea of providing those parameters in
code is that you're not limited to fields on a form. For example, I can set
a value for a parameter with a value I've found in a recordset that I have
open in the procedure I'm running. I have found that certain queries will
not work when I put references to controls in the query grid and then try to
execute them using code but they will when I create a parameter and provide
its value in code. I create the parameter by putting a name inside of
brackets in the query grid. Try creating a simple query and putting [Value1]
in the criteria of a field in the query grid and then opening the query.
You'll be prompted to provide a value. In code I can set that value by using
this code: CurrentDb.QueryDefs("qryMyQuery").Parameters("Value1") =
Something. Then I can do: CurrentDb.QueryDefs("qryMyQuery").Execute and the
query will run.

>
> The SQL statement therefore doesn't need "changing". It's very dangerous
> to
> change or write RWOP queries by code, because the "owner" becomes whoever
> is
> logged on at the time the code writes the RWOP, which kinda defeats the
> purpose of it.
>

According to the security faq, RWOP is ignored if the query is defined in
code. It will only work if its part of a saved query.

I'm doing this because I want to add a record to a table that the user
doesn't have insert permissions on. The query that the form is based on is
not updateable so I've created an Append query with RWOP and added
parameters and then saved the query. I'm trying to execute it in code behind
the form. It works well in one instance but I'm having trouble getting to
work on another form I'm working on now. For some reason the record isn't
getting added and I can't figure out if its due to the query or security or
something I haven't thought of yet.

Thanks

[Chris Mills]

> In your example, [Forms]![MyForm]![MyField] is a parameter of sorts.

Well, yes. If the form is not open then the operator is asked, certainly.
Nevertheless, it's one way of automatically passing a parameter, and works
fine.

Of course, such a query is only designed to be run with the referenced form
open. It could, for instance, be stored in the Recordsource of the form and
reference Me!MyField.

> The idea of providing those parameters in
> code is that you're not limited to fields on a form.

I think it's easier to provide them via a form. I think if you're re-writing
an RWOP via code then you stand the danger of altering the "owner". You don't
have a lot of control over that.

You could have a basic RWOP stored of course, and a further parameter query
based on that.

I have a feeling that your manipulation of Querydefs looks to me far too
complicated, when as I understand it all you want to do is alter the WHERE
parameter data, much like a filter. If it's associated (run from) a form, then
the form will be open won't it?

> According to the security faq, RWOP is ignored if the query is defined in
> code. It will only work if its part of a saved query.

Yes. Writing through code is pointless. However I did find one thing (you can
test it yourself). If you write a SQL statement in the Recordsource and save
the form design, that SQL statement appears to store as the owner, who you
were logged on as when you saved it (which would normally be the developer or
"owner", whoever). But as soon as you re-write the RWOP recordsource through
code, well it's re-written, again with whoever was logged on at the time, in
this case the user becomes the "owner". So there is a case where the RWOP does
not have to be stored (as a separate query). Of course, there's a potential
danger in this, in that it is not immediately obvious who the owner is, so it
would be easy to make an inadvertent mistake.

I'm not really sure what your issue is. If a query is stored then certainly a
user could just run the query. That's why you lock-down the database with
AllowBypassKeys, to remove such a view. I have given a special case,
apparently not well-known, where an RWOP can be stored directly in a
Recordsource. Depending on the circumstances of course, but I don't have a
great pressing reason to use RWOP. Using a form field for a parameter works
great. It is possible, that if "they" have as much Access knowledge as you
seem to be attempting to prevent, you may be wasting your time whatever you
do. To me, all this stuff about "parameter passing" is for operator
convenience and a nice-working form. If they can get to a stored RWOP query,
then they can run it, yes. That's how Access works. I suspect there's a point,
with Access, where you may be trying to be "too smart" as it were. But let
others give their ideas, by all means.

Chris

[TC]

I've got to this one before the email one :-)

Remember that the purpose of an RWOP query is to let the user access
objects to which he normally does not have access. And the purpose of a
parameterized query is to let the caller pass one or more values, at
runtime, which affect how the query works, in some way defined by the
person who wrote the query.

You can put those two things together & they will both work 100%
correctly. The result will be, a query that lets the user access
objects to which he normally does not have access, and which accepts
one or more values at runtime which affect how the query works, in some
way defined by the person who wrote the query.

So, if you write the query in such a way that people can select
inappropriate data by passing the right parameter values, the problem
is how you wrote the query - it's nothing wrong with RWOP, or
parameterized, queries.

HTH,
TC

[TC]

PS. One for you, Tom: why can't a person who uses an RWOP query, just
edit the query SQL directly, so he can can do /any/ data retrieval or
manipulation that the owner of the query can do? (Not just the specific
data retrieval or manipulation that the query owner wrote it for
originally?)

I know the answer already. I just feel you're going to be asking this
soon :-)

Cheers,
TC

[Joan Wild]

Chris Mills wrote:
> Yes. Writing through code is pointless. However I did find one thing
> (you can test it yourself). If you write a SQL statement in the
> Recordsource and save the form design, that SQL statement appears to
> store as the owner, who you were logged on as when you saved it
> (which would normally be the developer or "owner", whoever).

Compact the database, and see if that holds true.

--
Joan Wild

[Tom Stoddard]

"TC" <aatcbb...@yahoo.com> wrote in message
news:1128741315.8...@g47g2000cwa.googlegroups.com...

I assume that the user can't rewrite the sql if he/she doesn't have
permission to change the design of the query. That's my point about
parameters; a user doesn't need to change the design of the query when they
provide values for parameters but in effect, the value of the parameters
become part of the Where clause of the query.

Am I mistaken?

[Joan Wild]

Tom Stoddard wrote:
>
> I'm doing this because I want to add a record to a table that the user
> doesn't have insert permissions on. The query that the form is based
> on is not updateable so I've created an Append query with RWOP and
> added parameters and then saved the query. I'm trying to execute it
> in code behind the form. It works well in one instance but I'm having
> trouble getting to work on another form I'm working on now. For some
> reason the record isn't getting added and I can't figure out if its
> due to the query or security or something I haven't thought of yet.

Did you give the users insert permission on the RWOP query?

--
Joan Wild

[Tom Stoddard]

>
> Did you give the users insert permission on the RWOP query?
>
> --
> Joan Wild
>

Yes, I did Joan. Thanks! I figured it out. I was actually executing two
queries inside of a transaction. There was an error ocurring before the
transaction was committed. It works fine as long as I don't use the
transaction. That raises another question, however, which isn't really
related to security but you might be able to answer it anyway.

The reason for the two queries is that I want to add records to a second
table which is related to the first table I'm append the records to. The
problem is that I need to know what the primary key field of the new record
is before I can add the subsequent records to the second table. If I don't
use a transaction I can get that value by using a DLookup function against
the first table right after I've inserted the record. If there is an
uncommitted transaction then the DLookup function causes an error. If the
transaction is committed, or there is no transaction at all, then the code
works perfectly. The question is:

Is there an easier way to get the primary key value for a record when its
added to a table which uses an autonumber field (set to generate random
numbers because the database is replicated) as it's primary key (or
identity)? I've tried searching for this online but haven't found a solution
related to Access yet.

Thanks Again!

[Chris Mills]

> Compact the database, and see if that holds true.
>

Good suggestion. Compacting doesn't affect it.

It gives every appearance that the owner of the form is the owner of the
recordsource sql, when saved with the form design. I'd be perfectly happy if
there's a catch in it. I'm only saying what I found.

Thanks
Chris

[Joan Wild]

I can remember coming across this before. It worked for a while, and then
the user started getting no permissions errors - the only thing I could
think of was the frontend getting compacted and clearing out all the temp
queries.

It worked for a bit, and then would stop.

--
Joan Wild
Microsoft Access MVP

[Joan Wild]

--
Joan Wild
Microsoft Access MVP

Tom Stoddard wrote:
>
> The reason for the two queries is that I want to add records to a
> second table which is related to the first table I'm append the
> records to. The problem is that I need to know what the primary key
> field of the new record is before I can add the subsequent records to
> the second table. If I don't use a transaction I can get that value
> by using a DLookup function against the first table right after I've
> inserted the record.

You can't rely on that in a multiuser environment. Also how are you looking
it up?

> Is there an easier way to get the primary key value for a record when
> its added to a table which uses an autonumber field (set to generate
> random numbers because the database is replicated) as it's primary
> key (or identity)? I've tried searching for this online but haven't
> found a solution related to Access yet.

Using DAO you can grab the new ID before you update the main record...
Private Sub cmdAddRecords_Click()
On Error GoTo cmdAddRecords_Click_Error
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim dbs As Database
Dim rstMain As Recordset
Dim intMainID As Long

fInTrans = False
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbs = CurrentDb()
Set rstMain = dbs.OpenRecordset("MainTable", , dbAppendOnly)

wrkCurrent.BeginTrans
fInTrans = True
With rstMain
.AddNew
!somefield = whatever
!anotherfield= whatever
intMainID = rstMain!PrimaryKey
.Update
End With
'then here you can run your append query into the sub table specifying
intMainID as the value for the foreign key field.
dbs.execute "INSERT INTO.....

wrkCurrent.CommitTrans
fInTrans = False

cmdAddRecords_Click_Exit:
On Error Resume Next
rstMain.Close
Set rstMain = Nothing
Set dbs = Nothing
Set wrkCurrent = Nothing
Exit Sub

cmdAddRecords_Click_Error:
If fInTrans = True Then
wrkCurrent.Rollback
MsgBox "The records were not added"
End If
Select Case Err.Number
Case Else
MsgBox "Error #" & Err.Number & " :" & vbCrLf & vbCrLf &
Err.Description
End Select
Resume cmdAddRecords_Click_Exit

[Chris Mills]

Oh well, I'm certainly not offering to extend the Access warranty :-)

I can see that it's "iffy" in that it's not really documented, but then a lot
of the precise detail of Access is not well documented. And certainly one
could easily make a mistake, since the "owner" is not visible. But I don't
know why it would be "intermittent". Maybe it is.

It's arguable whether there is any point of course, in that recordset is a
crackable property with some mde crackers (I think), unlike code at the
present time. I was just contemplating how RWOP's can be "hidden", a wastefull
exercise if ever there was one :-)

Well, might stop the "average" umm...something.

Ta
Chris

"Joan Wild" <jw...@nospamtyenet.com> wrote in message

[Tom Stoddard]

"Joan Wild" <jw...@nospamtyenet.com> wrote in message

news:%23W6aEJF...@TK2MSFTNGP14.phx.gbl...

>
>
> --
> Joan Wild
> Microsoft Access MVP
>
> Tom Stoddard wrote:
>>
>> The reason for the two queries is that I want to add records to a
>> second table which is related to the first table I'm append the
>> records to. The problem is that I need to know what the primary key
>> field of the new record is before I can add the subsequent records to
>> the second table. If I don't use a transaction I can get that value
>> by using a DLookup function against the first table right after I've
>> inserted the record.
>
> You can't rely on that in a multiuser environment. Also how are you
> looking it up?

This is not a transactional application. It would be extremely unlikely for
2 users to be editing the same record at the same time and since the
autonumber field is generating random numbers, the likelihood of duplicate
primary keys is not worth the trouble of doing it otherwise.

Joan, I was doing just that before I implemented security but now I can't
update the table that way because the user doesn't have permission. I had to
create a RWOP query and save it in order for the user to be able to update
the records I want him to be able to edit. That's how this all started.

Thanks!

>

[Chris Mills]

Look. Call me simple. "You're simple, Chris" "Thankyou!"

I don't really know what your overall design scheme is. I just think there are
lots of complications with doing background table manipulation whilst the same
stuff is open through bound forms. Requery, things like that, sure. Standard
bound forms/subforms can do most things, automatically.

Why not just use all the in-built power of Access? With a stored RWOP if you
need it? I once got hot on "unbound forms", where you certainly have full
control, but they just weren't worth the hassle not least in maintenance.

The average office worker wouldn't know a bound from an unbound form. What
exactly are you trying to protect against? The KGB...Tom Wickerath...?

Please don't think I'm saying anything more than that there's some merit in
keeping things simple. As reasonably as possible. Hide the database window and
don't worry about them getting into stored queries (say). Don't worry about a
form not being open turning a Where clause into a parameter - just have the
referenced form open or self-referenced. If they can do things like
(whatever), who's to say what they can do?

You have to define the "threat" and probability, before you can address it.
HTH
"Simple Chris"

[TC]

Sure; the parameters are used however you use them. If you refer to
them in the WHERE clause, they will affect the WHERE clause. If you
don't, they won't. There are various places you could use a parameter
apart from the WHERE clause (although that would probably be the
commonest place).

Again, it is entirely up to the query designer. If you write a query
like this:

SELECT * FROM S3CR3T
WHERE ID_NUMBER = [parameter]

then, people who run that query can presumeably see /any/ ID number,
just by passing the right parameter value. If that is not appropriate,
then, you have written the query improperly. It's really nothing to do
with whether it is an RWOP query, or not.

But if you write a query like this, for example:

SELECT * FROM S3CR3T
WHERE CREATED_BY = CURRENTUSER()
AND CREATED_DATE >= [parameter]

then, the parameter does not let the user see records they shouldn't be
able to see.

Again, if a query lets a user display or edit inappropriate records,
then, the query has been written incorrectly. It's not a shortcoming in
queries, or parameters, or the RWOP feature.

HTH,
TC

[TC]

If an updatable, multi-table query is properly designed, Jet will fill
out the related fields for you - automagically! There is no need to do
that manually. But it can be tricky to get it to work:

- Obviously all the tables must be primary-keyed correctly;
- their relationships must be defined in the relationships window, and
- IIRC, all the relevant fields must be included in the SELECT list.

Do some research on multi-table updatable queries :-)

HTH,
TC

[Tom Stoddard]

TC,

I guess this all comes down to the updateable issue. Let me explain my
precise problem and see if you have a solutions to offer.

I will try to use the simplest example I can come up with. I have a
SalesReps table with 2 fields, SalesRepName & SalesRepID. I have a Customers
table with 3 fields, CustomerID, CustomerName, SalesRepID. They are related
with referential integrity through the SalesRepID field. Then I created a
Permissions table which has 2 fields, UserName & SalesRepID which is related
to the SaleReps table also with referential integrity. Now I want some users
to be able to edit, add & delete customers in the Customers table as long as
the customer is assigned to a SalesRep which the user has an entry in the
Permissions table for. For example, if there is a record in the Permissions
table with Tom in the UserName Field and 5 in the SalesRepID field then Tom
should be able to delete customers from the Customers table as long as the
SalesRepID for the Customer he deletes is 5. I can enforce that restriction
many ways but when it comes time to delete the Customer I need to use a RWOP
query because no users have delete data permissions on the Customers table.

Now I create a form and base it on a query that uses the Customers table,
the SalesReps table and the Permissions table (or I can do it with just the
Customers & Permissions table). The Customers table is related to the
SaleReps table and the SalesReps table is related to the Permissions table
but the Permissions table is not directly related to the Customers table. It
seems that as long as the Permissions table is part of the same query that
the Customers table is in, then I can't find a way to structure it to allow
edits, deletions or additions. Am I missing something? I have to use the
Permissions table to limit which customers records are displayed on the
form. I end up having to use one query to base my form on and then when the
user selects a record to delete I have to run a different query to
accomplish that. Thats's where the parameters come in.

It's working this way but its a lot more work than I wanted to do to enforce
this level of security. Any suggestions would be appreciated.

"TC" <aatcbb...@yahoo.com> wrote in message

news:1128836712.7...@g47g2000cwa.googlegroups.com...

[Joan Wild]

Tom Stoddard wrote:
>>
>> You can't rely on that in a multiuser environment. Also how are you
>> looking it up?
>
> This is not a transactional application. It would be extremely
> unlikely for 2 users to be editing the same record at the same time
> and since the autonumber field is generating random numbers, the
> likelihood of duplicate primary keys is not worth the trouble of
> doing it otherwise.

Yes, but you are looking up via DLookup the autonumber that was just added,
and since they are random, how do you look it up?

>>> Is there an easier way to get the primary key value for a record
>>> when its added to a table which uses an autonumber field (set to
>>> generate random numbers because the database is replicated) as it's
>>> primary key (or identity)? I've tried searching for this online but
>>> haven't found a solution related to Access yet.
>>
>> Using DAO you can grab the new ID before you update the main

SNIP

>
> Joan, I was doing just that before I implemented security but now I
> can't update the table that way because the user doesn't have
> permission. I had to create a RWOP query and save it in order for the
> user to be able to update the records I want him to be able to edit.
> That's how this all started.

OK, so create a RWOP query SELECT * FROM sometable...
Then set the rstMain to this saved query and append to it. Ditto for the
subtable.

[Tom Stoddard]

>
>>
>> Joan, I was doing just that before I implemented security but now I
>> can't update the table that way because the user doesn't have
>> permission. I had to create a RWOP query and save it in order for the
>> user to be able to update the records I want him to be able to edit.
>> That's how this all started.
>
> OK, so create a RWOP query SELECT * FROM sometable...
> Then set the rstMain to this saved query and append to it. Ditto for the
> subtable.
>

That's why I keep asking these questions. Sooner or later someone shows me
the obvious and the light goes on. Thanks Joan!

[TC]

Hi Tom

Let's see if we can work through this :-)

Table structures
----------------

First, multi-table qwuestions are too hard to understand, when you
describe them only as normal narrative text. You need to show the table
structures in some clearer way. Here's how I do it:

SalesReps
SalesRepID (PK?)
CustomerID
CustomerName

Permissions
UserName (PK?)
SalesRepID

You didn't define the Customers table, aso I'll make the obvious guess:

Customers
CustomerID (PK)
CustomerName

Second, it is an imperative, basic requirement of such problems, that
you clearly state the primary key (PK) of each table. The fact that you
haven't done that, makes me wonder whether you have defined them
correctly. I really should stop here, until you have done that.
However, let's plow on, regardless!

I've marked my idea of what the primary key should be for each table.
But, now we have a problem. If UserName is the PK of the Permissions
table, then, that table can have at most one record for a given User.
That means, a given User can have at most one SalesRepID. And, if
SalesRepID is the PK of the SalesRep table, that means that each
SalesRepID can have at most one CustomerID. So, putting all of that
together, each User can have at most one CustomerID. So the SalesReps
table is not required, and you could put the CustomerID directly in the
Permissions table!

That does not make sense. So, either, you have mis-designed the tables,
or I have mis-guessed the PK of one or more tables. Reviwing the
design, I'll change the PK of the Permissions table to include both
fields. This leads to a multi-field, or so-called "composite" primary
key. You're probably starting to see why it is so important that you
state your primary keys explicuitly when you describe the problem!

Permissions
UserName (P..)
SalesRepID (..K)

Now you should check, in the table design view, that you have defined
each primary key correctly. If you get the primary keying wrong,
everything goes out the window, and many things won't work the way you
think they should!

Third, by including CustomerID in the SalesReps table, you are saying:
"each sales rep has at most one current customer". If a sales rep can
have /several/ current customers, the CustomerID field should not be in
the SalesRep table. There should be a seperate table, called a
"joining" table. I can help you with that, if you need it. For the
moment, lets' go with what you currently have.

Forth, CustomerName /definitely should not/ be included in the
SalesReps table. That is a so-called "normalization" error. It will
cause you /loads/ of problems. You do not need it there. Your queries
can get it from the Customers table, via the CustomerID field. Again, a
single normalization error of this kind can stop the whole scheme
working.

Fifth, I wouldn't call the Permissions table that. It does not, in and
of itself, define any permissions. It just defines the SalesRepID for
each Access username. I think it would be better called, UserIDs, or
AccessUserSalesRepIDs, or somesuch. Let's ignore that for the moment.

So much for the table structures :-)

Relationships
-------------

Like the primary keys, you are also way too vague about the
relationships. You can't just say, "table A is related to table B". HOW
is it related (there are three different possible ways: 0:1, 1:0, and
1:1)? WHAT FIELD(S) is it related through? Here's my view of the
relationships, given the structure defined above:

SalesReps
SalesRepID (PK)
CustomerID < 1:1 relationship to Customers.CustomerID
NO(CustomerName)

Permissions
UserName (P..)
SalesRepID (..K) < 1:1 relationship to SalesReps.SalesRepID

Customers
CustomerID (PK)
CustomerName

Next
----

I was going to continue on, but I think that I should stop at this
point, for you to confirm that you have got the primary keys &
relationships correct and/or that I have properly understood them.

HTH,
TC

[TC]

PS. The impact of giving the Permissions table the composite primary
key, is that now, each User can have any # of records in that table,
ie. any # of SalesRepIDs. However, in fact, what I said before still
stands, and you could /still/ discard the SalesRep table & put the
relevant SalesRepID directly in the Permissions table! - /if/ you dfid
not need to store any other information /specific to each individual
SalesRep/.

But if you /do/ need to store other information specific to each
SalesRep - eg. their dates of birth - then, the SalesRep table is
required.

The more that I think about it, the more I suspect that a SalesRep
/can/ have more than one current customer. If that is correct, your
table structure is quite wrong & needs to be re-done.

You absolutely have to get these things right, before you even /think/
of writing any code, or queries.

Don't be disillusioned about it! I spent years in a corporate software
development environment, where professional software developers would
constantly try to write SQL without knowing the primary keys of the
tables involved. Then they wondered why after literally /weeks/ of
fulltime work, they couldn't get a single query to return the correct
data.

Cheers,
TC

[david@epsomdotcomdotau]

Another solution would be to use a User Defined Function (VBA)
for the query criteria. In the User Defined Function you could
read a value from a form, or supply a default value, or return Null,
or Prompt for a value, or raise an error or ....

select * from qry where idx = gfnGetIDXfromForm()

function gfnGetIDXfromForm as long

on error resume next
gfnGetIDXfromForm = 0
gfnGetIDXfromForm = forms("myform").txt_idx
exit function

(david)

"Tom Stoddard" <tsto...@andrewspaper.com> wrote in message
news:e9EiS93...@TK2MSFTNGP15.phx.gbl...

[Tom Stoddard]

"TC" <aatcbb...@yahoo.com> wrote in message

news:1128919668.8...@g14g2000cwa.googlegroups.com...

> Hi Tom
>
> Let's see if we can work through this :-)
>
>
> Table structures
> ----------------

TC,

You have misinterpreted my narrative. Let me try to define my tables in the
format you've suggested.

tblSalesReps
SalesRepID (PK)
SalesRepName

tblCustomers
CustomerID (PK)
CustomerName
SalesRepID < many:1 Relationship with tblSalesRep.SalesRepID

tblPermissions
UserName (P..)
SalesRepID (..K) < many:1 relationship with tblSalesRepID.SalesRepID

I hope that's clear. I appreciate you showing me how to express this in
text. Please let me know if I could improve on what I've put down here.

As I originally stated, both relationships listed above have referential
integrity in effect with cascading updates and deletes. This structure
allows me to arbitrarily assign permission to any user to view/edit any
number of customers based on which salesrep is assigned to the customer. The
relationship between tblCustomers and tblPermissions is many-to-many so they
are not joined directly in the relationship window but rather they are
joined indirectly through their relationships with tblSalesReps. This works
well for filtering the records in tblCustomers but the resulting recordsets
are not updateable. I keep thinking that there is some simple, obvious
solution to this but I don't see it.

Thanks again for all of your help!

[Tom Stoddard]

David,

That was my original thought but I was led to believe that using
user-defined functions in the query grid would result in inefficient
queries. Am I mistaken?

<david@epsomdotcomdotau> wrote in message
news:uQjzioYz...@TK2MSFTNGP15.phx.gbl...

[Joan Wild]

I think you need to provide more information. From your description of the
tables and this statement...

> The relationship between tblCustomers and
> tblPermissions is many-to-many so they are not joined directly in the
> relationship window but rather they are joined indirectly through
> their relationships with tblSalesReps

but you have not defined a many - to - many with the tblSalesReps as the
linking table. There is no relationship between customers and permissions.
Have a look at Northwind; specifically the many to many relationship between
Employees and Customers and you'll see the difference. If there really is a
relationship between the two, then you need a third table tblCustPerm with
CustomerID and Username to create the link to tblCustomers and
tblPermissions

tblCustomers (1-m) tblCustPerm
tblPermissions (1-m) tblCustPerm

What is the sql of your form's and subform's recordsources? What are you
trying to accomplish? I think you want users in the tblpermissions to be
able to delete records on this form and subform. Provide the sql and we'll
see.

--
Joan Wild
Microsoft Access MVP

[Tom Stoddard]

"Joan Wild" <jw...@nospamtyenet.com> wrote in message

news:OUItmCdz...@TK2MSFTNGP09.phx.gbl...

>I think you need to provide more information. From your description of the
>tables and this statement...
>
>> The relationship between tblCustomers and
>> tblPermissions is many-to-many so they are not joined directly in the
>> relationship window but rather they are joined indirectly through
>> their relationships with tblSalesReps
>
> but you have not defined a many - to - many with the tblSalesReps as the
> linking table. There is no relationship between customers and
> permissions. Have a look at Northwind; specifically the many to many
> relationship between Employees and Customers and you'll see the
> difference. If there really is a relationship between the two, then you
> need a third table tblCustPerm with CustomerID and Username to create the
> link to tblCustomers and tblPermissions
>

Joan, there is a 1-m relationship between Permissions and SalesReps and a
1-m relationship between SalesReps and Customers. It seems inefficient to me
to have the create a permissions table with the username and customerid in
it when I can determine which customers a user has permissions on by
filtering the SalesReps table which in turn filters the Customers table. The
following SQL will return all customers that "Tom" is given permission to
access but it's not updateable:

SELECT tblCustomers.*
FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON
tblSalesReps.SalesRepID = tblPermissions.SalesID
WHERE tblPermissions.UserName = "Tom"

> tblCustomers (1-m) tblCustPerm
> tblPermissions (1-m) tblCustPerm

My structure is actually this:

tblSalesReps (1-m) tblPermissions
tblSalesReps (1-m) tblCustomers

or

tblPermissions (m-1) tblSalesReps (1-m) tblCustomers

>
> What is the sql of your form's and subform's recordsources? What are you
> trying to accomplish? I think you want users in the tblpermissions to be
> able to delete records on this form and subform. Provide the sql and
> we'll see.
>

I won't get into the subforms yet because I'm sure you'll have some ideas
for me based on what I've already given you but suffice it to say that the
subforms are just extensions of what I've described above. Customers have
(1-m) relationships with other tables which inturn have (1-m) relationships
with other tables. It's all very orderly and normalized. A salesrep has
multiple customers. A customer has multiple price lists. The price lists
have multiple grades. Each grades have sub-grades.... If I want to delete a
grade from a price list I need to have permissions granted for the salesrep.
The challenge is that I want multiple users with permissions on multiple
salesreps. That's where the permissions table comes in. It's really the
table that's in between Users and SalesReps to facilitate the (m-m)
relationship that they have but Users isn't a table, it could be if I wanted
it to be but that wouldn't make a difference. I get a user name from the
currentuser() functions so I don't need a table to provide me with it.

Thanks Again!

[david epsom dot com dot au]

"Tom Stoddard" <tsto...@andrewspaper.com> wrote in message

news:%23MloCHZ...@TK2MSFTNGP15.phx.gbl...

> David,
>
> That was my original thought but I was led to believe that using
> user-defined functions in the query grid would result in inefficient
> queries. Am I mistaken?
>

No, not mistaken. However, a UDF as shown makes negligible
difference most of the time.

The UDF as shown is evaluated twice before the query runs,
then the value is passed to the query engine. This is not
as efficient as hardcoding a number (ie "3") into a predeclared
query, but not much different from any other parameter query,
including your first example.

The real problems come when you have a UDF that is (or
appears to be) dependent on a value from the record. A
common example is when you have an ODBC connection and a
UDF in the criteria that needs to be evaluated for every
record:
"where [idx] = gfnMatch([idx])"
which forces local evaluation of the criteria, increasing
network traffic and preventing use of indexes and
optimisations.

The worst examples are when you have multiple fields dependent
on a calculation, like "gfnCalc([item])". The UDF needs to be
evaluated at least once for each dependent field on each
record. -A real killer if the UDF is slow (a real killer
if the UDF includes database lookups).

(david)

[Joan Wild]

Tom Stoddard wrote:
> "Joan Wild" <jw...@nospamtyenet.com> wrote in message
> news:OUItmCdz...@TK2MSFTNGP09.phx.gbl...
>>
>

> Joan, there is a 1-m relationship between Permissions and SalesReps
> and a 1-m relationship between SalesReps and Customers. It seems

From your description that isn't the case. There is a m-1 between
Permissions and SalesReps
(one SalesReps can have many users)

You need to create a junction table between customers and permissions. Yes
this will be circular, but this is a circumstance where that is warranted.
If you do this, you'll find your query will be updateable.

[TC]

Tom Stoddard wrote:

> tblSalesReps
> SalesRepID (PK)
> SalesRepName

Looks good.

> tblCustomers
> CustomerID (PK)
> CustomerName
> SalesRepID < many:1 Relationship with tblSalesRep.SalesRepID

Looks good. Each customer has a single salesrep, but a single salesrep
can have many customers. Presumeable you have made SalesRepID a
non-required field in tblCustomers, so you can create a customer record
before you decide which salesrep is assigned to that customer, or you
can erase the SalesRepID value to indicate that the customer no longer
has a salesrep assigned.

> tblPermissions
> UserName (P..)
> SalesRepID (..K) < many:1 relationship with tblSalesRepID.SalesRepID

The composite primary key means that a single Access user can manage
the customers for /many diferent/ sales represenatives - not just one.
Is that what you want?

> This structure allows me to arbitrarily assign permission
> to any user to view/edit any number of customers based on
> which salesrep is assigned to the customer.

Seems right to me.

> The relationship between tblCustomers and tblPermissions is
> many-to-many so they are not joined directly in the relationship
> window but rather they are joined indirectly through their
> relationships with tblSalesReps.

This is correct /if/ tblPermissions retains the composite primary key;
ie. each Access user can manage the customers of many different sales
reps, not just one.

> This works well for filtering the records in tblCustomers but the resulting recordsets
> are not updateable.

Ok - now show us the SQL!

PS. I simply have not had time to read Joan's replies. So if anything
differs, it's probably just that we differ in our interpretation of
what you are saying.

Cheers,
TC

[TC]

I think I made that comment to you in regard to a previous post where
you proposed returning a "query criteria string" or somesuch from a
UDF? This implied a usage like the following:

SELECT ... FROM ... WHERE instr ( UDF(..), primarykey ) > 0

which is defintely not efficient.

HTH,
TC

[Tom Stoddard]

"Joan Wild" <jw...@nospamtyenet.com> wrote in message

news:O4GRB%23fzFH...@TK2MSFTNGP15.phx.gbl...

But where do I stop? If that's the case then I have to create junction
tables between tblPermissions and any other table I want to be able to edit.
For example, if I want an updateable query of price lists I now have to
create a junction table between Permissions and PriceLists. I could end up
with 6 or 7 junction tables which I'd then have to maintain. I'd have to
update all of those tables everytime I added a sales rep or user and
everytime I modified any permissions.

Right now I'm working around these restrictions by using parametized queries
and supplying the paremeters either in code or from forms based on queries
which are not updateable. I'm not sure which approach is worse but at least
the queries don't require me to store a whole bunch of redundant data.

Thanks Again!

[Tom Stoddard]

"TC" <aatcbb...@yahoo.com> wrote in message

news:1129003839....@f14g2000cwb.googlegroups.com...

Your interpretation is accurate. I do have a restriction on customers,
however, which requires them to have a salesrep assigned. This is a business
rule which my company follows so I thought it made sense to do the same.
Would that make a difference to the updateability of the following query?

The following SQL will return all customers that "Tom" is given permission
to access but it's not updateable:

SELECT tblCustomers.*
FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON

tblSalesReps.SalesRepID = blPermissions.SalesRepID WHERE
tblPermissions.UserName = "Tom"

I think I understand why this is so but I don't see an easy way around it.
Joan has suggested creating a "junction" table between Customers and
Permissions. I've tried that and it will work if all I want to edit are
records in the Customers table but if I want to edit records in table
further down the chain (for example, a customer's price list) then I'm right
back where I started.

I'm starting to realize that there is no easy solution here but thinking it
through so thoroughly has certainly helped me to understand it better and to
work around it more efficiently.

Thanks!

> Cheers,
> TC
>

[TC]

Tom Stoddard wrote:

> Your interpretation is accurate.

Ok, so we have the table structure correct :-)

> I do have a restriction on customers,
> however, which requires them to have a salesrep assigned. This is a business
> rule which my company follows so I thought it made sense to do the same.
> Would that make a difference to the updateability of the following query?

No, it shouldn't make any differnce to that. Just make the SalesRepID
field mandatory in the Customers table.

> The following SQL will return all customers that "Tom" is given permission
> to access but it's not updateable:
>
> SELECT tblCustomers.*
> FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
> tblCustomers.SalesRepID) INNER JOIN tblPermissions ON
> tblSalesReps.SalesRepID = blPermissions.SalesRepID WHERE
> tblPermissions.UserName = "Tom"

You are only selecting the fields from one of the tables involved.
IIRC, you may need to select /all/ the relevant fields before the query
is updatable. Change "SELECT tblCustomers.*", to "SELECT *", and see if
that makes it updatable.

> I'm starting to realize that there is no easy solution here but thinking it
> through so thoroughly has certainly helped me to understand it better and to
> work around it more efficiently.

Undoubtedly a very common error in Access databases is, an improperly
designed database structure. You have to get the tables, primary keys,
foreign keys, and relationships, all 100% correct, before it will all
work properly.

Tell me if the change suggested above, makes your query updatable. If
it doesn't, tell me whether you'd like me to replicate your structure &
query at home & try it myself. I'm willing to do that /if/ you still
want to get it working.

Cheers,
TC

[Tom Stoddard]

>
> You are only selecting the fields from one of the tables involved.
> IIRC, you may need to select /all/ the relevant fields before the query
> is updatable. Change "SELECT tblCustomers.*", to "SELECT *", and see if
> that makes it updatable.

I tried this and still had no success. I just discovered, however, that if I
change the query's recordset type property to Dynaset (inconsistent updates)
that I can edit these fields. I don't recall ever using this setting. Has it
always been available in Access? Is there a good reason not to use it? If
not, it might be my "easy solution". I can't believe I hadn't tried it
before. I guess the term "inconsistent" scared me away. From what I can find
online about it, it sort of bypasses some of the restrictions that
referential integrity enforces. I believe I can use it safely in my
application.

>
>
>> I'm starting to realize that there is no easy solution here but thinking
>> it
>> through so thoroughly has certainly helped me to understand it better and
>> to
>> work around it more efficiently.
>
> Undoubtedly a very common error in Access databases is, an improperly
> designed database structure. You have to get the tables, primary keys,
> foreign keys, and relationships, all 100% correct, before it will all
> work properly.
>
> Tell me if the change suggested above, makes your query updatable. If
> it doesn't, tell me whether you'd like me to replicate your structure &
> query at home & try it myself. I'm willing to do that /if/ you still
> want to get it working.

I appreciate the help you're offering but I don't want you to spend too much
time on it. The dialog we're having in this newsgroup is extremely helpful
to me.

Thanks!

>
> Cheers,
> TC
>

[Joan Wild]

Tom Stoddard wrote:
> But where do I stop? If that's the case then I have to create junction
> tables between tblPermissions and any other table I want to be able
> to edit. For example, if I want an updateable query of price lists I
> now have to create a junction table between Permissions and
> PriceLists. I could end up with 6 or 7 junction tables which I'd then
> have to maintain.

That is not true. Your price lists are related to your Customers which in
turn is related to the tblPermissions.

I'm bowing out of this, as TC and I are working at cross-purposes.

[TC]

'Ello, 'ello! I thought you were trying the updatability by opening
the query directly & typing into the datasheet?

A whole lot of other factors come in, if you are opening a recordset.

I'm sure that in principle, your query should be updatable. I don't
have Access on this PC, but I'll copy the details to diskette now, &
see if I can try it myself, sometime in the next few days.

Cheers,
TC

[TC]

Sorry, didn't mean to confuse the issue! Tom says he is learning from
all the replies, so neither of us are wasting our time here IMHO :-)

TC

[Joan Wild]

Don't apologize. You're not confusing the issue. It's just that there are
three very different sub-threads here, and I think Tom needs to work on one
thing at a time, since they are all related - get the design right first,
and other things should fall into place.

--
Joan Wild
Microsoft Access MVP

[Tom Stoddard]

"TC" <aatcbb...@yahoo.com> wrote in message

news:1129094075.6...@g49g2000cwa.googlegroups.com...

> 'Ello, 'ello! I thought you were trying the updatability by opening
> the query directly & typing into the datasheet?
>

I have been trying to do it directly in the datasheet and it's not
updateable. I'm using the term recordset generically. The records that are
displayed in the datasheet are a recordset which is returned by Jet based on
the SQL statement the Access generates based on what is entered in the query
grid. A recordset is a recordset is a recordset even if its displayed in a
datasheet. That's why, when you go into the property page of the query, one
of the properties is the "recordset type" property.

I seem to be having trouble describing a fairly simple scenario. Let me try
once again.

tblPermissions
UserName (P..)
SalesRepID (..K) < (M-1) relationship with tblSalesReps

tblCustomers
CustomerID (PK)
SalesRepID < (M-1) relationship with tblSalesReps
CustomerName

tblSalesReps
SalesRepID (PK) < (1-M) relationship with both tblPermissions and
tblCustomers
SalesRepName

My SQL statement copied directly out of the Access query window (except for
the note in parenthesis):

SELECT tblCustomers.* (I've tried many combinations here but none of them
made a difference)

FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON

tblSalesReps.SalesRepID = tblPermissions.SalesRepID
WHERE (((tblPermissions.UserName) = CurrentUser()));

By default, Access sets the 'recordset type' property of the query to
Dynaset. As is, the resulting records when displayed in the datasheet or in
a form are not updateable.

Originally I thought I could leave tblSalesReps out of the query altogether
like this:

SELECT tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblPermissions ON tblCustomers.SalesRepID =
tblPermissions.SalesID
WHERE (((tblPermissions.UserName)=CurrentUser()))

I added the SalesReps table (as a junction table) to the query thinking that
because there are relationships defined in the relationship window of the
database for those tables that it would result in an updateable query. It
didn't work. Basically, the relationships between these tables looks like
this:

tblPermissions (M----->1) tblSalesReps (1<-----M) tblCustomers

I've read what I could find in the Access help files and this behavior is by
design. This query should not be updateable unless I change the recordset
type property to 'Dynaset (inconsistent updates)'. My permission scheme is
to limit access to records based on their relationship to sales reps so I
believe that I've structured my tables and relationships properly. I can
filter records very easily. My only challenge is that I need to find a way
to edit the resulting records. Inconsistent Updates will allow me to do
that. Now I'm looking for 2 pieces of advice.

1. Is my security scheme flawed?
2. Is there anything I need to know anything about using 'Dynaset
(inconsistent updates)'? I understand that I have to careful about breaking
referential integrity when editing those records but is there some
unexpected 'gotcha' I should be looking out for? The reason I concerned is
that nobody suggested this before. Why not?

Thanks again, and yes, I'm also interested in what Joan has to say.

[TC]

I know what the problem is. I got it going at home earlier tonight. But
I am just back on for a moment, after a dinner with friends trying to
submit a crazy idea to Mythbusters! So I will get back on tomorrow &
give you a link to my example db.

Cheers,
TC

[TC]

Ok, there is bad news, & good news!

The bad news is, the query as currently written, is not updatable. This
is clearly due to the many-to-many join, which I should have seen
immediately.

The good news is, it only needs a simple change to the query. The table
structure, primary keys & relationships need not be changed.

Here is what you had before:

SELECT *

FROM (tblSalesReps INNER JOIN tblCustomers
ON tblSalesReps.SalesRepID = tblCustomers.SalesRepID)
INNER JOIN tblPermissions
ON tblSalesReps.SalesRepID = tblPermissions.SalesRepID
WHERE (((tblPermissions.UserName) = CurrentUser()));

Here is what to change it to. (From memory, cos I forgot my floppy
disk!)

SELECT *
FROM ( tblSalesReps AS reps INNER JOIN tblCustomers AS custs
ON custs.SalesRepID = reps.SalesRepID)
WHERE EXISTS
( SELECT null
FROM tblPermissions AS perms
WHERE perms.username = currentuser()
AND perms.salesrepid = reps.salesrepid )

Note the use of alias names (AS ...) to make it look simpler.

Yes? No?

HTH,
TC

[Tom Stoddard]

> SELECT *
> FROM ( tblSalesReps AS reps INNER JOIN tblCustomers AS custs
> ON custs.SalesRepID = reps.SalesRepID)
> WHERE EXISTS
> ( SELECT null
> FROM tblPermissions AS perms
> WHERE perms.username = currentuser()
> AND perms.salesrepid = reps.salesrepid )
>
> Note the use of alias names (AS ...) to make it look simpler.

Brilliant! Why does that work?

Let me guess. Since the permissions table is not actually attached to the
Customers table in the main query there is no reason not to allow updates.

I wouldn't have thought to select "null". Is there an advantage to doing
that instead of selecting one of the fields in perms or even *?

Thanks again for all of your help. This has been a long thread and has
gotten a little off of the security topic but for good reason. For those who
might take the time to read all of this and for my own closure, I'd like to
summarize.

My real world scenario:

I work for a company which has about 600 customers. Each customer is
assigned to 1 of 9 sales representatives. I have a database which enables
users to create and maintain price lists for many of those customers, among
other things. The users include sales reps, customer service reps,
purchasing personnel and managers. Customer service reps access the database
in order to look up pricing for customers and some of them are given
permission to maintain some of the sales reps price lists (some being the
important term). Sales reps should only be allowed to edit price lists for
their own customers, and managers should be able to edit all sales reps
price lists. This scenario requires me to assign permissions in an arbitrary
manner.

My solution:

I took update, insert and delete permission to the tables related to the
customers and their price lists away from all users and have allowed them to
edit the data only through queries that are 'Run With Owner Permissions'. I
created a permissions table which stores unique combinations of usernames
and salesrep numbers. In order to determine which records a user is able to
access, I filter the data using the permissions table which checks to see if
a price list is related to a customer who is assigned to a salesrep that the
current user has permission to edit. This solution provides me with a level
of security that the company is comfortable with.

Challenge:

In the process of implementing this scheme, I created some RWOP queries with
the permissions table attached so that I could filter the data as desired.
The result of the query was a recordset which was not updateable. This is
not the result I wanted.

Solution:

TC offered a solution which is accomplished through a design change to my
queries. His solution uses the "Exists" statement in the where clause of the
query (The sql statement is included at the top of this message). The query
checks for the existence of records in the permissions table which match the
salesrepID in the salesreps table of the main select statement. In doing so,
the query does not attach the permissions table directly to any of the
tables from which output fields are being selected. The result is a
recordset which is updateable.

Hats off to TC!

[TC]

Tom Stoddard wrote:

> Brilliant! Why does that work?

The original query had a many-to-many relationship between two of the
tables. Many-to-many queries are never updatable. There are some
logical reasons for this; a bit too much to discuss here. I removed one
table, to leave an oridinary updatable one-to-many query. I chose to
remove the Permissions table, because I could easily see how to regain
the required functionality by adding a "subquery" (the second SELECT
clause) on that table.

> Let me guess. Since the permissions table is not actually attached to the
> Customers table in the main query there is no reason not to allow updates.

No, it is really just the issue of m:m versus 1:m.

> I wouldn't have thought to select "null". Is there an advantage to doing
> that instead of selecting one of the fields in perms or even *?

This is a favourite hobby horse of mine, though I haven't actually
ridden it for some years now! The EXISTS operator is interested in one
thing, and one thing only: does the subquery return any /rows/? If it
does, the EXISTS is True; if it doesn't, the EXISTS is False. It does
not care about the /columns/ in the rows that are (or aren't) returned.

So, the subquery could say SELECT UserName, SELECT "hello world",
SELECT *, SELECT 123 & SalesRepID, or whatever else you wanted it to.
This would have no impact whatever on how the EXIST worked. Therefore,
as a matter of principle, I select "the least" thing that I can: NULL.

The other advantage of using NULL is that it verifies whether the
reader understands the EXISTS operator! If someone asks "why NULL?",
this suggests that they don't understand that the /columns/ selected by
the subquery, are irrelevant. I used this trick in my previous life as
a software project leader. Whene-ever someone asked "why NULL?", I knew
to give them my little EXISTS lecture!

> Thanks again for all of your help. This has been a long thread and has
> gotten a little off of the security topic but for good reason.

Thanks to you for staying with it! I've enjoyed it a lot, because we
eventually got a result. Too often, people get 2/3rds of the way to a
solution, then drop off the radar. I find that very frustrating, which
is why I generally stopped answering database design questions in other
newsgroups.

> For those who might take the time to read all of this and for my own closure, I'd like to summarize.

And well done for that too. I follow some other technical newsgroups
where I am not an expert in the topics discussed. Sometimes, lots of
people post to a topic, but no-one posts a summary, so I'm not sure
what they all concluded!

Cheers & well done,
TC