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)
Print
CloseCopyright © 2001 - 2025 MJ Technologies, LLC. All rights reserved.