Access Tip of the Day


Use variables to construct SQL statements (Access 97/2000/2002)


If you have a procedure that uses several nearly identical SQL statements, you can make your code easier to read and manage by logically storing the different parts of the statements in variables. You can then combine the variables as needed to build a required statement, letting you recycle the elements of the SQL statement that don't need changing. For example, you might use code like the following:

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSELECT = "SELECT CompanyName, City "
strFROM = "FROM Divisions "
strWHERE = "WHERE Country = ""USA"" "
strSQL = strSELECT & strFROM & strWHERE

rst.Open strSQL, CurrentProject.Connection
'code that does stuff
rst.Close

strWHERE = "WHERE Country = ""UK"" "
strSQL = strSELECT & strFROM & strWHERE

rst.Open strSQL, CurrentProject.Connection
'code that does stuff
rst.Close

strFROM = "FROM Clients "
strSQL = strSELECT & strFROM

rst.Open strSQL, CurrentProject.Connection
'code that does stuff
rst.Close

Of course, the SQL statements in this example are short and coding them completely wouldn't necessarily be a problem. However, it's easy to see how this technique could help with more complex queries.



(Content provided by Element K Journals)


(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
 
PrintPrint CloseClose

Copyright © 2001 - 2017 MJ Technologies, LLC.  All rights reserved.