If your SQL looks like this, you need to ask yourself how much code you’d have to inspect and revise if you decided to change a database field or table name, as frequently happens during development:
SQLString = "SELECT [first name], [last name], " & _ "[line preferences]" & _ " FROM [imaging users] WHERE [user code] = " & _ "'" & Trim(UCase(UserIDText.Text)) & "'"ODBCstatus = SQLExecDirect(ODBChandle1, SQLString, _ Len(SQLString))
What happens if SQL command conventions (field name delimiters) change? Because a compile doesn’t reveal such name misspellings or convention flaws, code in obscure procedures can be in production before defects are detected.
Our group established a table and field dictionary in a module used for a recent large project. This helped us ensure that we correctly pasted table and field names into all SQL commands. It also provided a repository that simplified maintenance.
As database resource names changed or new name-delimiting conventions were required, we revised the dictionary before recompiling. We also used the dictionary to convey descriptive information about tables and fields to developers. Our dictionary looks like this:
'tables:Public Const tblUsers As String = "[imaging users]"'data fields:Public Const fldFirstName As String = "[first name]" '16 charactersPublic Const fldLastName As String = "[last name]" '16 charactersPublic Const fldLinePreferences As String = _ "[line preferences]" '20 charactersPublic Const fldUserCode As String = "[user code]" '10 characters
Our SQL looks like this:
SQLString = "SELECT " & fldFirstName & _ ", " & fldLastName & _ ", " & fldLinePreferences & _ " FROM " & tblUsers & _ " WHERE " & fldUserCode & " = " & _ "'" & Trim(UCase(UserIDText.Text)) & "'"ODBCstatus = SQLExecDirect(ODBChandle1, SQLString, _ Len(SQLString))
Programmers don’t have to know the actual names of database components. They always use the constants that refer to the database components. A clean compile ensures you’ll use correct names and name-delimiting conventions in your SQL statements.