Removing embedded carriage returns from Microsoft Access

Carriage return. Such a wonderfully archaic term for the hidden byte that ends a line of text. My children have no idea what kind of carriage can return.

Speaking of archaic, applications like Microsoft Access have trouble with carriage returns. They can't easily be inserted into a text field, but ODBC imports from more sophisticated applications, such as FileMaker Pro, can insert carriage returns into Access fields.

Problem is, there's no easy way to remove them. Fields with embedded CRs behave oddly when edited, and exports and queries break. Search and replace won't work.

I found a method that works.

Export the key column and the troublesome field as XML (no XSD). Then use a text editor to replace every carriage return with a space. The result is a single line, but this doesn't affect XML import. Reimport the XML and the carriage returns are gone.

There are probably better methods.

Update 1/2/07: I ran into another issue where an Access field appeared empty, but it was not NULL. I used 'not null' and 'len=0' to identify these fields, then set them to NULL. Probably another character set problem. I have finally liked all the problems with creating a database that works with Outlook 2003, the PalmOS, sync to Palm, and FileMaker (via ODBC). More on that after I get some sleep.