Data truncated in MS Access UNION query

[RLau]

Hi All,

I'm using ODBC to link my Access 2000 database tables to MS SQL Server 2000 database and create queries and reports in Access. When I use a single query to retrieve a text field with large data, all data in the field are retrieved. However, when I use the same query in a UNION query, the data of the same field are truncated ( may be 255 characters but I didn't actually count ), but this seems to happen on certain records only.

Please help. Thanks

[MMcCarthy]

Hi All,

I'm using ODBC to link my Access 2000 database tables to MS SQL Server 2000 database and create queries and reports in Access. When I use a single query to retrieve a text field with large data, all data in the field are retrieved. However, when I use the same query in a UNION query, the data of the same field are truncated ( may be 255 characters but I didn't actually count ), but this seems to happen on certain records only.

Please help. Thanks

The short answer to this is Access Memo fields don't preform well in Union queries.

Have a look at this tutorial.

Mary

[RLau]

Thanks, Mary.

The field concerned is a text field of size for 5000 characters in a MS SQL Server 2005 database. My MS Access is actually Access 2002. Does the UNION problem happen to large text field as well ?

The question is, the same set of data works fine in the same environment when retrieved by a single query. When I modified the query to a UNION query with the two queries a replica of the original one that works and the difference of the two queries is the WHERE parts. The puzzle is that happens to certain records only but other records with large text data are OK.

Still scratching my head ....

The short answer to this is Access Memo fields don't preform well in Union queries.

Have a look at this tutorial.

Mary

[MMcCarthy]

Thanks, Mary.

The field concerned is a text field of size for 5000 characters in a MS SQL Server 2005 database. My MS Access is actually Access 2002. Does the UNION problem happen to large text field as well ?

The question is, the same set of data works fine in the same environment when retrieved by a single query. When I modified the query to a UNION query with the two queries a replica of the original one that works and the difference of the two queries is the WHERE parts. The puzzle is that happens to certain records only but other records with large text data are OK.

Still scratching my head ....

The large text field in SQL server is translated by Access as a memo field. Access only has two options Text field (max char size of 255) and Memo field. As the tutorial says anything outside of a basic query on this field just doesn't really work in access.

Mary

[RLau]

Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau

The large text field in SQL server is translated by Access as a memo field. Access only has two options Text field (max char size of 255) and Memo field. As the tutorial says anything outside of a basic query on this field just doesn't really work in access.

Mary

[MMcCarthy]

Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau

The problem is really in the use of Memo fields as it is not just UNION queries that are affected. To the best of my knowledge M$ has made no effort to address this. It just doesn't handle text fields above 255 characters in any managable way.

Mary

[NeoPa]

Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau

They would see it as more of a feature than a problem.
Using Memo type fields in these circumstances (assuming it were supported) would be such a resource and processor overhead that implementing it is probably not such a great idea.
No one (IMHO) should be thinking of using text fields of that size anywhere that needs any processing on them (Sorting, Filtering etc - UNION has an implicit sort) except in extremis. Access simply forces a better design on the developer.