VBA JSON Parser "datatype mismatch" error importing into MS Access table

I have used the VBA JSON library (https://github.com/VBA-tools/VBA-JSON/releases) to attempt to parse a json file into an access table.

The code I use is:

Private Function JSONImport()

    Dim db As database, qdef As QueryDef
    Dim FileNum As Integer
    Dim DataLine As String, jsonStr As String, strSQL As String
    Dim p As Object, element As Variant

Set db = CurrentDb

    ' READ FROM EXTERNAL FILE
FileNum = FreeFile()
    Open "M:\ImportBamboo\Emp.json" For Input As #FileNum

    ' PARSE FILE STRING
    jsonStr = ""
    While Not EOF(FileNum)
        Line Input #FileNum, DataLine

        jsonStr = jsonStr & DataLine & vbNewLine
    Wend
    Close #FileNum
    Set p = ParseJson(jsonStr)
    'MsgBox jsonStr
    
    ' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE
    For Each element In p
        strSQL = "PARAMETERS [firstName] Text(255), [lastName] Text(255), " _
                          & "[SSN] Text(255), [maritalStatus] Text(255); " _
                  & "INSERT INTO BambooEmpTest (FirstName, LastName, SSN, MaritalStatus) " _
                          & "VALUES([firstName], [lastName], [SSN], [maritalStatus]);"

        Set qdef = db.CreateQueryDef("AddEmp", strSQL)

        qdef!FirstName = element("firstName")
        qdef!LastName = element("lastName")
        qdef!SSN = element("ssn")
        qdef!MaritalStatus = element("maritalStatus")

        qdef.Execute
    Next element

    Set element = Nothing
    Set p = Nothing
End Function

The function fails at qdef!FirstName = element("firstName") due to datatype error. When I examine the query: "AddEmp", it appears the source for the insert is completely blank. When I check the jsonStr with the messagebox, the json string is there. I've never worked with json before so I'm above my head.

Of note is that the source json file has more than the four test columns, above, but I wanted to test it before adding all the params.

Here is what the json file looks like with names changed to protect the innocent- It is a single layer file:

"data": [
    {
      "firstName": "Fred",
      "lastName": "Smith",
      "dateOfBirth": "1980-02-04",
      "ssn": "666-66-6666",
      "maritalStatus": "Married",
      "addressLineOne": "666 Delancey Street",
      "addressLineTwo": "Apt 666",
      "city": "San Francisco",
      "state": "CA",
      "zipcode": "94107",
      "workPhone": "415-555-6666",
      "mobilePhone": "415-555-6666",
      "homePhone": null,
      "email": "test@test.com",
      "homeEmail": "test@gmail.com",
      "hireDate": "2024-09-16",
      "compensationPayType": "Salary",
      "compensationPayRate": "1 USD",
      "compensationPaidPer": "Year",
      "jobInformationDivision": "10",
      "compensationPaySchedule": "Salaried Employees",
      "employmentStatusEffectiveDate": "2024-09-16",
      "employmentStatus": "Full-Time, Salaried, Exempt",
      "employmentStatusTerminationType": null,
      "supervisorEid": "188",
      "supervisorName": "Smith, Joe",
      "terminationDate": null,
      "preferredName": "Freddie",
      "employeeNumber": "10-0091303",
      "status": "Active",
      "compensationEffectiveDate": "2024-09-16",
      "jobInformationDepartment": "Operations",
      "compensationOvertimeStatus": "Exempt"
    },
    
      ...more records here...

  ]
} 
etc

Here is what the test import table looks like with just four columns:

Thank you for any help you have!!!!!!

-Bonnie

I tried to run it, received the error. I was expecting the json information to parse but the source appears to be blank.

When you run

Set p = ParseJson(jsonStr)

you get back a Dictionary object in p. That dictionary has a single key "data", and the value for that key is a Collection of dictionary objects, each representing an employee record.

So, you need to loop over that collection stored under "data":

For Each element In p("data")
    'extract data from `element("firstName")` etc etc 
Next element