Stop user saving query

[nq]

I have a database with many predefined queries/reports. These are sufficient for all users except one. This user would like to create temporary queries on an ad hoc basis. I can add a command button to invoke the query command:

DoCmd.RunCommand acCmdNewObjectQuery

However, this will allow the user to save the query when exiting. Eventually this will cause database "bloat". Is there a way of preventing the user saving the query or automatically deleting it afterwards?

 

[KenReay]

HI

Not sure it will fix your problem, but a suggestion:

Instead of allowing the user to create a new query, you could have a single "scratch" query definition, allow the user to create an SQL string for their required query, and save that string in the "scratch" queries .SQL property. That way, although they are "saving" the query, they are not producing "n" query definitions.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nq]

Ken.
A good approach but how do I open the scratch query?
I can create a blank query and save it but I can't seem to find a DoCmd.RunCommand option to open the query. Any ideas?

 

[nq]

Ken.

The code below solves the problem. The qryUserDefined query must have one table and one field in it although the user may modify everything. It would be nice to have a more elegant solution.

Dim stDocName As String
Dim stTempName As String

stDocName = "qryUserDefined"
stTempName = "qryUserTemp"

On Error Resume Next
DoCmd.DeleteObject acQuery, stTempName
DoCmd.CopyObject , stTempName, acQuery, stDocName
DoCmd.OpenQuery stTempName, acNormal, acEdit


Thanks for your input.

 

[nq]

Further update.
The above code works but............
In the db startup options, I have disbled "Allow Full Menus" to minimise user damage. Unfortunately this stops my advanced user from adding any tables to the temporary query.
Back to the drawing board.

 

[KenReay]

Hi

I am not sure of your level of expertise, or exactly which part of the problem you are having difficulty with, but my assumption was that you would manipulate (an existing) query def object using DAO syntax (you will need to add a reference to the DAO library if you are using a version of Access after A97),

Something along the lines of:

Dim Db as DAO.Database
Dim rs as DAO.Recordset
Dim qdf as DAO.QueryDef
Dim strSQL as String

strSQL = .... blah code here to build the SQL string

Set Db = CurrentDb()
Set qdf = db.QueryDefs("MyScratchQuery")
qdf.sql = strSQL
Set Rs = qdf.Execute

It is also possible (in code) to add a Querydef to the Querydefs collection.

Another possible solution may be to provide a QBE type interface to allow the user to define queries. I recall some code by DHookum (a regular contributor in the Reports forum), which did this, but unfortunately I cannot lay hands on a copy of it. Perhaps if you reposted your question in the Reports forum DHookum may respond and point to to the code in question.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nq]

Ken.
The user is not versed in SQL.
My original code: DoCmd.RunCommand acCmdNewObjectQuery

allowed the user to invoke the standard QBE interface. This was exactly what I wanted. Unfortunately the user could save any created query - hence db bloat. Primarily I needed a method of stopping the user save any query or automatically deleting the saved query.

Thanks for the lead with DHookum. I'll try that avenue.
Regards,
Nigel.

 

[Remou]

Hi
I am not sure how useful / sensible this is, furthermore I have not looked at it very carefully:

On Error Resume Next
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
    If IsError(qdf.Properties("Description")) Then
        Debug.Print qdf.Name
    Else
        Debug.Print qdf.Properties("Description")
        Debug.Print qdf.Properties("DateCreated")
    End If
End If
Next
End Sub

The general idea is to add a description to valid queries. Queries created by your user will not have one.

 

[nq]

Is there a method to "Allow Full Menus" programatically. This would solve my problem - enable full menus, use the query and then disable full menus again.

 

[nq]

Problem solved. Thanks to all who contributed to the solution. The following code works as a global module.

Public Function Temp_Query()

Dim stDocName As String
Dim stTempName As String

stDocName = "qryUserDefined"
stTempName = "qryUserTemp"

On Error Resume Next
DoCmd.DeleteObject acQuery, stTempName
DoCmd.CopyObject , stTempName, acQuery, stDocName

DoCmd.ShowToolbar "RunQuery", acToolbarYes
DoCmd.OpenQuery stTempName, acViewDesign, acEdit

DoCmd.RunCommand acCmdShowTable

End Function