Open object without database window

[Guest]

I have a fairly simple db with about 4 active users. I am using basic
security with the database window hidden forcing users to navigate using
forms/buttons. I would like to allow them to create their own simple
queries. However if they do so, they will be unable to retrieve them without
opening the db window. Is there a way around this?

 

[Graham Mandeno]

Hi Lisa

It depends on how restrictive you want to be.

If you want to allow complete access to the query designer, then you could
have a table of "user queries". Allow the user to open a form bound to this
table. Adding a new record would involve creating a new query
(CreateQueryDef) and then opening it in design view. Thereafter, the query
could be edited or viewed from buttons on your form.

OTOH, if you want to restrict queries only to one or two tables (or other
queries) or to certain fields then you will need to design a UI for your
user to select fields and specify filters and sort orders etc. This can be
a pretty major undertaking, but might be necessary for "dangerous power
users".

 

[George Nicholson]

You can always provide users with a single-select listbox that lists the
queries in the db (or, maybe just queries that don't have a specific "this
is crucial to the app and I don't want you messing with it" prefix). Then
you'd have to write the "open selected query" code, etc.

 

[Guest]

How would I set that up? I would like to list all queries that start with a
given prefix. Thanks in advance for your help.

Lisa.

 

[George Nicholson]

How would I set that up? I would like to list all queries that start with

a
given prefix. Thanks in advance for your help.

Here are two approaches:

1) a simple query that provides just query names
(This has no prefix criteria, but that's simple enough to add):
SELECT Name, Type
FROM MSysObjects
WHERE ((MSysObjects.Type)=5);

2) this approach will fill a listbox with query Names *and* the Descriptions
that appear in the database window:
(Excluded: queries starting with "~" and "q_", as well as those designated
as system objects.)

Public Sub FillQueryList(lbo As ListBox)
' Fill listbox with list of queries AND their descriptions
' Modified from Access Developers Handbook (I think)
On Error GoTo ErrHandler

Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim strList As String
Dim strDescription As String
Dim fSystemObj As Boolean
Dim fShowSystem As Boolean

fShowSystem = Application.GetOption("Show System Objects")

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
'Check and see if this is a system object
fSystemObj = IsSystemObject(acQuery, qdf.Name)
' Skip query if its a system object
If fSystemObj Eqv False Then
strDescription = " "
' skip certain other queries
Select Case Left$(qdf.Name, 1)
Case "~"
' Don't include
Case "q"
If Mid$(qdf.Name, 2, 1) <> "_" Then
strList = strList & qdf.Name & "; "
On Error Resume Next
strDescription = qdf.Properties("Description")
On Error GoTo ErrHandler
strList = strList & strDescription & "; "
End If
Case Else
strList = strList & qdf.Name & "; "
On Error Resume Next
strDescription = qdf.Properties("Description")
On Error GoTo ErrHandler
strList = strList & strDescription & "; "
End Select
End If
Next qdf
lbo.RowSourceType = "Value List"
lbo.RowSource = strList
ExitHere:
Set qdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
' (not included) Call ErrorLog(mDocName & ": FillQueryList")
Resume ExitHere
End Sub

Private Function IsSystemObject(intType As Integer, ByVal strName As String,
_
Optional ByVal varAttribs As Variant) As Boolean
On Error GoTo ErrHandler
' Determine whether or not the object named 'strName' is
' an Access system object or not.
' Returns TRUE/FALSE

If IsMissing(varAttribs) Eqv True Then
varAttribs = 0
End If

If ((Left$(strName, 4) = "USys") Or (Left$(strName, 4) = "~sq_")) Eqv
True Then
IsSystemObject = True
Else
IsSystemObject = ((intType = acTable) And _
((varAttribs And dbSystemObject) <> 0))
End If
ExitHere:
Exit Function
ErrHandler:
'Call ErrorLog(mDocName & "/" & CurrentObjectName & ": IsSystemObject")
Resume ExitHere
End Function

HTH,

 

[Guest]

Thanks for all your help.

Here are two approaches:

1) a simple query that provides just query names
(This has no prefix criteria, but that's simple enough to add):
SELECT Name, Type
FROM MSysObjects
WHERE ((MSysObjects.Type)=5);

2) this approach will fill a listbox with query Names *and* the Descriptions
that appear in the database window:
(Excluded: queries starting with "~" and "q_", as well as those designated
as system objects.)

Public Sub FillQueryList(lbo As ListBox)
' Fill listbox with list of queries AND their descriptions
' Modified from Access Developers Handbook (I think)
On Error GoTo ErrHandler

Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim strList As String
Dim strDescription As String
Dim fSystemObj As Boolean
Dim fShowSystem As Boolean

fShowSystem = Application.GetOption("Show System Objects")

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
'Check and see if this is a system object
fSystemObj = IsSystemObject(acQuery, qdf.Name)
' Skip query if its a system object
If fSystemObj Eqv False Then
strDescription = " "
' skip certain other queries
Select Case Left$(qdf.Name, 1)
Case "~"
' Don't include
Case "q"
If Mid$(qdf.Name, 2, 1) <> "_" Then
strList = strList & qdf.Name & "; "
On Error Resume Next
strDescription = qdf.Properties("Description")
On Error GoTo ErrHandler
strList = strList & strDescription & "; "
End If
Case Else
strList = strList & qdf.Name & "; "
On Error Resume Next
strDescription = qdf.Properties("Description")
On Error GoTo ErrHandler
strList = strList & strDescription & "; "
End Select
End If
Next qdf
lbo.RowSourceType = "Value List"
lbo.RowSource = strList
ExitHere:
Set qdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
' (not included) Call ErrorLog(mDocName & ": FillQueryList")
Resume ExitHere
End Sub

Private Function IsSystemObject(intType As Integer, ByVal strName As String,
_
Optional ByVal varAttribs As Variant) As Boolean
On Error GoTo ErrHandler
' Determine whether or not the object named 'strName' is
' an Access system object or not.
' Returns TRUE/FALSE

If IsMissing(varAttribs) Eqv True Then
varAttribs = 0
End If

If ((Left$(strName, 4) = "USys") Or (Left$(strName, 4) = "~sq_")) Eqv
True Then
IsSystemObject = True
Else
IsSystemObject = ((intType = acTable) And _
((varAttribs And dbSystemObject) <> 0))
End If
ExitHere:
Exit Function
ErrHandler:
'Call ErrorLog(mDocName & "/" & CurrentObjectName & ": IsSystemObject")
Resume ExitHere
End Function

HTH,