Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
DB_Lookup validates a code or ID against a database table. Where as XL_Lookup is meant for tiny code lists, such as States or Provinces that fit nicely in an Excel range, DB_Lookup is meant for massive lists such as employees, customers, or general ledger account numbers. Mere mortals such as we stuggle to memorize such volumes of data and so a means to search such lists using partial codes or descriptions is often a necessity.
Because this is often used over massive lists, I recommend adding a limiter to your SQL Select statements, such as the “Top 99″ bit shown in the example’s documentation below. Unfortunately, “Top 99″ isn’t standard ISO.2008 SQL. It works fine for our Access database, but Access doesn’t accept the standard “Fetch Top 99 Rows Only”. This means you may have to adjust your SQL statements a bit depending on which database you use. To help you find which syntax is best, here is a helpful link: http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause
Search capabilities are welcome user tools and so it may seem unfair that XL_Lookup doesn’t offer them for the tiny lists in Excel ranges. That’s okay. If you really want to add such capabilities to Excel ranges, you can. DB_Lookup works with Excel’s ranges since Excel’s ranges can be searched by ODBC just like most relational databases.
There are times, however, when we don’t want to offer DB_Lookup‘s search capabilities and so, we have a switch that allows us to turn that feature off, bUsePopUp. If bUsePopUp is set to false, the user will not be given the opportunity to select a valid entry; DB_Lookup will just verify the code or ID. This is useful when we want to perform a final check of all entries just before updating the database and we only want to know if everything is OK. If it isn’t, the user will get their chance to fix things later.
DB_Lookup can connect to the database by creating its own private connection from a connection string, or by sharing an already existing connection object. I recommend using a connection object for speed in most cases, especially when checking scores of entries just before updating the database.
Lastly, I want to mention the vbTextCompare constant in our Replace statement in the code below. vbTextCompare makes our Replace statement case insensitive so I don’t have to worry if “like” in sSQLCode string was all lower case, all upper case, or mixed case.
Here is DB_Lookup. Next post will cover frmSelect.
Function DB_Lookup(sCode As String, _ sSQLCode As String, sSQLDesc As String, _ sCodeLbl As String, sDescLbl As String, _ bUsePopUp As Boolean, sConnect As String, _ Optional cn As ADODB.Connection _ ) As Variant
' Description:Validates that a value exists in a database table
' Parameters: sCode Code/ID/Number to be validated ' sSQLCode SQL to find records by Codes/ID/Numbers ' sSQLCode SQL to find records by Description/Names ' sCodeLbl Label for Code in frmSelect ' sDescLbl Label for Desc in frmSelect ' bUsePopUp If true, let user select sCode ' sConnect Connection String ' cn Connection Object (optional)
' Example: sSQLCode = _ ' "Select Top 99 [Product Code],[Product Name] " & _ ' "From Products " & _ ' "Where uCase([Product Code]) like '?%' " & _ ' "Order by [Product Code] " ' sSQLDesc = _ ' "Select Top 99 [Product Code],[Product Name] " & _ ' "From Products " & _ ' "Where uCase([Product Name]) like '%?%' "& _ ' "Order by [Product Name] " ' v = DB_Lookup(rngCell.Text, "Products", _ ' sSQLCode, sSQLDesc, _ ' "Code", "Name", True, sConnect, cn) ' Date Init Modification ' 01/01/01 CWH Initial Programming
On Error GoTo ErrHandler ' DB_Lookup = Null 'Assume the worst Dim v As Variant 'Generic Result Dim sSQL As String 'SQL string 'Step 1: Just see if it is in the file ' Use the sSQLCode string to construct an _ SQL Select statement without wildcards or _ the "like" keyword (use "=" instead) sSQL = Replace( _ Replace( _ Replace(sSQLCode, "%", ""), _ "?", sCode), _ " LIKE ", " = ", 1, -1, vbTextCompare)
v = SQLRead(sSQL, sConnect, 300, False, cn) 'Step 2: If not, ask user to select (if allowed) If IsNull(v) Or IsEmpty(v) Or v = "" Or v = Failure Then If bUsePopUp Then With frmSelect .pConnect = sConnect .pLblCode = sCodeLbl .pLblDesc = sDescLbl .pTitle = "Select " & sCodeLbl .pCode = "" .pDftCode = Replace(sCode, "?", "") & "%" .pSQLCode = sSQLCode .pSQLDesc = sSQLDesc .Show Do While .Visible DoEvents Loop If .pOK Then 'The OK button was used to exit DB_Lookup = """" & .pCode & """,""" & .pDesc & """" End If End With End If Else DB_Lookup = v End If
ErrHandler: If Err.Number <> 0 Then MsgBox _ "DB_Lookup - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext On Error GoTo 0