Force acceptance of a single space ' ' in a Text column? 1

[hindsjl]

I'm working with data downloaded from a company database (Oracle), which was built on a convention that a single space ' ' would be the default value for all text columns in all tables. There are _many_ of these ' ' values in the database.

When I import downloaded textfiles into Access, these single-space values get automatically converted to nulls by MS, who in their wisdom obviously know our data better than we do. This not only drives me crazy when trying to compare two fields -- one of which may now be null -- but it makes uploads back into the company database risky, as I have to be careful to restore those nulls to "correct" single-space values.

Anyone know how to disable this behavior, and get Access just to read in the damned data as received?

Thanks.
John

 

[Remou]

Is a line by line import a possibility for you?

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("impy")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("import.txt")
'Skip Header
f.ReadLine
Do While f.AtEndOfStream <> True
    a1 = Split(a, ",")
    rs.AddNew
    For i = 0 To UBound(a1)
        rs(i) = a1(i)
    Next
    rs.Update
Loop

 

[genomon]

You can try changing the Access table field properties in design view to NOT allow zero-length strings, but if the spaces are converted thru the provider it may throw an error. If you have a shot at massaging the data thru SQL on the import, you could maybe try the Replace function?

I wondered why the baseball was getting bigger.
Then it hit me.

 

[dhookom]

I would question the "wisdom" of "a convention that a single space ' ' would be the default value for all text columns in all tables". Talk about unconventional!

I would probably use Nz([FieldName," ") when uploading. Is it possible to work directly with the linked Oracle tables?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[maxhugen]

I agree with Duane. Spare yourself a lot of trouble trying to have Access use the single space, and just replace it when uploading as Duane suggested.

Max Hugen
Australia

 

[hindsjl]

Thank you all.

I was hoping for a global setting that would tell Access to "stop second-guessing and just read the file", or something like that. No luck there, but your solutions came close enough.

Remou's code has to be modified once for each table and inputfile -- though I suppose it could prompt for both somehow. But it doesn't require any specific-named-column logic, which makes it very generic to set up and maintain. And it manages to bypass Access' automatic rtrim() distortion.

BTW, had to change
a1 = Split(a, ",")
to
a1 = Split(f.ReadLine, ",")

I think the suggestion others offered of converting on the upload, as using the Nz([FieldName," ") would mostly work, but would have to be set up for each of many fields in many tables, meaning more work for me.

And it gets worse -- the source database I'm drawing from also contains some multiple-space values (' ' and ' ') distinct from the default ' ' (No, don't ask.) Once those get trimmed by Access on the import, I have no way of distinguishing them on the export.

Thanks again.
John