Thread: Using ADO in Excel VBA - Search for Field in Access Database

Using ADO in Excel VBA-Search for Field in Access Database

--------------------------------------------------------------------------------
I'm fairly new to ADO and I'm trying to figure out how to search for a field inside an Access Database by using Excel VBA. I am able to retrieve a record by finding a value in a particular field, but when the field is not found is when I'm having the issue.

I would like to have an if statement or other workaround where if the field value I am looking for is not found, then a userform will popup to prompt the user to register their information. if I can just get the syntax down on how to do something else when the field is not found, I will be okay.


Below is the vba code I am using in Excel...

I am retrieving the user's Windows Logon ID, and then going to Access to Retrieve their email address from a table named Employees.



Code:

Option Explicit
Const myDB = "Employees.mdb"


    
Private Sub CommandButton1_Click()

    Dim User As String
    Dim email As String
    Dim myFile As String
    User = UserNameWindows

  ' Test Field Select button
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sSQL As String

    MsgBox ("Your Computer Login is ") & User
    sSQL = "SELECT * FROM Employees WHERE `Network Login ID` = '" & User & "'"





    Application.EnableEvents = False

  ' Create the database connection
    Set cnn = New ADODB.Connection
    myFile = ThisWorkbook.Path & "\" & myDB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open myFile
    End With
    
  ' Create the recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
        CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
    
    
    
  ' Transfer data to Excel

    email = rst.Fields("email").Value
    MsgBox ("Your email is ") & email
        
  ' Close the connection and clean up references
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True


End Sub

    
    
Function UserNameWindows() As String
    UserNameWindows = Environ("USERNAME")
End Function

By the field is not found you mean there is no employee in the database? or there is no email associated with the employee?

The reason I ask is they create 2 different issues.

With rst
    If Not rst.EOF then
        If Not IsNull(!Email)
            'something here
        End If
    End If
End With

Those are examples of how I would mitigate both situations.

Hope this helps,

Dan