View query written in VBA

[Maver1ck666]

Is there a way to get a select query written in VBA to appear on screen like
a std query does please?

Why not just use the std query option I hear you say! Well, I am creating a
small program for other users who will be creating one off queries so I dont
want them to accidentally delete my ones so though it would be easier to
write them in a module and when a certain procedure happens, my query is
called for the user to check over.

Any suggestions please?

Kind regards,
Mav

 

[Keith Wilby]

Is there a way to get a select query written in VBA to appear on screen
like
a std query does please?

Why not just use the std query option I hear you say! Well, I am creating
a
small program for other users who will be creating one off queries so I
dont
want them to accidentally delete my ones so though it would be easier to
write them in a module and when a certain procedure happens, my query is
called for the user to check over.

Any suggestions please?

Kind regards,
Mav

Sounds like your users can get to the database window and are all sharing
the same file. These are regarded by the majority of developers to be bad
practise. Probably a better approach would be to provide your users with
their own "open" front end file with linked tables to your data, that way
they can create whatever queries they like without code.

Keith.
www.keithwilby.co.uk

 

[John Spencer]

You could create the SQL, assign it to a querydef object, and open the
querydef in design view.

UNTESTED CODE follows - note lack of error trapping
Public Sub BuildAndShowQuery(strSQL as String)
Dim qdef As QueryDef
Dim db As DAO.Database

Set db = CurrentDb()
db.QueryDefs.Delete "TEMP" 'Trap error 3265 Object does not exist
Set qdef = db.CreateQueryDef("Temp", strSQL)
qdef.Close
DoCmd.OpenQuery "Temp", acViewDesign, acReadOnly
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County