davidsanderson
asked on
With Owneraccess Option Not Working In Code
i have a testuser that does not have permissions to edit tblMailMergePrintQueue.
i want the table to be appended though regardless of permissions and i want to do this through code, so i do this:
DoCmd.RunSQL ("INSERT INTO tblMailMergePrintQueue (QueueDate, QueuedBy, JobNum, MailMergeDocumentID)" & _
" SELECT Date() As QueueDate, CurrentUser() as QueuedBy, '" & Me.cboJobID.Column(1) & "' as JobNum, 37 as MailMergeDocumentID WITH OWNERACCESS OPTION;")
notice the "WITH OWNERACCESS OPTION". when testuser tries to run this, he gets the much dreaded "Operation must use an updateable query" error.
if i move the exact SQL statement in a query object and then i call it through code like this:
DoCmd.OpenQuery "qappMailMergeDocumentID37 "
everything works fine this way.
is there something i'm missing here? does WITH OWNERACCESS OPTION not work in code? i cant find any KB article on this.
i want the table to be appended though regardless of permissions and i want to do this through code, so i do this:
DoCmd.RunSQL ("INSERT INTO tblMailMergePrintQueue (QueueDate, QueuedBy, JobNum, MailMergeDocumentID)" & _
" SELECT Date() As QueueDate, CurrentUser() as QueuedBy, '" & Me.cboJobID.Column(1) & "' as JobNum, 37 as MailMergeDocumentID WITH OWNERACCESS OPTION;")
notice the "WITH OWNERACCESS OPTION". when testuser tries to run this, he gets the much dreaded "Operation must use an updateable query" error.
if i move the exact SQL statement in a query object and then i call it through code like this:
DoCmd.OpenQuery "qappMailMergeDocumentID37
everything works fine this way.
is there something i'm missing here? does WITH OWNERACCESS OPTION not work in code? i cant find any KB article on this.
I know that the WITH OWNERACCESS OPTION will not work when you use a SQL statement as the recordsource for a form or report instead of an existing query name. I suspect that through code it will not work either as it needs to get the permissions for that existing query object, whereas just a SQL statement really has no owner.
ASKER
can you think of any other way i could do it besides using a query object?
Here is some info from microsoft
SQL statements used in a RunSQL macro or as the RowSource property for a control
are not real objects; instead, they are compiled and built when they are run.
Because they are not real objects in the Database window, they do not have an
owner, and the "Run With Owner's Permissions" property is ignored when they are
run. If you do not have permissions for the underlying table, you receive the
error message
SQL statements used in a RunSQL macro or as the RowSource property for a control
are not real objects; instead, they are compiled and built when they are run.
Because they are not real objects in the Database window, they do not have an
owner, and the "Run With Owner's Permissions" property is ignored when they are
run. If you do not have permissions for the underlying table, you receive the
error message
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i guess i'm stuck with it.