So this is what I've come up against over and over and have never come to a good resolution.

I do have a resolution but it means having front-end code working.

The problem.

If you create a text field in a table and want it to have an empty string, then it returns Null when you try to read it in code.

If you edit the table directly, put some text in, save it by moving to another record, then move back and deleted the text then you still get the null value in code.

BUT!

If I write vbnullstring to the field from code then it's not null any more.

So to fix the null I have to use a front end to do that.

Which sucks when I copy, say a list of zip-codes or something and paste it into a table that has another text field.

I have to write code to loop through it and set it all to vbnullstring.

What I need is a way (inside the database, not from code) to tell it that an empty string is perfectly acceptable and to stop the null nonsense.

This is what I'm doing now for an existing project.

Some tables have a Definition field and others don't. But all types can be listed in this form which is why there's the "HasDictionary" thing.

This one is simple it's the only field that might have nothing in it.

But in a more normal front-end, there might be a whole lot of text fields that are created with null values.

It's actually why I stopped using all forms of data controls. I got tired of them throwing me null errors and just started rolling my own.

The question: Is there a way to have Access create the new record with an empty, but not null value? Not even a space character (which my version of Access won't let me enter a space character as the default value anyway). I'm talking about from Access, not from any front-end.

Code:
Private Sub lstTableEntries_Click()
Dim RST As DAO.Recordset
Dim SQL As String
Dim nPrimaryKey As Long
Dim nRecordcount As Long

On Error GoTo errHandler

nPrimaryKey = Itemdata(lstTableEntries)

If nPrimaryKey <= 0 Then Exit Sub

SQL = "SELECT * FROM " & cmbLookupTables.Text & " WHERE LookupID = " & nPrimaryKey

nRecordcount = OpenRST(RST, SQL, idx_Recordset_Dynaset)

If nRecordcount = 0 Then Exit Sub

With RST

  If HasDefinition(cmbLookupTables.Text) Then

    If IsNull(.Fields("Definition")) Then ' // Begin stupidity.

      .Edit

        .Fields("Definition") = vbNullString

      .Update                             ' / End of the stupids.

    End If

    txtDefinition.Text = .Fields("Definition")

  End If

End With

txtLookupEntry.Text = RST.Fields("Lookup")

lblDefinition.Enabled = txtDefinition.Enabled

cmdDeleteEntry.Enabled = lstTableEntries.ListIndex >= 0

Changed = False

Exit Sub

errHandler:

MsgBox Error & " (" & Err & ")."

End Sub