ADODB recordset on excel table return null value when it shouldn't

Hi!

I have an excel workbook full with VBA code to get several things done. I need frequent querys to data tables hosted inside the workbook, no RDBMS system in use this time.

However, I've found very convenient to solve this querys using ADODB recordsets. I implement this kind of recordsets with this Function in one of my utility modules

Please Login or Register  to view this content.

And I use this function in code like this:

Please Login or Register  to view this content.

This have been working like a charm for months now, but a couple of days ago I suddenly found a problem. There are some recordsets that return null values for some fields even when the actual value of the cell in the table is not null.

I've made several tests and google for similar issues but found nothing.

Have anyone a clue of what the hell is happening here?

Thank you in advance

Hi Trevor, thank you for your attention.

I've upload a sample wkb, it has just one module with two methods. If you run the one called "test_sqlSelect", it will show you the problem.

Regards

Any ideas?

I'd really appreciate any help

You have mixed data types (numbers and text) within the same column. That has always been a problem for ADO (since a proper database won't allow you to do that), even if you use IMEX=1. You should convert the numbers to text by prefixing them with an apostrophe and then your code should work.

Thank you very much rorya!

You are absolutely right.

I'd also suggest not using ado on an open workbook, there's a known bug which causes memory leaks. If you can live with it and haven't noticed any side effects then it may not be the end of the world, just be aware of it

Ok Kyle, I'll keep an eye on it. I haven't notice any side effects so far, as you say.

Thank you for your warning

No problem, if you want a bit more information: https://support.microsoft.com/en-us/kb/319998