Column datatypes that will accomodate the weirder characters (line feed etc)

[ipisors]

Ok, well, sorry, but after a few years I am now "that guy". I pretty much left Access behind and have been working with almost all SQL Server / SSRS / SSIS for a few years.
Frankly, I miss Access (NOT for data storage of course !!!), but I do miss it for its awesome front end app that could be very easily hooked with SQL server for a quick 'n tidy app.

But right now I am being forced to deal with the primary thing I did NOT like using Access for - data & tables. Frustrated. First of course with the folks around me who if they'd listened to anything I'd said over the past week, we wouldn't have this problem. But of course, their emergency is my emergency, even if there is no way that makes sense on any planet, so here goes for my question:

We have columns coming from SQL Server, going out via an ETL process I built, to some text files, and ultimately ending up in someone's pet Access Database - let's call it Fido. The SQL database is a naughty enough one ... all kinds of varchar(max) (whether needed or not), and all kinds of columns built to ingest data coming in that the database designer didn't bother to talk to anyone about, so data types, lengths, precision, and constraints are mostly the results of beers and guessing. Now in his defense, he probably never guessed that, years later, someone in a position of both presumed 'wisdom' and authority, would decide to take data out of the safely protected SQL database, and put gigabytes of it in Access, so that we could begin the long, long process of fighting with the datatypes differences. The data will never quite look the same again or be accurate again, but that point seems to be lost on them (which I'm fine with at this point).

Fido is naughty too though, because HE gives me vague errors, like "unparsable error" during the text file import. Never mind that I gave my team 13 other ways to do this, they decided instead to request this process, so I am stuck trying to come up with wise ideas to support an unwise process.

I already found and stripped out a few line feeds, carriage returns, whatever (mostly proved to be char(13) in SQL, but I threw in a replace() for 12 and 10 too just for good measure).
However, Fido is still barking "unparsable error". I have the record, but of course, it's kinda hard to try to figure out which all characters might make Fido mad.


(May I just say that when you call a field TEXT, no character should be illegal in it?? And furthermore, when you [Microsoft] actually build a customer dialogue wizard to import data, and the data is perfectly delimited with pipe symbols PLUS surrounded by quotes, then ALL CHARACTERS COMING INTO A TEXT FIELD VIA THAT IMPORT PROCESS, OTHER THAN A DOUBLE QUOTATION MARK AND A PIPE SYMBOL, OUGHT TO BE 'PARSABLE' !!

Before I spend my afternoon writing a function to strip out every ascii character code that I think Fido may not like, or ripping off someone else's and then spending the afternoon re-writing it to suit me, I just had an idea. Could it be that FIDO might perhaps have reserved a bit of wisdom whereby, if I switch the column type to something else, like Memo, then "all characters except the delimiter symbol and the text surrounding quotes will be allowable? It would just make so much sense to have AT LEAST ONE DATATYPE in an entire "database" program that will accept that, but I dare only hope.

I tried to google it but to no avail. Microsoft's page about text file imports vs. an Access table read kind of like a teenager who is thinking about it for the first time. "If you strip away, ya know, WEIRD CHARACTERS, then it should be OK".

Any brilliance on this issue please share !!!

[June7]

I agree, 'should' be accepted. However, nothing can quite beat an actual test. So test an import of your data to Memo field, let us know what happens. I am betting that if Text field doesn't like it, neither will Memo.