Beyond Excel: VBA and Database Manipulation

Jun 17 2010   4:37PM GMT

Check Entry – DB_Lookup

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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:

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
                Do While .Visible
                If .pOK Then   'The OK button was used to exit
                   DB_Lookup = """" & .pCode & """,""" & .pDesc & """"
                End If
           End With
       End If
       DB_Lookup = v
   End If
    If Err.Number <> 0 Then MsgBox _
        "DB_Lookup - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: