Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Excel cross post, ADO/Jet issue

    I posted this in the excel forum as that is certainly most appropriate, but I figured I might get a nibble or two here as the ADO and Jet components have bearing on access as well:
    '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
    Last edited by Teddy; 01-04-05 at 11:58.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i never solved it in DAO either! A makes too many "intelligent" decisions.
    i attack the XLS cell-by-cell from code or import a CSV (all columns=Text)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by izyrider
    i never solved it in DAO either! A makes too many "intelligent" decisions.
    i attack the XLS cell-by-cell from code or import a CSV (all columns=Text)

    izy
    Current work-around:

    I created a small "PrepSource()" sub that opens the source and checks the first few records and inserts 0 if they are null. 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 consistancy and speed I'm going to get.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •