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.