Thread: 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.

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

Hi

Here's one way.

' Transfer data to Excel
    If rst.RecordCount > 0 Then
      email = rst.Fields("email").Value
      MsgBox ("Your email is ") & email
    Else
      MsgBox "Do your request thing here"
    End If

HTH

rylo