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:

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
setting the Default Value of the PackID field in Access to "" - it behaves as though "" is a NULL
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?

It's probably a thing in Access to make your life "easier" to send NULLs for a blank string, because in the bad old VBA days, there weren't good ways to deal with NULL values. It is hacky but how about a single space, then trim everything back on its way in? — Jeremy
– Jeremy, Commented Apr 16, 2015 at 13:41
Do you have any control over the SQL server? Is it an option to drop a trigger in there, to convert NULLs to empty strings before insert? (Note: I haven't tested this, but if it isn't an option, then there's no point in seeing if it works.) — Lynn Crumbling
– Lynn Crumbling, Commented Apr 16, 2015 at 13:44
Perhaps it would be useful if you update how you link SQL tables (suppose not adp project) - SQL Server Native Client ODBC driver? — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Apr 16, 2015 at 14:12

knot22 · Accepted Answer · 2015-04-17 15:58:29Z

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.

edited Apr 17, 2015 at 15:58

answered Apr 16, 2015 at 17:55

knot22

2,7985 gold badges36 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vojtěch Dohnal Over a year ago

So for me it is clear: never create app in Access with SQL Server database... Sadly they have dropped ADP support in Access 2013.

knot22 · Accepted Answer · 2015-04-17 15:47:39Z

0

An alternative solution to this problem (no Default Value in text box Properties for PackID).

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(PackID.Value) Or PackID.Value = "" Then
        PackID.Value = " "
    End If
End Sub

This still uses the concept of passing SQL Server " " (read this as quote space quote) as the field's value. On the UI side, this is an alternative to setting a default value for the field.

answered Apr 17, 2015 at 15:47

knot22

2,7985 gold badges36 silver badges62 bronze badges

Collectives™ on Stack Overflow

VBA empty string for insert into SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related