VBA empty string for insert into SQL Server

Question

Here is the situation: There's a SQL Server database with a Microsoft Access front end. There is a table in the SQL Server database with a column called PackID which is defined as VARCHAR(6) NOT NULL (it has to be NOT NULL because it's part of a composite primary key for this table). Empty strings are legitimate in the PackID column. In fact, they occur quite often. When the user enters data in the UI for this table, and tabs through the PackID field, an error message appears: "Cannot insert the value NULL into column 'PackID'; column does not allow nulls. INSERT fails."

Things that have been tried that do not work:

1. adding a default constraint on this column in the SQL Server database with a value of '' - apparently the default is only used if the column is omitted from the INSERT statement
2. setting the Default Value of the PackID field in Access to "" - it behaves as though "" is a NULL

3. calling the following VBA code when user moves off row in UI (Lost Focus event)

   If IsNull(PackID.Value) Then
      PackID.Value = ""
   End If
   

Does anyone know how to force an empty string in Access so it's interpreted as an empty string for SQL Server and not a NULL?

Answer 1

Setting the Default Value in the text box Properties for PackID in Access to this

=" "

worked. The space between the double quotes is very important. Leaving the space out causes the insert to fail. In SQL Server LEN(PackID) returns 0. For instance:

SELECT LEN(''), LEN(' ');

both return 0. It appears as though SQL Server treats both of these as empty strings.