Access Not allowing INSERT or UPDATE of null values in SQL-Server column (Access Run-time Error 3162)

Question

Hello the wide world of stackoverflow,

ISSUE:
I am currently working on developing a sql-server database that uses access forms to get and insert data. Prior to yesterday everything was working fine. The issue now is that I am getting the following error when trying to run my insert query.

Access Runtime Error '3162': You tried to assign the Null value to a variable that is not a Variant data type.

ISSUE DETAILS:
This seems to be a result of Access not letting me INSERT or UPDATE null values in a SQL table column that does allow nulls.
Further supporting this if I run the same queries in Microsoft SQL Server Management Studio I am able to INSERT and UPDATE Null values.
You can see the INSERT query I am currently using under OLD ISSUE DETAILS.

SOLUTION 4/24/19 1:35 PM PST:
It seems that the access table link was not using the latest version of the SQL table. After refreshing the link everything worked as it should

OLD ISSUE DETAILS:
UPDATE: 4/24/19, 10:33 AM PST: Realized I forgot to provide where the error happens. The error occurs in the InsertProject function at QDF.Execute

As far as I have seen I am not doing this. All of the table columns that can be null are set as such and the variables inside of vba seem to be set correctly from what I can see.

I have provided the code, queries, and other information that are involved with this issue.

Here is the query I am using:


    INSERT INTO ci_project 
    (project_id, 
    project_group, 
    project_projecttype, 
    project_hasmilestones, 
    project_hasgantt, 
    project_category, 
    project_difficulty, 
    project_notes, 
    project_completiondetails, 
    project_statuscode, 
    project_inprogressdate, 
    project_completeddate, 
    project_datecreated, 
    project_lastupdated, 
    project_canceleddate, 
    project_isActive) 
    VALUES (@projectID
    ,@projectGroup
    ,@projectType
    ,@projectHasMilestones
    ,@projectHasGantt
    ,@projectCategory
    ,@projectDifficulty
    ,@projectNotes
    ,@projectCompletionDetails
    ,@projectStatus
    ,@projectDateInProgress
    ,@projectDateComplete
    ,@projectDateCreated
    ,@projectDateUpdated
    ,@projectCanceledDate
    ,@projectIsActive);

Here is the function I made to make it easier to call it multiple times:


    'This is located in another Module called OtherFunctions
    Dim dbs as DAO.Database

    Public Function InsertProject(projectID As String, _
                                  projectGroup As Integer, _
                                  projectType As Integer, _
                                  projectHasMilestones As Boolean, _
                                  projectHasGantt As Boolean, _
                                  projectCategory As Integer, _
                                  projectDifficulty As Integer, _
                                  projectNotes As Variant, _
                                  projectCompletionDetails As Variant, _
                                  projectStatus As Integer, _
                                  projectDateInProgress As Variant, _
                                  projectDateComplete As Variant, _
                                  projectCanceledDate As Variant, _
                                  projectIsActive As Integer)

        OtherFunctions.Initialize

        Dim QDF As DAO.QueryDef

        If FindQuery("InsertProject") = True Then OtherFunctions.dbs.QueryDefs.Delete "InsertProject"

        Set QDF = OtherFunctions.dbs.CreateQueryDef("InsertProject", SQLInsertProject)

        QDF.Parameters("@projectID").Value = projectID
        QDF.Parameters("@projectGroup").Value = projectGroup
        QDF.Parameters("@projectType").Value = projectType
        QDF.Parameters("@projectHasMilestones").Value = projectHasMilestones
        QDF.Parameters("@projectHasGantt").Value = projectHasGantt
        QDF.Parameters("@projectCategory").Value = projectCategory
        QDF.Parameters("@projectDifficulty").Value = projectDifficulty
        QDF.Parameters("@projectNotes").Value = projectNotes
        QDF.Parameters("@projectCompletionDetails").Value = projectCompletionDetails
        QDF.Parameters("@projectStatus").Value = projectStatus
        QDF.Parameters("@projectDateInProgress").Value = ConvertDateToUnix(projectDateInProgress)
        QDF.Parameters("@projectDateComplete").Value = ConvertDateToUnix(projectDateComplete)
        QDF.Parameters("@projectDateCreated").Value = ConvertDateToUnix(Now())
        QDF.Parameters("@projectDateUpdated").Value = ConvertDateToUnix(Now())
        QDF.Parameters("@projectIsActive").Value = projectIsActive
        QDF.Parameters("@projectCanceledDate").Value = ConvertDateToUnix(projectCanceledDate)

        QDF.Execute

        If FindQuery("InsertProject") = True Then OtherFunctions.dbs.QueryDefs.Delete "InsertProject"

        Set QDF = Nothing

    End Function

Here is where I am calling the function:


    'These are set in the same sub as the insert project call
    Dim projectID As String
    Dim CancelDate As Variant
    Dim canceledStatus As Integer

    'These are located in a different module called OtherFunctions
    Public IDEASUGGESTION_HASGANT As Boolean
    Public IDEASUGGESTION_HASMILESTONES As Boolean
    Public IDEASUGGESTION_PROJECTTYPE As Integer


    ' /\/\/\ THERE IS CODE ABOVE THIS /\/\/\
        canceledStatus = 12
    If Me.IdeaStatus = canceledStatus And DatabaseQueries.CheckIdeaSuggestion(Me.IdeaID) = True Then
       CancelDate = Now()
       If MsgBox("Are you sure you want to do this? Canceling a idea will make it un-editable.", vbYesNo) = vbYes Then
           GoTo IdeaCancel
       Else
           GoTo GotoEnd
       End If
    ElseIf Me.IdeaStatus = canceledStatus And DatabaseQueries.CheckIdeaSuggestion(Me.IdeaID) = False Then
       MsgBox "You cannot cancel an idea that does not exist.", vbExclamation
       CancelDate = Null
       GoTo GotoEnd
    Else
       'other code run here not pertaining to the insert
    End If

    Call DatabaseQueries.InsertProject(
         projectID, _
         Me.IdeaGroup, _
         OtherFunctions.IDEASUGGESTION_PROJECTTYPE, _
         OtherFunctions.IDEASUGGESTION_HASMILESTONES, _
         OtherFunctions.IDEASUGGESTION_HASGANT, _
         Me.IdeaCategory, _
         Me.IdeaDifficulty, _
         Null, _
         Null, _
         Me.IdeaStatus, _
         Me.IdeaInprogressDate, _
         Me.IdeaCompleteDate, _
         CancelDate, _
         1)

    ' \/\/\/ THERE IS CODE BELOW THIS \/\/\/

When I am running this, these are the form values:


    Me.ideaID = Null
    Me.IdeaGroup = 1
    Me.IdeaCategory = 2
    Me.IdeaDifficulty = 1
    Me.IdeaStatus = 1
    Me.IdeaInprogressDate = Null
    Me.IdeaCompleteDate = Null
    OtherFunctions.IDEASUGGESTION_PROJECTTYPE = 1
    OtherFunctions.IDEASUGGESTION_HASMILESTONES = False
    OtherFunctions.IDEASUGGESTION_HASGANT = False

The Table structure is:

    Column Name                 Data Type   Can be Null
    project_id                  varchar(45) No
    project_group               int         No
    project_projecttype         int         No
    project_hasmilestones       bit         No
    project_hasgantt            bit         No
    project_category            int         No
    project_difficulty          int         No
    project_notes               text        Yes
    project_completiondetails   text        Yes
    project_statuscode          int         No
    project_inprogressdate      bigint      Yes
    project_completeddate       bigint      Yes
    project_datecreated         bigint      No
    project_lastupdated         bigint      No
    project_canceleddate        bigint      Yes
    project_isActive            int         No

My apologies for the wall of code blocks.

If anyone knows why or can figure out why this error is happening I would be very grateful.

UPDATE 4/24/19 11:10 AM PST: Based on commends from HansUp I created an alternate insert method using the RST.AddNew method More details can be found here. After doing this I found that the variable that is causing grief is projectCanceledDate in the InsertProject function. Only issue is I don't know why, the variable is defined as a Variant.

UPDATE 4/24/19 11:43 AM PST: After doing some more testing. I have discovered that I am able to insert and update NULL values for project_cancleddate inside of the Microsoft SQL Server Management Studio.

Why is INSERT action needed? Are you not using bound forms? Step debug, which variable is throwing the error? — June7, Apr 24 '19 at 17:08
@June7 There are multiple tables involved in the insert this is just one of them that is having issues. I also need to use unbound columns since I am changing the format of the microsoft time code to the unix time code which, as far as I am aware, you cannot display as a date. — EliSauder, Apr 24 '19 at 17:12
That error means that a primary key field, or required field, is trying to be assigned a NULL value and it's not happy. I would start debugging with ensuring the keys you're INSERTing are all populated with values, then work your way to required fields. — Fink, Apr 24 '19 at 17:17
Does ideaID feed projectID variable? projectID expects a string yet you say ideaID is Null. Only Variant type can hold null. Advise again, step debug. — June7, Apr 24 '19 at 17:17
@Fink I have gone through all of the fields in the table that I am inserting to and made sure that all of the values that are required (NOT NULL) are being inserted. — EliSauder, Apr 24 '19 at 17:19
@June7 Not quite, projectID is fed by a another method which gets values from another table that contains the counter for each idea. — EliSauder, Apr 24 '19 at 17:24
You can pinpoint the problem value if you use the DAO [Recordset.AddNew method](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-addnew-method-dao). And [here is an example](https://stackoverflow.com/a/20332327/77335) from a Stack Overflow answer. — HansUp, Apr 24 '19 at 17:36
I had a similar problem, i ended up using DAO.recordsets to load data in, and it worked that way. i never figured out why i was getting the error. — KySoto, Apr 24 '19 at 17:38
@HansUp So replace QDF.Execute with Set Recordset = QDF.openrecordset and then add recordset.addnew? Oh the link you gave is for adding data using Recordset. my bad. — EliSauder, Apr 24 '19 at 17:38
What does ConvertDateToUnix do with a Null input value? Does it return Null? — donPablo, Apr 24 '19 at 17:41
@donPablo Yes, The function itself is set as Variant so if it receives null it returns null. — EliSauder, Apr 24 '19 at 17:42
@HansUp I have tested what you recommended and found that it is the projectCanceledDate variable in the InsertProject function that is giving me grief. The issue is it says it needs to be a Variant data type but it already is. — EliSauder, Apr 24 '19 at 18:08
@June7 Using HansUp's method (using RecordSet.AddNew) I was able to pinpoint the variable causing the error. It is projectCanceledDate in InsertProject. Issues is it is already set to Variant. — EliSauder, Apr 24 '19 at 18:26
OK, at least you've pinpointed the problem field and value; that's progress. :-) Unfortunately, I can't say why it's a problem. Can you add the row if you substitute a non-Null value for that field? If so, can you then update that new row replacing the field value with Null? — HansUp, Apr 24 '19 at 18:27
@HansUp Yes it is progress. I am able to insert it when it is not null. However I am unable to update it to null afterwards. This seems to be pointing to the actual table column not allowing nulls. However I have checked to make sure that it does. — EliSauder, Apr 24 '19 at 18:34
@HansUp There are no constrains added to the table other then a few foreign keys, none of which point to the project_canceleddate column. I can also update it or insert it if I am directly in MSSMS. — EliSauder, Apr 24 '19 at 18:38
`BIGINT`? Which version of Access are you using? That's only supported on recent updates of Access 2016 and on Access 2019. Also, which ODBC driver? And have you refreshed the linked table? — Erik A, Apr 24 '19 at 19:01
@ErikA I am running on the latest version from office 365 (I believe). The database itself is also located in a SQL-Server if that has any implication on it. — EliSauder, Apr 24 '19 at 19:06
@HansUp Thank you for all your help. :-) Turns out it was just that the access table link needed to be refreshed. Still not sure why since I don't think I ever set the table to be NOT NULL. — EliSauder, Apr 24 '19 at 20:37
@HansUp Ah, my apologies. I am still pretty new to Stack overflow. Thank you again for your help, It was a pleasure. :) — EliSauder, Apr 25 '19 at 22:59

score 1 · Accepted Answer · answered Apr 24 '19 at 20:32

After doing some more experimentation I found the problem. It seems that a change in the SQL server had not been reflected in the access database (namely making the project_canceleddate accept nulls). After refreshing the link on the ci_project table everything worked fine.

score 0 · Answer 2 · answered Apr 24 '19 at 18:05

In the Call InsertProject, the fields projectID and CancelDate are not given any values. What is used for them?

Add this simple code to look at what is there in the parameters.

Dim parm As DAO.Parameter
For Each parm In QDF.Parameters
    Debug.Print parm.Name, parm.Value
Next parm

Here is what I got

  @projectID                  MyProjId
  @projectGroup               1
  @projectType                1
  @projectHasMilestones       0
  @projectHasGantt            0
  @projectCategory            2
  @projectDifficulty          1
  @projectNotes               Null
  @projectCompletionDetails   Null
  @projectStatus              1
  @projectDateInProgress      Null
  @projectDateComplete        Null
  @projectDateCreated         1556103601
  @projectDateUpdated         1556103601
  @projectCanceledDate        Null
  @projectIsActive            1

projectID is based on another method which pulls values from a counter table that I have. It concatenates different values Prefix + Counter + Suffix and then returns that string. I can add that function to the question if you want to see it. CancelDate is set to either Now() or Null depending on values entered in the form. I have provided that logic in the question right under "Here is where I am calling the function:" — EliSauder, Apr 24 '19 at 18:17
I do like that loop to view a QueryDef's params. Thank you for posting that. — EliSauder, Apr 24 '19 at 18:21

Access Not allowing INSERT or UPDATE of null values in SQL-Server column (Access Run-time Error 3162)

2 Answers2

Linked