Access import embedded double-quote problem?

[jclarey]

I'm trying to import a comma- and double-quote delimited file to Access 97 using VBA code as follows:

DoCmd.TransferText acImportDelim, "PI Import Specification", "tblImport", "PI.TXT", False, ""

My Import Specification has:
File format = delimited
Field delimiter = comma (,)
Text qualifier = double-quote (&quot

The problem is that the data may contain embedded double-quotes in the middle of a field, for instance around "Jack" in the following record:

"1","Wednesday","My name is "Jack" Williams","55","22"

Access gives an "Unparsable Record" error and does not import this record.

I've searched Microsoft and these forums.

Proposed solutions include:
1. reading the file byte-by-byte and cleaning it up myself (yuck)
2. importing to another product first, like Excel, then to Access (yuck)
3. get double quotes removed from the input file before Access processing (not sure I can get supplier to do this)

Has anyone gotten this resolved in an easier way?

Thanks in advance for any help you can provide.

Jeff Clarey
Appleton WI