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

Question

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": "[email protected]",
      "homeEmail": "[email protected]",
      "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.

Tim Williams · Accepted Answer · 2025-03-20 22:49:49Z

2

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

answered Mar 20 at 22:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

3 Comments

5uperBonbon Mar 21 at 15:58

Oh my gosh, it worked!!!!! THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!! YOU ROCK, TIM!!! THANK YOU!!!!!!!!!!!! I am so grateful!!!!!!

5uperBonbon Mar 21 at 15:58

Seriously. Thank you!!!!!!!

Tim Williams Mar 21 at 15:59

Good to hear it's working out.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related