(Solved) – How to make a proper query using Excel as database and ADODB as connection?


I am using Microsoft ADO 6.1 Library. I am making a simple login form where the users and their password are stored in a worksheet (StaffList) in an Excel file (Reports.xlms) that is outside of the workbook where I am creating a Login userform(DataEntry.xlms). I am trying to query from Reports.xlms and verify if username and password match any of the entries entered in the userform.
On the userform, I have this code for reference for the login button.

Private Sub btn_Login_Click()

Dim txtbx_staffname As String
Dim txtbx_password As String

If Me.txtbx_staffname.Value = "" Then 'Username
    MsgBox "Please enter your user name.", vbCritical
    Exit Sub

    End If

If Me.txtbx_password.Value = "" Then
    MsgBox "Please enter your password.", vbCritical
    Exit Sub

    End If

Call UserLogin(Me.txtbx_staffname.Value, Me.txtbx_password.Value)
End Sub

I have this code to check if username and password entered is present in the StaffList

Sub UserLogin(User_Id As String, Password As String)

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:_Download CenterCoop Drive_Coop DriveTHHC DBDatabaseReports.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=YES;FMT=Delimited;IMEX=1;"";"
qry = "Select * From [StaffList$] where User_Id = '" & User_Id & "'" 
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 0 Then
    MsgBox "Incorrect Usern name", vbCritical
ElseIf rst.Fields("Password").Value = Password Then
    MsgBox "Incorrect Password", vbInformation
    MsgBox "Incorrect Password", vbCritical
End If


End Sub

I having a run-time error that “Data type mismatch in criteria expression in the line. “rst.Open qry, cnn, adOpenKeyset, adLockOptimistic”
It seems fine if ì use “qry = “Select * From [StaffList$]”

I don’t understand why there is a data type mismatch when both variables are assigned as strings. What is the way to make a query here?

Sorry, amateur vba. Thanks for helping.

Leave a Reply

Your email address will not be published. Required fields are marked *