Question 200876 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Importing >255 characters into MS Access database

I’m currently working on a script where I import a .json file into a microsoft access database, so far each field enters without issue, except for one field where the characters are more than 255.

To overcome this, i’ve tried writing in a check on that field to see if the entry is more than 255 characters, and if it is, split it across two other columns.

It all seems to run fine, but when it goes to execute the above, I get an error ‘3001’ invalid argument.

The two additional columns (notessplit1 and notesplit2) are listed in the strsql parameters.

        If Len(element("notes")) > 255 Then
            Dim notesValue As String
            notesValue = element("notes")
    
        ' Split the notes value into two parts
            Dim maxLength As Integer
            maxLength = 255
            Dim notessplit As String
            Dim notessplit2 As String
    
            If Len(notesValue) > maxLength Then
                notessplit1 = Left(notesValue, maxLength)
                notessplit2 = Mid(notesValue, maxLength + 1)
            Else
                notessplit1 = notesValue
                notessplit2 = ""
            End If
    
            qdef!notes_split1 = notessplit1
            qdef!notes_split2 = notessplit2
            qdef!notes = ""
        Else
            qdef!notes = element("notes")
            qdef!notes_split1 = ""
            qdef!notes_split2 = ""
        End If

     QDEf.execute

I was expecting that whenever the script comes across an entry greater than 255 characters, the "notes" column would be blank, and the entry would be spread across the two "notessplit" columns.

Instead I just get error 3001: invalid argument.

All other fields up to this point enter fine, including where the notes are less than 255 characters.

Tags:

3

Answers


  1. 0

    You miss declaring notessplit1:

    Dim notessplit1 As String

    And why qdef!notes_split1, not qdef!notessplit1?

    Also, those fields may not accept empty strings. If so:

            qdef!notes = element("notes")
        qdef!notes_split1 = Null
        qdef!notes_split2 = Null
    Login or Signup to reply.
  2. 0

    Another way is to change table’s design. Change the type of ‘notes’ field from ‘Short Text’ to ‘Long Text’. It accepts up to 63999 characters.

    Demo

    Login or Signup to reply.
  3. 0

    You are using a querydef with parameters to add records. As you have found, LongText parameters do not work. They just don’t.

    A better approach for your use case is to use DAO.Recordset.AddNew.

    It works for all data types, is easier to read and maintain, and for row-by-row additions as you need here, it’s even faster than an INSERT query.

    Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("TargetTable", dbOpenDynaset)

'--- Loop over JSON data starts here

rs.AddNew

'... more columns
rs!Notes = element("notes")
'... more columns

rs.Update

'--- Loop over JSON data ends here

rs.Close
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search