Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max string length 256 char via ODBC... any fixes? (Access 2003 in WinXP SP

    The XP ODBC driver seems to be truncating individual text (string) fields from my database to 256 characters (whether accessing Access 2003 database using Hyperion BI or cut n' pasting data from Access tables into Excel). Does anyone know of a fix for this (e.g., newer ODBC driver, configuration change) or is it a standard ODBC feature? Thanx in advance for any help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    Text fields in Access have a maximum length of 255 characters. Memo fields can be much longer. The ODBC driver for Access from Microsoft will preserve long memo fields - I just checked in Excel.

    You can use the Microsoft Data Access Components (MDAC) Utility: Component Checker to determine the version of MDAC you have. The most recent version for Windows XP SP-2 is MDAC 2.8 SP1.

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    Hi HansV, thanks for your reply! The fields I'm trying to copy are memos but I'm getting truncation to 255 characters when (a) copying from Access 2003 to Excel 2003, ([img]/forums/images/smilies/cool.gif[/img] copying from Access 97 to Excel 97 or querying Access 97 via Hyperion BI. Interestingly enough the Component Checker (thanks for the link!) reports that I'm running MDAC 2.7 on XP SP1 on both the machines to which I have access (one running Office 97, the other running Office 2003)... might this be an answer? Kind regards, Mal.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    2.7 is the correct version for Windows XP SP-1 (I recommend updating to Windows XP SP-2, by the way, but that doesn't have anything to do with the truncation problem, as far as I can see).

    When I copy/paste from an Access table to an Excel worksheet, I see truncation too. But copy/paste doesn't use the ODBC driver. Instead, use Data | Import External Data | New Database Query... from Excel, or use File | Export... from Access.
    Oh, and do you have a format set for the memo field? That is known to truncate to 255 characters.

  5. #5
    New Lounger
    Join Date
    Dec 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    Hi Hans,

    Running out of luck on this one, it seems... as you observed, importing data into Excel doesn't truncate memo fields, nor does exporting from Access... and Hyperion, though capable of retrieving text fields to (potentially) any length (I've been bothering their tech support about this), is still hitting truncation to 256 characters. If it's not the ODBC driver I can't think what else it would be... <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    Thanks again for your help!
    Mal

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    I don't know anything about Hyperion. If it comes with a proprietary ODBC driver for Access, the problem is on their side, and we cannot do anything about it. If it uses Microsoft's ODBC driver for Access, I don't know what goes wrong, for this driver supports long memo fields.

  7. #7
    New Lounger
    Join Date
    Dec 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    More testing elicits the curious result that if you put a query-side function into Hyperion the query goes and truncates all long strings in the results to 256 characters! <img src=/S/confused.gif border=0 alt=confused width=15 height=20> So Access, ODBC and Bill Gates are blameless! Sorry this has been a bit of a wild goose chase, but at least I know that the driver side of things is working ok. Thanks very much for your help!
    Mal (feeling silly) <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    I don't know anything about Hyperion either, but is it possible to export the Access information to a CSV or similar text file and then import it into Hyperion? If that fails to work it may be that Hyperion is not capable of handling text fields greater than 256 characters. Based on a quick perusal of their website, it appears the focus is more on numerical and analytic functions than on text reporting.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    I don't know if you're connecting to the Access database through Hyperion, or IOW how you are importing --
    Some folks here use BrioQuery, which is now a Hyperion product, and it comes with a sample database & connections to that database -- you might see if you can add a memo field to that database (if none exists) and connect to it, and see how the data arrives. If this is how you are "importing" the data, then, disregard my suggestion (as you've already tested it!). I don't have it loaded, or I'd test it out.
    thx
    Pat

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max string length 256 char via ODBC... any fixes? (Access 2003 in WinX

    I just tested a 'way older version of Brioquery using a 'way older (Access 97) database, and the memo came through fine -- I couldn't display it well in the package (never learned it real well) -- so, they way I know it came through is that I exported it to text, and the memo field appeared fully. However, when I exported it to Excel, the memo truncated to the 255 level -- just as an FYI. I don't know if BI (Business Intelligence?) is similar or the same to Brioquery, but the answer might be in how capable the import/export might be from certain other packages -- don't know if that's helpful.
    thx
    Pat

Posting Permissions

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