Checking SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
Part of my application allows users to edit a pre-generated SQL statement
and then uses this as the source of a list box control. However, I have
noticed that if the SQL statement contains a syntax error no warning or error
is generated and the list box just displays 0 rows. This can obviously lead
a user to think there is no data that meets the query criteria. Is there a
way of checking the SQL statement to ensure that it is correct so that it can
be handled if it isn't?

Thanks
Andy
 
Try saving the SQL as a QueryDef object. An error will be raised:

Sub BadSQL()
On Error GoTo Err_BadSQL

Dim strSQL As String
Dim qdfCurr As DAO.QueryDef

strSQL = "SELECT"
Set qdfCurr = CurrentDb.CreateQueryDef("qryBadSQL", strSQL)

End_BadSQL:
Exit Sub

Err_BadSQL:
MsgBox Err.Number & ": " & Err.Description
Resume End_BadSQL

End Sub

You could actually have a predefined QueryDef object associated with the
form, and strictly update the SQL of that QueryDef.
 
Andy,

Do you mean you actually let the user modify the SQL expression
directly? Not good!!! There's definitely a better way to achieve what
you want, if you care to explain.

Nikos
 
Hi Nikos

The reason it works like this is historic really. We have another
non-Access database in which the front-end contains a query tool that allows
users to build queries by selecting certain fields and the relevant
parameters. Since these are fixed, flexibility was provided by allowing the
user to modify the SQL (so they could use OR statements or other fields).
Subsequently, I was required to build Access databases so decided to follow
this model in order to keep the interfaces the same. Consequently, staff
here are able to use simple SQL syntax to modify their queries eg a query can
be built to return all rows where a field is 'X' and it can simply be changed
to return rows where that field is 'Y' by editing the SQL.

I am aware that this is not ideal and so the more recent databases I have
done work slightly differently by allowing users to select and apply
parameters to any field rather than a limited set so there is little need for
them to modify the SQL.

However, I am more than willing to come up with a better method - this
particular database was only the second one I put together so I am painfully
aware that there is a lot that can be improved!!!

Andy
 
Andy,

For what it's worth, here's what I do in a similar situation:

i have a query with all the possible tables and fields potentially
required for filtering or results, and no criteria set in it;
I have a form with a text box (include field in results, and sorting
order) and two textboxes/combos for applying filters (unique values or
ranges);
The code behind my "Run Query" command button scans all the text boxes
to compose the SELECT and the GROUP BY clauses, and all the parameter
controls to compose the WHERE clause, then puts all those together in an
SQL string. It is a coding-intensive solution, but provides maximum
flexibility as well as a user-friendly, fool proof interface... took me
about a day to code, but has kept my users happy for two years.

HTH,
Nikos
 
Yes keep the users happy. Don't want the natives rising up with tar and
feathers, that's what Finance is for.
 
Back
Top