Problem with MSysTable object permission

[isetea]

Hi,
who can tell me what this error message means and what I have to do to solve the issue?

"You do not have the necessary permission to use the 'MSysTables' object. Have your system administrator or the person who created this object establish the appropriate permissions for you."

I'm the administrator and this happens with on of the users logon. I gave this user 'Read', 'Read Design' and 'Modify Design' access to that query, because the user needs to set a criteria.

Quick advice is much appreciated.

Thanks.

[PEB]

In fact this is system table! Is need to access just this table? This can be wormfull for your database!

[isetea]

I'm not sure, if I get your point. But I gave the group (not the single user, sorry I forgot to say) access to the tables underlying the specific query as well. The group has now read, read design, modify and update access to all data what is linked to the query in any way. It didn't work out...

In fact this is system table! Is need to access just this table? This can be wormfull for your database!

[MMcCarthy]

What is the query?

I'm not sure, if I get your point. But I gave the group (not the single user, sorry I forgot to say) access to the tables underlying the specific query as well. The group has now read, read design, modify and update access to all data what is linked to the query in any way. It didn't work out...

[isetea]

Private Sub Open1_Click()

On Error GoTo Err_macro1_Click

Dim db As DAO.Database
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim sFilter As String
Dim SFilter2 As String

Set db = CurrentDb()

sFilter = MultiSelectSQL(cboDept)
sFilter2 = MultiSelectSQL(cboStat)

' Close Temp Query
DoCmd.Close acQuery, "qry_011_Select_Agent_Numbers_National"

' Delete Temp Query
DoCmd.DeleteObject acQuery, "qry_011_Select_Agent_Numbers_National"

' Construct SQL statement
strSQL = "SELECT [tbl_004_Agent Numbers].Name, [tbl_004_Agent Numbers].[Staff No]," & _
"[qry_003_Subquery_Select_Staff_Details].Title, [qry_002_Subquery_Select_Dpeartment_Details].[Dept Name]," & _
"[qry_003_Subquery_Select_Staff_Details].[Employment Status New]," & _
"[tbl_004_Agent Numbers].AFSL, [tbl_004_Agent Numbers].[Individual ID]," & _
"[tbl_004_Agent Numbers].[CAPS ID], [tbl_004_Agent Numbers].[Primary CAPS]," & _
"[tbl_004_Agent Numbers].[CFS ID], [tbl_004_Agent Numbers].[CBA Short Name]," & _
"FROM ([qry_002_Subquery_Select_Dpeartment_Details] INNER JOIN [qry_003_Subquery_Select_Staff_Details] " & _
"ON [qry_002_Subquery_Select_Dpeartment_Details].OUN = [qry_003_Subquery_Select_Staff_Details].OUN)" & _
"INNER JOIN [tbl_004_Agent Numbers] " & _
"ON [qry_003_Subquery_Select_Staff_Details].[Staff No] = [tbl_004_Agent Numbers].[Staff No]" & _
"WHERE ([tbl_004_Agent Numbers].[Staff No] > 100 and " & _
"[qry_002_Subquery_Select_Dpeartment_Details].[Dept Name] " & sFilter & " and " & _
"qry_003_Subquery_Select_Staff_Details.[Employment Status New] " & SFilter2 & ")" & _
" ORDER BY [tbl_004_Agent Numbers].[Staff No];"

Debug.Print strSQL
' Create query and provide it with its SQL statement

Set qdf = db.CreateQueryDef("qry_011_Select_Agent_Numbers_Na tional", strSQL)
' Open query for user

DoCmd.OpenQuery "qry_011_Select_Agent_Numbers_National"
Open1_Click_Click:

Exit Sub

Err_macro1_Click:
MsgBox Err.Description
Resume Open1_Click_Click

End Sub

[MMcCarthy]

Firstly, you create the query but you never delete it.


also there were some errors in your query. I've simplified it and corrected what I've found. Try it and see if it makes a difference.

strSQL = "SELECT T4.Name, T4.[Staff No], Q3.Title, Q2.[Dept Name]," & _
"Q3.[Employment Status New], T4.AFSL, T4.[Individual ID]," & _
"T4.[CAPS ID], T4.[Primary CAPS], T4.[CFS ID], T4.[CBA Short Name]," & _
"FROM ([qry_002_Subquery_Select_Dpeartment_Details] Q2 " & _
"INNER JOIN [qry_003_Subquery_Select_Staff_Details] Q3 " & _
"ON Q2.OUN = Q3.OUN) INNER JOIN [tbl_004_Agent Numbers] T4 " & _
"ON Q3.[Staff No] = T4.[Staff No] " & _
"WHERE ((T4.[Staff No] > 100) And (Q2.[Dept Name]='" & sFilter & "') " & _
"AND (Q3.[Employment Status New]='" & SFilter2 & "')) " & _
"ORDER BY T4.[Staff No];"

[isetea]

Hi

I thought I delete the query very in the beginning with:

' Delete Temp Query
DoCmd.DeleteObject acQuery, "qry_011_Select_Agent_Numbers_National"

And the query is working as expected at the moment. I also don't have a problem with my query, but with the user-group permission to run the query through a form. Running the query while selecting the single query without the form the permission works perfectly.

I also don't know what you modification means. Does it is a real change in the query logic? What does it correct exactly? Maybe you can help me to see the amendment...

Thanks

Firstly, you create the query but you never delete it.


also there were some errors in your query. I've simplified it and corrected what I've found. Try it and see if it makes a difference.

strSQL = "SELECT T4.Name, T4.[Staff No], Q3.Title, Q2.[Dept Name]," & _
"Q3.[Employment Status New], T4.AFSL, T4.[Individual ID]," & _
"T4.[CAPS ID], T4.[Primary CAPS], T4.[CFS ID], T4.[CBA Short Name]," & _
"FROM ([qry_002_Subquery_Select_Dpeartment_Details] Q2 " & _
"INNER JOIN [qry_003_Subquery_Select_Staff_Details] Q3 " & _
"ON Q2.OUN = Q3.OUN) INNER JOIN [tbl_004_Agent Numbers] T4 " & _
"ON Q3.[Staff No] = T4.[Staff No] " & _
"WHERE ((T4.[Staff No] > 100) And (Q2.[Dept Name]='" & sFilter & "') " & _
"AND (Q3.[Employment Status New]='" & SFilter2 & "')) " & _
"ORDER BY T4.[Staff No];"

[MMcCarthy]

Firstly there were spacing problems.
Secondly you had no '=' signs in your where statement and your parameters weren't correctly identified as strings. Running a query through vba has different syntax.

Have you tried running with this amended query?

[isetea]

The code you gave me I firstly had, but it was not working with the result. That's why I have no "=".
Now entering the one again the following error pops up:

Syntax Error (missing operator) in query expression '((T4.[Staff No] > 100) And (Q2.[Dept Name]=' = 'Spec Dist - NSW North") AND (Q3.[Employment Status]=' = ' Current"))'.

Due to my previous experience it happens becauseof the "=" sign. I was trying so long with the Immediate result window till the outcome was equal to what a query sql would look like and so I resulted my code.

Is your code working for you?

Firstly there were spacing problems.
Secondly you had no '=' signs in your where statement and your parameters weren't correctly identified as strings. Running a query through vba has different syntax.

Have you tried running with this amended query?

[isetea]

You probably need to know the Multiselect function I have for sFilter to understand Why the VBA was in that way I had.

Public Function MultiSelectSQL(ctl As Control, Optional Delimiter As String) As String

Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1: sResult = " = '" & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter & "'"
Case Else
sResult = " in ("For Each vItem In .ItemsSelected sResult = sResult & Delimiter & "'" & .ItemData(vItem) & Delimiter & "'" & ","
Next vItem Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

[MMcCarthy]

Can you post the original query and I can check the syntax against it.

As you have obviously put the operators and single quotes into variables.

change the where statement back to the following leaving everything else as I gave it to you:

"WHERE ((T4.[Staff No] > 100) and " & _
"(Q2.[Dept Name]" & sFilter & ") and " & _
"(Q3.[Employment Status New] " & SFilter2 & ")) " & _
"ORDER BY T4.[Staff No];"

[isetea]

This is the temp query sql from a couple of days ago.

SELECT [tbl_004_Agent Numbers].Name,
[tbl_004_Agent Numbers].[Staff No], [qry_003_Subquery_Select_Staff_Details].Title, [qry_002_Subquery_Select_Dpeartment_Details].[Dept Name], [qry_003_Subquery_Select_Staff_Details].[Employment Status New], [tbl_004_Agent Numbers].AFSL,
[tbl_004_Agent Numbers].[Individual ID],
[tbl_004_Agent Numbers].[CAPS ID],
[tbl_004_Agent Numbers].[Primary CAPS],
[tbl_004_Agent Numbers].[CFS ID],
[tbl_004_Agent Numbers].[CBA Short Name]
FROM (qry_002_Subquery_Select_Dpeartment_Details INNER JOIN qry_003_Subquery_Select_Staff_Details
ON [qry_002_Subquery_Select_Dpeartment_Details].OUN = [qry_003_Subquery_Select_Staff_Details].OUN)
INNER JOIN [tbl_004_Agent Numbers]
ON [qry_003_Subquery_Select_Staff_Details].[Staff No] = [tbl_004_Agent Numbers].[Staff No]
WHERE ([tbl_004_Agent Numbers].[Staff No] > 100 and [qry_002_Subquery_Select_Dpeartment_Details].[Dept Name] in ('Spec Dist - NSW North','Spec Dist - NSW South') and qry_003_Subquery_Select_Staff_Details.[Employment Status New] in ('Current','Exited'))
ORDER BY [tbl_004_Agent Numbers].[Staff No];


I changed the WHERE clause as you told me and it is now working. I will check it with another user reg. the permission problem.

[isetea]

:( Still no change for my security permission problem :(