none
Type Mismatch with ADODB.Recordset RRS feed

  • Question

  • Good afternoon, all -

    I'm having a problem with an Excel macro.

    I'm taking it over from another employee who has left, so I'm trying to figure out how to make it work.

    Basically, it runs a sql query to grab some data and then plugs that data into an Excel sheet.

    However, in the section running the query, I'm getting a Type Mismatch error - oddly enough, the routine works fine on another computer.

    The variable and connection throwing the error are defined so;

    Dim iIPNewAdmRecs As Integer
    Dim rsIPNewAdm As ADODB.Recordset

    The error being thrown is on this line in the connection section;

    iIPNewAdmRecs = rsIPNewAdm.RecordCount

    So, I'm guessing that iIPNewAdmRecs should not be defined as an integer. But, from searches, I'm not sure what it should be. Nor can I figure out why it works on one computer - and has for months and months - but, not this new one. Both are using the same version of Excel. The only real difference I can see is that one where it works is Win7, the other Win10.

    Can anyone give me a hand on this?

    Thanx in advance for any assistance!


    • Edited by Adam Quark Friday, February 21, 2020 8:22 PM
    Friday, February 21, 2020 8:21 PM

Answers

  • Finally found that the type for a 64-bit system is LongLong. So, I set that when I declared the variable and now it works.

    Thanx, everyone, for your assistance!

    • Marked as answer by Adam Quark Wednesday, March 4, 2020 3:55 PM
    Wednesday, March 4, 2020 3:55 PM

All replies

  • Going by one site I found, it said that RecordCount returns a long value, so I changed the Dim statement to make it long, rather than integer. But, it still threw the Type Mismatch error.
    Friday, February 21, 2020 8:31 PM
  • For an experiment use MoveLast then MoveFirst before asking for RecordCount. Most likely this will fail too and if so that means the (guessing the Win10 computer) failing computer the underlying DAO provider is the issue. Check to see if both are the same version e.g. 3.6

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, February 21, 2020 8:42 PM
    Moderator
  • For an experiment use MoveLast then MoveFirst before asking for RecordCount. Most likely this will fail too and if so that means the (guessing the Win10 computer) failing computer the underlying DAO provider is the issue. Check to see if both are the same version e.g. 3.6

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    I'll give that a try - have to research how, first, though.

    If it helps, this is the whole of the connection section;

    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=DatabaseName;Data Source=ServerName"
    Set rsIPNewAdm = New ADODB.Recordset
    rsIPNewAdm.CursorLocation = adUseClient
    rsIPNewAdm.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

    Is the DAO provider the connection defined in the first line?

    Apologies for my noobiness, but I'm learning as I go on this connection jazz.

    Friday, February 21, 2020 8:55 PM
  • Check to see if both are the same version e.g. 3.6

    The version on the working box is 12.0.6293.0. That of the failing box is 13.0.5426.0.

    So, they are different versions, but I'm at a loss how to go from there.

    Oh, and, I was wrong about the OS of the working box; it, too, is Win10.

    Thanx for your help, Karen!

    Friday, February 21, 2020 9:07 PM
  • Hi Adam Quark,

    Did you solve your problem? If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    If your problem is not solved, I find a related reference:

    ADO error or bug - Type Mismatch

    Hope the suggestions in the reference could be helpful.

    Besides, since your code is more related to VBA, you can consider posting it in VBA forum for more efficient responses. Thanks. 

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, February 24, 2020 6:54 AM
    Moderator
  • Hi Adam Quark,

    Did you solve your problem? If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    If your problem is not solved, I find a related reference:

    ADO error or bug - Type Mismatch

    Hope the suggestions in the reference could be helpful.

    Besides, since your code is more related to VBA, you can consider posting it in VBA forum for more efficient responses. Thanks. 

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thanx, Xingyu - I had forgotten all about that 32/64-bit issue.

    My VBA editor shows a "Microsoft Activex Data Objects (Multi-dimensional) 6.x" reference. Is that the same as "Microsoft Activex Data Objects 6.x?"

    I went ahead and checked it, anyway, and we'll see what happens. If my PC bursts into flame and burns down the city, I'll let you know...

    Monday, February 24, 2020 8:02 PM
  • Nope, nothing changed, still getting the Type Mismatch error.

    I'll give your references a closer look and see what I can learn, but, at the moment, my manager has tasked me to convert the whole process to SSIS and bypass Excel altogether, anyway, so the question may be moot.

    Although, I'd still like to figure it out...

    Thanx again, Xingyu!

    Monday, February 24, 2020 8:06 PM
  • Hi Adam Quark,

    The following reference is SSIS forum, if you have any problems about SSIS in the future, you can ask your question there.

    SQL Server Integration Services

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 25, 2020 6:12 AM
    Moderator
  • Well, I found a solution - not so much an answer, but it works.

    I commented out Option Explicit and the Dim statements creating the relevant variables. That way, Basic will decide the type at runtime.

    This worked. The connection worked fine and my macros processed like a charm. So, at least I can get on with things.

    But, like I said, this isn't an answer.

    Microsoft documentation says that RecordCount returns a long. But, as I said in a prior post, setting up a Dim statement defining the variables as long still gave the Type Mismatch. So, something is still not working right - I don't know what it may be, but I'm able to run my macros, so I guess I'm not all that concerned.

    I don't know whether or not to count this thread as Answered. I mean, I seem to have gotten around the problem, but the fact remains, I should be able to Dim the variables as Long and not have a problem - well, as I see it, anyway. Maybe I'm wrong and missing something...

    Thursday, February 27, 2020 5:16 PM
  • In regards to the code resolving at runtime you can always do

    Console.WriteLine(SomeVariable.GetType()) 

    To see what the type is at runtime while running under the debugger. This way you can strong type the variable.

    Also note the replies here

    https://stackoverflow.com/questions/28729023/type-mismatch-error-on-adodb-recordset


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, February 27, 2020 5:52 PM
    Moderator
  • In regards to the code resolving at runtime you can always do

    Console.WriteLine(SomeVariable.GetType()) 

    To see what the type is at runtime while running under the debugger. This way you can strong type the variable.

    Also note the replies here

    https://stackoverflow.com/questions/28729023/type-mismatch-error-on-adodb-recordset


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    I'd heard of this 32/64 bit problem and we were under the impression that installing Microsoft Access database engine 2016 corrected it.

    Are we wrong about that?

    Thursday, February 27, 2020 7:21 PM
  • In regards to the code resolving at runtime you can always do

    Console.WriteLine(SomeVariable.GetType()) 

    To see what the type is at runtime while running under the debugger. This way you can strong type the variable.

    Also note the replies here

    https://stackoverflow.com/questions/28729023/type-mismatch-error-on-adodb-recordset


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    I'd heard of this 32/64 bit problem and we were under the impression that installing Microsoft Access database engine 2016 corrected it.

    Are we wrong about that?

    Wish I could test that and know for sure but have not worked with that technology since 1995.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 27, 2020 8:30 PM
    Moderator
  • In regards to the code resolving at runtime you can always do

    Console.WriteLine(SomeVariable.GetType()) 

    To see what the type is at runtime while running under the debugger. This way you can strong type the variable.

    Also note the replies here

    https://stackoverflow.com/questions/28729023/type-mismatch-error-on-adodb-recordset


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    I just tried putting your piece of code in the macro - although, I used MsgBox, rather than Console - and I got an Object Required error. I've never had any luck getting Console to work in an Excel macro, so I figured it's something only available in a standalone compiler or something. Or, and this is not outside the realm of possibility, I'm doing something wrong.

    Heaven forfend!

    Is .GetType allowed in VBA? When I enter the "." after the variable name, Autosense usually provides options at that point, but it didn't show anything at all. Is this a VB.NET code?

    But, when I took that off and just sent the variable to MsgBox, I got 93 - to be expected, since it's a record count and is working, now that I've removed the type assignment.

    However, when I had a breakpoint in and hovered over the variable, it displayed 93^. Is a caret some sort of shorthand for a specific type? If so, what type?

    Thanx for your assistance Karen, I truly appreciate it.

    Thursday, February 27, 2020 9:16 PM
  • Finally found that the type for a 64-bit system is LongLong. So, I set that when I declared the variable and now it works.

    Thanx, everyone, for your assistance!

    • Marked as answer by Adam Quark Wednesday, March 4, 2020 3:55 PM
    Wednesday, March 4, 2020 3:55 PM
  • Finally found that the type for a 64-bit system is LongLong. So, I set that when I declared the variable and now it works.

    Thanx, everyone, for your assistance!

    For the record, there is little gain from building for 64bit, never have since starting with .NET.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 4, 2020 4:44 PM
    Moderator
  • For the record, there is little gain from building for 64bit, never have since starting with .NET.

    It was code written by someone else and dumped on me. When we moved to new computers, the process using that code stopped functioning and I had to find a solution.

    So, now it works and everyone's happy again. Yay!

    Wednesday, March 4, 2020 8:13 PM
  • For the record, there is little gain from building for 64bit, never have since starting with .NET.

    It was code written by someone else and dumped on me. When we moved to new computers, the process using that code stopped functioning and I had to find a solution.

    So, now it works and everyone's happy again. Yay!

    Cool :-)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 4, 2020 8:24 PM
    Moderator