Permissions on SQL

[Bonnie]

Hi there! Using A02 on XP. Not a programmer. I'm getting a permissions error
(#3033 - You don't have the nec permissions to use the 'qCensus1EditsCopy'
object. Have your syst admin or the person who created this obj estab the
approp perm for you.) I have given full admin rights to my usergroup
'RPSAdm' and I still get the permissions error. Works fine for me but no one
else. Here is my code behind the button that won't work for them:

Private Sub Command56_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID AS
RevID, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.SSN
AS RevSSN, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.FName
AS RevFName, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.LName
AS RevLName, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Gender AS RevGender, "
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOB, ""mm/dd/yyyy"") AS RevBirth,"
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOH, ""mm/dd/yyyy"") AS RevHire,"
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.Hours
AS RevHours, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.Comp
AS RevComp, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DefAmt AS RevDefAmt, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.ExclComp AS RevExclComp, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Sec125 AS RevSec125, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.StatusCode AS RevStatusCode, "
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.StatusDate, ""mm/dd/yyyy"") AS RevStatusDate "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL
'Debug.Print strSQL

'the name of your query goes in here
Set qryTest = dbsCurrent.QueryDefs("qCensus1EditsRev")

qryTest.SQL = strSQL ******This line is highlighted by the debugger.
'Debug.Print strSQL
'other code

Set dbsCurrent = Nothing
Set qryTest = Nothing
'--------------------------------

'Uncomment the MsgBox line or add a Debug.Print strSQL line.
'Debug.Print strSQL
'It should give you the correct SQL for the value selected in the
"RunThisOne" combo box.

'MsgBox "Done."

Dim stDocName As String

stDocName = "rCensus1Compare"
DoCmd.OpenReport stDocName, acPreview

End Sub

NOTE: [RunThisOne] is a field on my form with the selected contract number.
It runs code that finds the table named like the field and creates the query
and opens a report.

I'm hoping someone will look at this and see some little thing I need to
fix. Thanks in advance for your time and advice.

 

[Jon Ley]

Sounds like you havent quite given your users permission to modify the design
of the query qCensus1EditsRev (OK, so I'm stating the obvious here!). While
logged on as a user that does have full permissions on this query, select the
query in the database window and then go to the Tools menu, select Security,
and then select User and Group Permissions. Select the user or group that
needs this permission and make sure the Modify Design box is ticked. If you
still can't get this to work, you could always try the following...

Delete the query. Modify your code (the following is untested 'air code'):

Private Sub Command56_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "
'... build up the select statement as before

Set qryTest = dbsCurrent.CreateQueryDef("qCensus1EditsRev", strSQL)
stDocName = "rCensus1Compare"
DoCmd.OpenReport stDocName, acPreview
'Wait for the user to close the report...
Do
DoEvents
Loop Until SysCmd(acSysCmdGetObjectState, acReport, stDocName) = 0
'...then delete the query
dbsCurrent.QueryDefs.Delete "qCensus1EditsRev"

Set qryTest = Nothing
Set dbsCurrent = Nothing

End Sub


The user will always be able to create the query (anyone can create a new
query), and because the query is deleted at the end of the process, the next
user should have no problem creating it again.

The only downside of this approach is that it is now not straightforward to
make design changes to your report. You could have a backup copy of the query
that you rename while your are designing the report and then rename back
again once the report is OK.

Good luck,

Jon.

 

[Bonnie]

Jon,

You are a GENIUS! Works GREAT! Don't know why the permissions are goofy. I
even gave that group ALL admin rights for the entire DB and still got the
error on anyone but me. Now, with the create/delete, it works for everyone!

Thanks for being part of the newsgroups!