setting .DefaultValue, then importing .CSV

[TheAntiGates]

I am trying to set defaultvalue to prevent null from existing in a table. I can't get it to work. I tried variation 1., 2. and the final code shown below. Why does the code fail as the comments indicate, and is this (including setting .required) the way to prevent importing nulls into my table?Sub foo()
Dim i As Long, td As TableDef, db As Database
Set db = CurrentDb
Set td = db.TableDefs("mytable")
For i = 0 To td.Fields.Count - 1
td.Fields(i).Required = True
td.Fields(i).AllowZeroLength = True

'td.Fields(i).DefaultValue = " " '1. bombs out - hmm

'td.Fields(i).DefaultValue = "" '2. doesn't work alone, but okay if preceded by "x" line see below - hmmm

td.Fields(i).DefaultValue = "x"
td.Fields(i).DefaultValue = "" 'this bombs in a more creative way
Next i
Set td = Nothing: Set db = Nothing
End SubRunning as is doesn't bomb, and it's hard to verify that "" is actually the default value. Since .Required is true for each field, you'd think that a zero length string would be the default. But that's not how it works when I import. When I import I get an error message box:
"Microsoft Office Access was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and 0 record(s) were lost due to key violations."

If I click Yes to proceed anyway, nothing imports.

There is no primary key, no indices, and no key violation circumstances, other than the .Required that is on each field.

Could it be that setting .Required and .DefaultValue is not usable for importing data, and that its mission is simply to patrol Form entry?

EDIT: I'm on Access 03, if it wasn't evident. I thought there was an AllowNulls Property in the past!

[TheAntiGates]

Unless someone will correct me, I'll assume that setting .Required is no good for text importing when Nulls exist, Nulls can not be overridden by DefaultValue, and that the nulls must be imported as is to a NON required field and cleaned up later.

IOW, setting .Required and .DefaultValue doesn't provide a translation mechanism for handling data imports. .Default Value has no meaning with data imports. On imports, .Required is simply a traffic cop that bombs out when it finds nulls (requiring you to have to correct the source 'ere continuing); and otherwise its only mission is simply as a traffic cop that prohibits null entry when using a Form.

Unless someone corrects me.

My original quest was to intercept incoming nulls and substitute DefaultValue for them during a text import, and I conclude that the code above fails to achieve that. If there is another solution, though, that would be nice to here. There are also still code mysteries above if anyone wants to explain them.

EDIT: "hear" replaces "here" one sentence back - though no one corrected me...

[Norie]

Gates

How about just running an update query after the import?

[TheAntiGates]

Because I wanted to hit all 230 fields at once in a loop. But yes, that's where I'm heading right now :(.

EDIT: (Fields may be added and removed over time, too - but the loop wouldn't care.)

[Tommy]

I know this is not what you are asking for.

I would read in the .CSV file replace all ",," with ",x," and write the file (LOL that is of course if it is really this simple) . Then import it. In other words massage the data first to what you want. The reason I am suggesting this is because you are saying the import isn't working as you want, nor is the DefaultValue. Here is another workaround.

Public Function GetStrFile$(iFName$)
mFno = FreeFile
Open iFName For Input As #mFno
mLen& = LOF(mFno)
GetStrFile = Input(mLen, #mFno)
Close #mFno
End Function
Public Function PutStrFile(iFName$, iInfo$)
mFno = FreeFile
Open iFName For Output As #mFno
Put #mFno, , iInfo
Close #mFno
End Function
Sub aa()
mInfo = GetStrFile("C:\somfilenameandlocation")
mInfo = Replace(mInfo, ",,", ",x,")
PutStrFile "C:\somfilenameandlocation", mInfo
End Sub

[Norie]

Gates

Why do you have 230 fields in the first place?

[TheAntiGates]

Norie: because I can?
Tommy: Well that's certainly worth having. Here was my gruesome solution (don't tell any moderators - it's not VBA!). (Dohhh, you're a moderator!)
void
fix_nulls(char *sbuff)
{
int i,j;
if (!sbuff[0]) return;
j = strlen(sbuff)+1;
for (i=1; i<j; ++i) { /* Yes, start at offset 1 */
if (sbuff[i-1] != ',') continue;
if (sbuff[i] != ',') continue;
memmove(sbuff+i+3,sbuff+i,j-i);
sbuff[i]='"'; /* yeah, yeah, should use memcpy on sbuff+i */
sbuff[i+1]=' ';
sbuff[i+2]='"';
j+=3;
}
i=0; /* for breakpoint */
return;
}