How do I reuse SQL in VBA

N

Nick X

Happy New Year Everyone,
I have some complex SQL statements that I would like to assign to a couple
different Events. Any Ideas would be appreciated.
Thanks

Dim strSQLAll As String
Dim strSQLsearch As String
strSQLAll = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark;"

strSQLsearch = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark " & _
"WHERE (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2]) AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1]) AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2]) AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1]) AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1]));"
 
A

Allen Browne

So you want to reuse these strings, exactly as they are in several events of
the one form? Declare them as constants in the General Declarations section
of your form's module.

Open the module of the form (code window.)
At the top, just below the Option statements, add these lines:
Private Const mstrcSQLAll = "SELECT ...
Private Const mstrcSQLsearch = "SELECT ...

You can now use the strings mstrcSQLAll or mstrcSQLsearch anywhere in the
module of this form.

In practice, what I generally do is to declare the stub of the SQL statement
(the SELECT and FROM clause) as one constant, and the tail (typically the
ORDER BY clause) in another. Then I just build the string for the WHERE
clause to patch in between them. Of course, strWhere is just a zero-length
string for the case where you want all records.

One advantage of this approach is that it is much more efficient for
handling lots of criteria (as in your last example) and also copes better
with cases where the user doesn't fill in all the search boxes.

For a downloadable example of building the WHERE clause like that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nick X said:
Happy New Year Everyone,
I have some complex SQL statements that I would like to assign to a couple
different Events. Any Ideas would be appreciated.
Thanks

Dim strSQLAll As String
Dim strSQLsearch As String
strSQLAll = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark;"

strSQLsearch = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark " & _
"WHERE (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1]));"
Click to expand...
 
M

Minton M

So you want to reuse these strings, exactly as they are in several events of
the one form? Declare them as constants in the General Declarations section
of your form's module.

Open the module of the form (code window.)
At the top, just below the Option statements, add these lines:
    Private Const mstrcSQLAll = "SELECT ...
    Private Const mstrcSQLsearch = "SELECT ...

You can now use the strings mstrcSQLAll or mstrcSQLsearch anywhere in the
module of this form.

In practice, what I generally do is to declare the stub of the SQL statement
(the SELECT and FROM clause) as one constant, and the tail (typically the
ORDER BY clause) in another. Then I just build the string for the WHERE
clause to patch in between them. Of course, strWhere is just a zero-length
string for the case where you want all records.

One advantage of this approach is that it is much more efficient for
handling lots of criteria (as in your last example) and also copes better
with  cases where the user doesn't fill in all the search boxes.

For a downloadable example of building the WHERE clause like that, see:
    Search form - Handle many optional criteria
at:
   http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.



Happy New Year Everyone,
I have some complex SQL statements that I would like to assign to a couple
different Events.  Any Ideas would be appreciated.
Thanks
Click to expand...
   Dim strSQLAll As String
   Dim strSQLsearch As String
   strSQLAll = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
       "PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
       "Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
       "[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
       "[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
       "FROM PtblBenchmark;"
Click to expand...
   strSQLsearch = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
       "PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
       "Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
       "[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
       "[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
       "FROM PtblBenchmark " & _
       "WHERE (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1]));"- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Another idea I use a lot is to store the SQL statement in a table and
make the parameters tokens in the string, eg:

Select txtName from tblProducts where idProduct=?productID

... then I have a function look up the SQL and replace the tokens with
the values. This works well because you can change the functionality
of queries without having to recompile any code. I'm scared to say
it's similar to a stored procedure because it *so* isn't, but you get
the idea.

I'm not a huge fan of embedding SQL in my code, but Allen's suggestion
is great if you need a lightweight solution.

Hope this helps,
James
 
N

Nick X

Thanks Allen,
This is exactly the solution I had at the tip of my brain. this will come in
handy for the search form I am creating.
Thanks Again,
Nick

Allen Browne said:
So you want to reuse these strings, exactly as they are in several events of
the one form? Declare them as constants in the General Declarations section
of your form's module.

Open the module of the form (code window.)
At the top, just below the Option statements, add these lines:
Private Const mstrcSQLAll = "SELECT ...
Private Const mstrcSQLsearch = "SELECT ...

You can now use the strings mstrcSQLAll or mstrcSQLsearch anywhere in the
module of this form.

In practice, what I generally do is to declare the stub of the SQL statement
(the SELECT and FROM clause) as one constant, and the tail (typically the
ORDER BY clause) in another. Then I just build the string for the WHERE
clause to patch in between them. Of course, strWhere is just a zero-length
string for the case where you want all records.

One advantage of this approach is that it is much more efficient for
handling lots of criteria (as in your last example) and also copes better
with cases where the user doesn't fill in all the search boxes.

For a downloadable example of building the WHERE clause like that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nick X said:
Happy New Year Everyone,
I have some complex SQL statements that I would like to assign to a couple
different Events. Any Ideas would be appreciated.
Thanks

Dim strSQLAll As String
Dim strSQLsearch As String
strSQLAll = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark;"

strSQLsearch = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark " & _
"WHERE (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1]));"
Click to expand...
Click to expand...
 
N

Nick X

Thanks Minton,
I think I will be able to use this in the future.
NickX

Minton M said:
So you want to reuse these strings, exactly as they are in several events of
the one form? Declare them as constants in the General Declarations section
of your form's module.

Open the module of the form (code window.)
At the top, just below the Option statements, add these lines:
Private Const mstrcSQLAll = "SELECT ...
Private Const mstrcSQLsearch = "SELECT ...

You can now use the strings mstrcSQLAll or mstrcSQLsearch anywhere in the
module of this form.

In practice, what I generally do is to declare the stub of the SQL statement
(the SELECT and FROM clause) as one constant, and the tail (typically the
ORDER BY clause) in another. Then I just build the string for the WHERE
clause to patch in between them. Of course, strWhere is just a zero-length
string for the case where you want all records.

One advantage of this approach is that it is much more efficient for
handling lots of criteria (as in your last example) and also copes better
with cases where the user doesn't fill in all the search boxes.

For a downloadable example of building the WHERE clause like that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.



Happy New Year Everyone,
I have some complex SQL statements that I would like to assign to a couple
different Events. Any Ideas would be appreciated.
Thanks
Click to expand...
Dim strSQLAll As String
Dim strSQLsearch As String
strSQLAll = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark;"
Click to expand...
strSQLsearch = "SELECT PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Northing, " & _
"PtblBenchmark.Easting, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2, " & _
"Switch([cboFieldList]=" & """1971 Elevation""" &
",[Elevation],[cboFieldList]=" & """Archived Elevation""" & "," & _
"[ArchivedElev],[cboFieldList]=" & """Local 88 Elevation""" &
",[ElevChk],[cboFieldList]=" & """GPS Elevation""" & "," & _
"[ElevGPS],[cboFieldList]=" & """NGS Elevation""" &
",[ElevGPS],[cboFieldList]=" & """""" & ",[Elevation]) AS Elev, * " & _
"FROM PtblBenchmark " & _
"WHERE (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X1] And
[forms]![frmBenchmarkSearch]![X2])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y1] And [forms]![frmBenchmarkSearch]![Y2])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1])) OR (((PtblBenchmark.Northing) Between
[forms]![frmBenchmarkSearch]![Y2] And [forms]![frmBenchmarkSearch]![Y1])
AND
((PtblBenchmark.Easting) Between [forms]![frmBenchmarkSearch]![X2] And
[forms]![frmBenchmarkSearch]![X1]));"- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Another idea I use a lot is to store the SQL statement in a table and
make the parameters tokens in the string, eg:

Select txtName from tblProducts where idProduct=?productID

... then I have a function look up the SQL and replace the tokens with
the values. This works well because you can change the functionality
of queries without having to recompile any code. I'm scared to say
it's similar to a stored procedure because it *so* isn't, but you get
the idea.

I'm not a huge fan of embedding SQL in my code, but Allen's suggestion
is great if you need a lightweight solution.

Hope this helps,
James
Click to expand...
 
D

DF

I would like to "link" a query to a form component for the purpose of
operating with the metaphor-object instead of text. Is there a
straighforward way of doing that?
 
D

Douglas J. Steele

You're going to have to explain in a little more detail what it is you're
trying to do.

What do you mean by linking a query to a form? What is "operating with the
metaphor-object instead of text"?
 
Top