MSAccess and a really weird behaviour with distinct

By | June 25, 2010

Recently I started working with MS Access databases, I know not the best choice. I ran into a really weird problem with the database, which I didn’t expect.

I have a relativly simple select query which contains a distinct. Something like “select distinct id, content from table”. Nothing fancy so you’d expect the content of the table excluding all duplicates.

But this is also where the weirdness begins with MS Access. I was using this type of query to select data for my search engine. But some data wasn’t included for some reason.

After a lot of debugging my queries and code it turned out that MS Access ODBC was only returning the first 255 characters of a MEMO field. So I thought the driver was to blame, until I ran the same query in MS Access itself. As it turns out Access was the one truncating MEMO fields to VARCHAR(255) fields when using a DISTINCT in a query.

Leave a Reply