none
'Type Mismatch' error opening recordset RRS feed

  • Question

  • Getting a 'type mismatch' error from the third statement below. Any idea what might cause this? Value of rs1 is 'nothing'.

       Dim myDB As Database, rs1 As Recordset, rs2 As Recordset
       Set myDB = DBEngine.Workspaces(0).Databases(0)
       Set rs1 = myDB.OpenRecordset("tmpErrorDetail", DB_OPEN_DYNASET)  'Output

    This came right out of the blue in a database that has been working fine for years.

    Monday, November 16, 2015 9:17 PM

Answers

  • It's best to declare recordsets explicitly as DAO recordsets, to avoid confusion with ADODB recordsets.

    The code looks like it was written for Access 2.0 or so. A more up-to-date version:

        Dim myDB As DAO.Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset
        Set myDB = CurrentDb
        Set rs1 = myDB.OpenRecordset("tmpErrorDetail", dbOpenDynaset)  'Output


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 16, 2015 11:20 PM
  • Hi AllTheGoodNames,

    Did the suggestion from Hans work for you?

    Based on the link below, I suggest you check whether you add both the Data Access Objects (DAO) library and the ActiveX Data Objects (ADO) library.

    # You receive a "Type mismatch" error when you run the OpenRecordset method
    https://support.microsoft.com/en-us/kb/181542

    If your issue has not been resolved, please feel free to let us know your current situation.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 23, 2015 8:37 AM

All replies

  • It's best to declare recordsets explicitly as DAO recordsets, to avoid confusion with ADODB recordsets.

    The code looks like it was written for Access 2.0 or so. A more up-to-date version:

        Dim myDB As DAO.Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset
        Set myDB = CurrentDb
        Set rs1 = myDB.OpenRecordset("tmpErrorDetail", dbOpenDynaset)  'Output


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 16, 2015 11:20 PM
  • Hi AllTheGoodNames,

    Did the suggestion from Hans work for you?

    Based on the link below, I suggest you check whether you add both the Data Access Objects (DAO) library and the ActiveX Data Objects (ADO) library.

    # You receive a "Type mismatch" error when you run the OpenRecordset method
    https://support.microsoft.com/en-us/kb/181542

    If your issue has not been resolved, please feel free to let us know your current situation.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 23, 2015 8:37 AM
  • I am getting a consistent Type mismatch error when trying to return a recordset that calls a stored procedure from MS Access; this is despite explicitly declaring the recordsets as DAO objects

    Please help !!

    thanks

    Public Function GetClientCases(idxClient As String) As DAO.Recordset On Error GoTo Proc_err Dim db As Database Set db = CurrentDb() Dim qdf As QueryDef Dim rs As DAO.Recordset Dim strConnect As String Dim sqlBegin Dim strSQL As String ' Dim rs As Recordset ' Dim i As Integer strConnect = GetStrConnect If con Is Nothing Then ' con is a connection object Else If dbConnect Then ' this calls the connection Else MsgBox "Failed to connect to database server" & crlf & crlf & "Please close the application", vbCritical GoTo Proc_err End If End If Set qdf = db.QueryDefs("vw_ListClientCases") sqlBegin = qdf.SQL qdf.SQL = qdf.SQL & idxClient Set GetClientCases = rs Debug.Print rs.RecordCount Proc_exit: On Error Resume Next 'set rs = Nothing qdf.SQL = sqlBegin Set qdf = Nothing Set db = Nothing 'the code runs to here and exits with a type mismatch error

    Proc_err:

    resume proc_exit End Function



    jawsurgeon

    Tuesday, March 27, 2018 7:34 PM
  • This really ought to have been asked in a new thread, rather than tacked on to this one, since you were able to state up front that the issue discussed in this thread doesn't apply.

    Anyway, your code doesn't actually assign anything to the Recordset variable rs, unless I've totally overlooked something.  Shouldn't that be:

        qdf.SQL = qdf.SQL & idxClient

        Set rs = qdf.OpenRecordset()   '*** line added by DG

        Set GetClientCases = rs

    ?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, March 27, 2018 9:24 PM
  • I must have not posted the code correctly...sorry for that

    Indeed I did assign the rs to the result and then assigned the function to the rs

    All of that works Fine!

    However it still exits with a type mismatch code.

    Public Function GetClientCases(idxClient As String) As DAO.Recordset
    On Error GoTo Proc_err
        Dim db As DAO.Database
        Set db = CurrentDb()
        Dim qdf As DAO.QueryDef
        Dim rs As DAO.Recordset
        Dim strConnect As String
        Dim sqlBegin
        Dim strSQL As String
     
        strConnect = GetStrConnect
        If con Is Nothing Then
        Else
            If dbConnect Then
            Else
                MsgBox "Failed to connect to database server" & crlf & crlf & "Please close the application", vbCritical
                GoTo Proc_err
            End If
        End If
        Set qdf = db.QueryDefs("sp_GetClientCases")
        sqlBegin = qdf.SQL
        qdf.SQL = qdf.SQL & idxClient
        '******************************************
        Set rs = qdf.OpenRecordset(dbOpenSnapshot)
        Set GetClientCases = rs
        Debug.Print rs.RecordCount
        '******************************************
        'Exection is fine...
    Proc_exit:
        On Error Resume Next
        Set rs = Nothing
        qdf.SQL = sqlBegin
        Set qdf = Nothing
        Set db = Nothing
        Exit Function
        '...function exits with an error Type MisMatch
    Proc_err:
    Resume Proc_exit
    
    End Function


    jawsurgeon

    Wednesday, March 28, 2018 2:48 PM
  • Proc_exit:
        On Error Resume Next
        Set rs = Nothing
        qdf.SQL = sqlBegin
        Set qdf = Nothing
        Set db = Nothing
        Exit Function
        '...function exits with an error Type MisMatch
    Proc_err:
    Resume Proc_exit
    
    End Function

    Hi jawsurgeon,

    What is the effect of    Set rs = Nothing    on the result of the function?

    Wednesday, March 28, 2018 3:36 PM
  • You need to temporarily remove all error handling and then step through the code line-by-line to determine which line is causing an error. I suspect:

    1. strConnect = GetStrConnect (because GetStrConnect is not a String)
    2. If con Is Nothing Then (because you don't define what 'con' is)
    3. If dbConnect Then (because dbConnect is not defied as anything and even if it was, your If statement isn't complete. Is If dbConnect supposed to be = to some value or = True/False or what?)

    Once we know which line of code is causing a problem, then we can maybe help you correct it.


    Wednesday, March 28, 2018 3:37 PM
  • In the calling code, to what do you assign the result of GetClientCases?

    Please post the line of code that calls the function, and the declaration of the variable that receives its result.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 3:52 PM
  • Dirk:

    I've never seen this in my life:

    Public Function GetClientCases(idxClient As String) As DAO.Recordset

    Will ACCESS even allow a Public Function defined as a Recordset? And why would you do that and then go on to define a different one?

    I might try: Public Function GetClientCases()

    and then go from there because you are right. We don't know how idxClient is determined.

    Wednesday, March 28, 2018 4:31 PM
  • I've never seen this in my life:

    Public Function GetClientCases(idxClient As String) As DAO.Recordset

    Will ACCESS even allow a Public Function defined as a Recordset?.

    Absolutely it will.  A function can return a recordset.

    And why would you do that and then go on to define a different one?

    I'm not sure I understand what you're asking.  I do think that, if it were me, I'd probably eliminate the rs variable altogether and just write:

        Set GetClientCases = qdf.OpenRecordset(dbOpenSnapshot)

    That's perfectly legitimate.

    We don't know how idxClient is determined.

    I don't think idxClient is the issue here, since we are told that the OpenRecordset for rs works fine.  I wouldn't do it that way -- I'd use a parameter query, rather than modifying the querydef's SQL each time -- but it should work.

    My current guess is that the type mismatch isn't inside the function at all, but at the point where the return value (a DAO Recordset) is assigned to some variable in the calling code.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 4:40 PM
  • Dirk:

    There must a Call to Public Function GetClientCases(idxClient As String) As DAO.Recordset somewhere. And when the Call is made, it's going to want a String definition of idxClient isn't it? Then it apparently defines GetClientCases as a recordset using idxClient as its criteria. As I said, I've never seen this.

    Wednesday, March 28, 2018 5:09 PM
  • So the purpose of this code is to call SQL procedure on a Azure database and return the resultant records into an access object (form, combo box, whatever).

    I don'tr want to rewrite the entire code that calls this parameter query every time i need the records. Returning the records as a recordset would fulfuill this objective.

    I didn't really want to assign the results of the query to a rs and then assign the result to the function ..but it seemed to work and i was trying everything.

    I havent set rs = nothing yet...but hey I will give it a try

    The idxClient is just the parameter passed to the stored procedure.

    Hope this helps. Thanks for all the responses.

    Look forward to some more thoughts solutions


    jawsurgeon

    Wednesday, March 28, 2018 5:11 PM
  • Please answer my questions from a previous post:

    In the calling code, to what do you assign the result of GetClientCases?

    Please post the line of code that calls the function, and the declaration of the variable that receives its result.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 5:23 PM
  • I have stepped through the code with and without error handling

    GetStrConnect returns a connection string

    dbConnect is a function that connects to the Azure database

    con is a public variable that states whether the db is connected.


    jawsurgeon

    Wednesday, March 28, 2018 5:33 PM
  • So far I have not called GetClientCases except from the debug window...

    maybe that is the problem !


    jawsurgeon

    Wednesday, March 28, 2018 5:34 PM
  • And how are you calling it from the Immediate Window?  What are you entering there?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 5:40 PM
  • ?GetClientCases(1) 

    jawsurgeon

    Wednesday, March 28, 2018 5:46 PM
  • ?GetClientCases(1) 

    In my test, that works okay.  But my test was simplified, and doesn't involve SQL Azure.  Try this in the immediate window:

        ?TypeName(GetClientCases())


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 5:57 PM
  • Here is the result :

    ?TypeName(GetClientCases(1))
     0 
    Recordset2


    jawsurgeon

    Wednesday, March 28, 2018 6:35 PM
  • Here is the result without setting the rs =

    Set GetClientCases = qdf.OpenRecordset(dbOpenSnapshot)
        'Set GetClientCases = rs
        Debug.Print rs.RecordCount
        '******************************************

    Here is the result:

    ?TypeName(GetClientCases(1))
    Recordset2

    So perhaps the error is from the debugger itself


    jawsurgeon

    Wednesday, March 28, 2018 6:41 PM
  • That's not actually what I asked you to enter.  Is that really what you entered, and really what you got?

    I asked you to enter:

        ?TypeName(GetClientCases())

    and you say you entered:

        ?TypeName(GetClientCases(1))

    I wanted to find out the TypeName of the return value of GetClientCases(), which should (if all was working properly) have resulting in "Recordset2".  What you entered *ought* to be getting the TypeName of the second field in the returned recordset, which I wouldn't expect to be "Recordset2", but I don't see how it could have resulted in the *two* values you posted.

    Could you please try again, and make sure that you enter exactly what I asked?

    EDIT:  THIS WHOLE POST IS A MISTAKE ON MY PART.  IF YOU'RE FOLLOWING THIS THREAD, PLEASE IGNORE IT.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Wednesday, March 28, 2018 6:46 PM
  • Here is the result without setting the rs =

    Set GetClientCases = qdf.OpenRecordset(dbOpenSnapshot)
        'Set GetClientCases = rs
        Debug.Print rs.RecordCount
        '******************************************

    Here is the result:

    ?TypeName(GetClientCases(1))
    Recordset2

    So perhaps the error is from the debugger itself

    That's odd.  I wonder if your stored procedure is returning multiple recordsets, or a count and a recordset.  Did your procedure include the statement "SET NOCOUNT ON"?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 6:49 PM
  • The argument is not optional. 

    ?typeName(GetclientCases())

    returns: Compile Error: Argument Not optional


    jawsurgeon

    Wednesday, March 28, 2018 6:52 PM
  • SET NOCOUNT ON is set in the procedure


    jawsurgeon

    Wednesday, March 28, 2018 6:53 PM
  • The argument is not optional. 

    ?typeName(GetclientCases())

    returns: Compile Error: Argument Not optional

    Oh, that's right, your procedure requires an argument, so 1 is the argument to the function.  According to the function definition, it should have been a string, but Access would helpfully convert it to a string before passing it to the function.

    SO ...

    When you entered this in the Immediate Window:

        ?TypeName(GetClientCases(1))

    you got the output:

        Recordset2

    And you didn't get an error?  That means the function was called successfully, and returned a DAO recordset.  Do we still have a problem?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 28, 2018 6:58 PM
  • Here is the procedure:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[GetClientCases]
    -- Add the parameters for the stored procedure here
    @IdxClient AS INT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        -- Insert statements for procedure here
    SELECT *
    FROM ClientCases

    WHERE idxClient = @idxClient
    END


    jawsurgeon

    Wednesday, March 28, 2018 7:03 PM
  • And now that I look at it again, I see why you were getting the type mismatch error.  You said you were testing in the Immediate Window with this:

        ?GetClientCases(1) 

    But you can't Debug.Print *any* recordset object; it will always give you a type mismatch.  However, you should be able to enter this in the Immediate Window to print the value of the first field in the recordset:

        ?GetClientCases(1).Fields(0)

    Or you could set a variable to it and loop through the fields:

        set rs = GetClientCases(1) : for each fld in rs.Fields : ?fld.Name, fld.Value : next fld : rs.Close


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Proposed as answer by Jawsurgeon Wednesday, March 28, 2018 7:56 PM
    • Edited by Dirk Goldgar Wednesday, March 28, 2018 8:06 PM typo
    Wednesday, March 28, 2018 7:15 PM
  • Thanks Dirk for your help.

    I think this solves the issue.

    =Adam


    jawsurgeon

    Wednesday, March 28, 2018 7:57 PM