Set Querydef SQL to Null (Open an empty query editor)

[Jeff Freilich]

Hi All

I am trying to set the SQL of a querydef - I can do so using this
code:

CurrentDb.QueryDefs("EDitQuery").SQL = Me.txtSQL.Value
(Where me.txtSQL contains the sql statement.

What I want to do is set the SQL to nothing so it will clear any SQL
in the underlying querydef - and I can open up a blank query editor.
I have tried the following:

Set SQL to "SELECT" - says SELECT is a reserved word and the syntax is
wrong
Set SQL to "" - does not like that either
Set SQL to Null - using a null value variable
Set SQL to Empty - using an empty variable

Nothing seems to work - any thoughts in pointing me in the right
direction would be great

Thanks,

Jeff

 

[Benjamins via AccessMonster.com]

Hi Jeff,

You cannot have a Query without any SQL statement. Access wouldn't accept it.
What you can do is to delete the query.

When you want the query, just create the query if it does not exist.

 

[Jeff Freilich]

Hello and thanks for the reply

I know that you cannot actually have a query with an SQL statement -
but what I am trying to emulate it if you open a query in the query
editor = go to SQL view and delete all the SQL - when you go back to
the Query Editor you get an empty Editor (and then if you go back to
the SQL view you see: "SELECT;"

Is that possible to emulate or is that just a feature/function of
Acces that puts the "SELECT;" back in to what appears to be an empty
SQL statement?

Thanks

Jeff

 

[Douglas J. Steele]

It's not really clear to me what you're trying to do (nor what you mean by
"you cannot actually have a query with an SQL statement", since all queries
ARE SQL statements!)

 

[Jeff Freilich]

Sorry Doug

I was responding to the previous post - who said you cant have an
"Empty" SQL statement (I missed the word empty)

WHat I am trying to do is this

I have a query in my database called "EditQuery" - this is really just
a placeholder query - I have a table that stores various SQL
statements that the Admin users can modify/edit/run as needed. I can
easily pass the SQL statement from the table into the
"EditQuery" (once they finish editing the query they will save it and
then I have a process which allows them to save the SQL statement from
"EditQuery" back in to the table.

But where I am stuck is if I want to allow the user to "Add" a new
query - I want to change the "EditQuery" SQL statement to nothing so
that it opens up a blank Query Editory - as if you selected New Query.

Can you pass a null or empty value to QueryDef("EditQuery")? Or is
there a better way to do what I am attempting to do?

I hope I explained it better this time - if not please let me know -
thanks for your assistance

Jeff

 

[Marshall Barton]

I know that you cannot actually have a query with an SQL statement -
but what I am trying to emulate it if you open a query in the query
editor = go to SQL view and delete all the SQL - when you go back to
the Query Editor you get an empty Editor (and then if you go back to
the SQL view you see: "SELECT;"

Is that possible to emulate or is that just a feature/function of
Acces that puts the "SELECT;" back in to what appears to be an empty
SQL statement?

Have you tried to save that? It clearly is not a valid SQL
statement (thus not a valid query) and you get the same
reserved word error message.

That leads me to the conclusion that you are trying to
emulate something that Access doesn't do and can not allow.

 

[Banana]

Hello and thanks for the reply

I know that you cannot actually have a query with an SQL statement -
but what I am trying to emulate it if you open a query in the query
editor = go to SQL view and delete all the SQL - when you go back to
the Query Editor you get an empty Editor (and then if you go back to
the SQL view you see: "SELECT;"

Is that possible to emulate or is that just a feature/function of
Acces that puts the "SELECT;" back in to what appears to be an empty
SQL statement?

Thanks

Jeff

This?

http://www.accessruncommand.com/codeex/184.htm

 

[Douglas J. Steele]

As Marshall points out, you seem to be wanting to do something Access
doesn't support.

 

[Jeff Freilich]

Thanks to everyone for their reply

Yes I can save a query that has nothing in it.

You canot run the query - you get the message "Query Must have at
least one destination field" - but you can edit it and when you do you
get an Empty Query Editor

But what I am getting is that this must be a build in featuer of how
access handles queries - and that you cannot in fact pass an empty
value to the Querydefs SQL statement.

And I can't even simply add a table to the SQL statement: "SELECT FROM
tblOLRMain;
I guess I have to pass at least one field to the SQL statement:
"SELECT tblOLRMain.OLID FROM tblOLRMain;"

Thanks,

Jeff

 

[Douglas J. Steele]

You could always use

"SELECT * FROM tblOLRMain;"

 

[Dirk Goldgar]

This?

http://www.accessruncommand.com/codeex/184.htm

I find that the following code, adapted from the above, works:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.SelectObject acQuery, strQuery
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdDelete
DoCmd.RunCommand acCmdDesignView
End Sub

However, I think it's rather chancy, because it relies on nothing else
getting the focus between the statements.

 

[Jeff Freilich]

Hi Dirk

Thank you very much - that actually did the trick

And yes I agree it is a little chancy in that it has to keep the focus
on the query during the statements - but that is ok as this is the
only thing the user will be able to do at that moment - or I will lock
the user out from being able to do anything else

Thanks again to all who commented - very much appreciated

Jeff

 

[Banana]

I find that the following code, adapted from the above, works:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.SelectObject acQuery, strQuery
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdDelete
DoCmd.RunCommand acCmdDesignView
End Sub

However, I think it's rather chancy, because it relies on nothing else
getting the focus between the statements.

I wonder if we inserted Echo on/off between the statements would help
matters any?

 

[Dirk Goldgar]

I wonder if we inserted Echo on/off between the statements would help
matters any?

The original code you linked to had Echo Off at the beginning, and Echo On
at the end (after "RunCommad acCmdSQL View"). But I found in testing that
the statements I added didn't work unless I removed the Echo Off. I tried a
couple of variations with no success, and then gave up.

 

[Banana]

The original code you linked to had Echo Off at the beginning, and Echo
On at the end (after "RunCommad acCmdSQL View"). But I found in testing
that the statements I added didn't work unless I removed the Echo Off.
I tried a couple of variations with no success, and then gave up.

Ah, I see. Thinking about it bit, it does makes sense- Echo disables the
screen updating but we're replicating the UI functionality which of
course works through the screen (more or less).

I tried something and this seems to work here:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
Echo False
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.RunCommand acCmdDelete
Echo True
End Sub


Basically, I removed the RunCommand selecting the object & text because
by default, Access already has the SQL highlighted when you open it in
SQL View so we can just go directly to acCmdDelete.

Of course, this may be just fragility in different way as I don't know
for sure if Access _always_ highlight SQL text when we switch to SQL
view or whether this could be changed via Options and thus breaking the
code above.

BTW, the minimum acceptable SQL:

SELECT 1;


I used to have a series of template queries in one of my project where I
set several of queries' properties and re-used the template to re-crate
certain kind of query (e.g. a template for a combobox's rowsource, a
template for a form's recordsource, a template for ODBC nonscalar query
and so on) though I never tried to automate this as OP was wanting to.

 

[Dirk Goldgar]

Ah, I see. Thinking about it bit, it does makes sense- Echo disables the
screen updating but we're replicating the UI functionality which of course
works through the screen (more or less).

Yes, that's the conclusion I came to. I was surprised to find that, even if
I turned Echo back on before SelectObject and RunCommand acCmdSelectAll, it
still didn't work.

I tried something and this seems to work here:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
Echo False
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.RunCommand acCmdDelete
Echo True
End Sub


Basically, I removed the RunCommand selecting the object & text because by
default, Access already has the SQL highlighted when you open it in SQL
View so we can just go directly to acCmdDelete.

Yes, that was the first thing I did, but I hoped that by explicitly
selecting the query and doing a "select all", one might avoid any quirks as
much as possible. I don't know how much faith to put in that hope, though.

It occurs to me that one might use Windows API calls to locate the query
design window and send a message to clear it. I don't feel like spending
the time to work that out right now, though.

 

[Banana]

Yes, that's the conclusion I came to. I was surprised to find that, even if I turned Echo back on before SelectObject and RunCommand acCmdSelectAll, it still didn't work.

I didn't expect that particular error. Quite surprising, indeed!

Yes, that was the first thing I did, but I hoped that by explicitly
selecting the query and doing a "select all", one might avoid any quirks
as much as possible. I don't know how much faith to put in that hope,
though.

Perfectly reasonable.

It occurs to me that one might use Windows API calls to locate the query
design window and send a message to clear it. I don't feel like
spending the time to work that out right now, though.

I'm also not sure if it's going to be any less fragile than the other
solutions we discussed. I've been never quite comfortable with that kind
of use for the APIs, and Vista & Win7 has changed considerably such
that SendMessage no longer work in the same manner.

I would imagine that if we were going down the API route, I'd want to
find if it was possible to temporarily disable mouse clicks during the
code is running. That's brute-force but at least not as fragile and in
theory the code should run quickly enough that user never notice.

 

[Dirk Goldgar]

I'm also not sure if it's going to be any less fragile than the other
solutions we discussed. I've been never quite comfortable with that kind
of use for the APIs, and Vista & Win7 has changed considerably such that
SendMessage no longer work in the same manner.

I would imagine that if we were going down the API route, I'd want to find
if it was possible to temporarily disable mouse clicks during the code is
running. That's brute-force but at least not as fragile and in theory the
code should run quickly enough that user never notice.

That's an interesting idea. I doubt I'll go any farther with this, but I'll
hang onto it for future reference. Thanks.

 

[Marshall Barton]

I would imagine that if we were going down the API route, I'd want to
find if it was possible to temporarily disable mouse clicks during the
code is running. That's brute-force but at least not as fragile and in
theory the code should run quickly enough that user never notice.

Don't forget about Timer events doing the same kinds of
things. That's my primary reason for avoiding RunCommand
and DoCmd without an object argument.

 

[Banana]

Don't forget about Timer events doing the same kinds of
things.

Well, I could be wrong but a while ago, I did look at using Timer events
to run some code asynchronous and after some experimentation came to the
conclusion that Timer event does not run in a separate thread from the
rest of code so we would probably have a race condition but not
conflicts due to two pieces of code executing concurrently.

If we only have to worry about race condition, it shouldn't be material
as the code being discussed should enable mouse clicking/screen
updating/whatever before it finish and switch to the other procedure
waiting to be executed.

That's my primary reason for avoiding RunCommand
and DoCmd without an object argument.

I'm so there with you. I like to avoid either because I find it more
reliable to manipulate the objects directly. In this case, though, I
don't think there's any alternative.