Hey guys, I'm running into a roadblock with an ADO issue.

'm having issues with null values in excel when constructing an ADO 2.7 recordset from another spreadsheet. Basically if the first few values in a given column are null, the ENTIRE column returns null.

Initiall I assumed this was due to ado's attempt to discern the datatype from the first 8 rows of data. To attempt to circumvent the issue, I set IMEX=1 and HDR=Yes, but to no avail. For record, all data in the column is numeric.

I also tried formatting the entire column as text to see if it would eliminate any mixed data issues, no dice there either. As a last ditch effort I attempted to convert null to the string "blank" during the open method of the recordset. It looks like the datatype is "guessed" before the query is actually run though, as this had no effect other then returning "blank" for every record instead of null.

I'm thinking the issue is specific to the way ado/excel handles null. I haven't found any resource addressing what ado will do if the entire first 8 entries in a given column are null.

Hopefully someone can shed some light on this for me?

TIA folks!

-Teddy

Cheat. Construct the query so that the first few values in that column aren't NULL.

-PatP

That's my current plan. Until I can figure this out, I'm writing a procedure that first opens the target source and preps it for import. That's awfully slow though. I suppose I could evaluate just the first 8 for null and throw a numeric 0 in there. That would be enough to force the datatype I think...


*update:

I ended up going a little of both here... I created a small "PrepSource()" sub that opens the source and checks the first few records for null and inserts 0 if they are. Then in the open method for the recordset, I use an iif() statement to set the remaining nulls to zero. I think for now that's the best compromise of consitancy and speed I'm going to get.

Pat,

didn't you point this out as a known M$ problem....

Ya gotta love M$...bullet proof

As always heh.

There's a good deal of documentation on this sort of error with mixed datatypes. Unfortunately there was a lack of information regarding expected behavior if the ENTIRE sample is null. Workarounds ahoy!