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

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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, ( 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.

Reply | Quote

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.

Reply | Quote

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…

Thanks again for your help!
Mal

Reply | Quote

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.

Reply | Quote

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! 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)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote