Importing >255 characters into MS Access database

Question

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.

Andre · Accepted Answer · 2023-07-05T07:59:47.110

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

That worked perfectly, thank you! – DeepFriar Jul 06 '23 at 03:50 — DeepFriar, Jul 06 '23 at 03:50

score 0 · Answer 2 · answered Jul 05 '23 at 05:48

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

answered Jul 05 '23 at 05:48

Gustav

53,498
7
29
55

I added a declaration for `notessplit1` but I still get the above error. No particular reason its `notes_split1`, just because all other fields within the table that have spaces I've used _ so kept the naming convention. The fields accept empty strings because the 14 times it runs before it reaches the entry with >255 characters those fields work fine. Just in case though I changed those fields to Null and still get the same error once it reaches the split script. – DeepFriar Jul 05 '23 at 06:42

rotabor · Answer 3 · 2023-07-05T07:39:07.603

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.

edited Jul 05 '23 at 07:39

answered Jul 05 '23 at 06:14

rotabor

561
2
10

Notes field is set to Long but I still get the error "9271" Invalid Property Value when it reaches the note larger than 255 characters. – DeepFriar Jul 05 '23 at 06:39
I just tested it. It's possible to put a long string into 'Long Text' typed field. Check your db once more. – rotabor Jul 05 '23 at 07:33
1

The problem is not the table, but the LongText parameter of the INSERT query. – Andre Jul 05 '23 at 07:54
@Andre It works as depicted using 'RecordSet.AddNew' – rotabor Jul 05 '23 at 10:51

Importing >255 characters into MS Access database

3 Answers3