Parsing JSON into Access table

Question

I'm working off a previous thread: Parsing JSON feed automatically into MS Access

But I'm getting a type mismatch error on all the qdef! lines. Here's my portion of the code:

For Each element In p
    strSQL = "PARAMETERS [id] Text(255), [class] Text(255), [exchange] Text(255), " _
                          & "[symbol] Text(255), [name] Text(255), [status] Text(255), [tradeable] Text(255), [marginable] Text(255), [shortable] Text(255), [easy_to_borrow] Text(255); " _
           & "INSERT INTO WRK_ALP_TICKER " _
                          & "VALUES(Now(), [id], [class], [exchange], [symbol], [name], [status], [tradeable], [marginable], [shortable], [easy_to_borrow]);"
    Set qdef = db.CreateQueryDef("", strSQL)

    qdef![id] = element("id")
    qdef![Class] = element("class")
    qdef![exchange] = element("exchange")
    qdef![symbol] = element("symbol")
    qdef![Name] = element("name")
    qdef![STATUS] = element("status")
    qdef![tradeable] = element("tradeable")
    qdef![marginable] = element("marginable")
    qdef![shortable] = element("shortable")
    qdef![easy_to_borrow] = element("easy_to_borrow")

    qdef.Execute
Next element

score 0 · Answer 1 · answered May 06 '20 at 21:56

Your first column is a TEXT field named ID. However, your first inserted value is a datetime, Now(). In append queries, you need to align columns either positionally or by name. Ideally, you should always specify the columns in INSERT INTO clause and not rely on implicit column order.

Therefore, specify the needed datetime column as first inserted value position. Also, consider using a saved Access query and not temporary querydef to avoid line breaks and concatenation in VBA.

SQL (save as a query object; params renamed to avoid confusion with columns)

PARAMETERS [id_prm] Text(255), [class_prm] Text(255), [exchange_prm] Text(255), 
           [symbol_prm] Text(255), [name]_prm] Text(255), [status_prm] Text(255), 
           [tradeable_prm] Text(255), [marginable_prm] Text(255), 
           [shortable_prm] Text(255), [easy_to_borrow_prm] Text(255);

INSERT INTO WRK_ALP_TICKER ([CreatedDatetime], [id], [class], [exchange], [symbol], [name],
                            [status], [tradeable], [marginable], [shortable], [easy_to_borrow])

VALUES (Now(), [id_prm], [class_prm], [exchange_prm], [symbol_prm], [name_prm], [status_prm], 
        [tradeable_prm], [marginable_prm], [shortable_prm], [easy_to_borrow_prm])

VBA

For Each element In p    
    Set qdef = db.QueryDefs("mySavedInsertQuery")

    qdef![id_prm] = element("id")
    qdef![class_prm] = element("class")
    qdef![exchange_prm] = element("exchange")
    qdef![symbol_prm] = element("symbol")
    qdef![Name_prm] = element("name")
    qdef![status_prm] = element("status")
    qdef![tradeable_prm] = element("tradeable")
    qdef![marginable_prm] = element("marginable")
    qdef![shortable_prm] = element("shortable")
    qdef![easy_to_borrow_prm] = element("easy_to_borrow")

    qdef.Execute  dbFailOnError
Next element

Thank you; that should have been an obvious fix to me. Unfortunately, it raised a new problem. Nothing happens when I run the code, and there are no errors raised in this code or in the ParseJson call. Are there any other known issues with the JsonConverter.bas code? — jrn0074, May 11 '20 at 14:57
But is data appended to your table? If run properly with appropriate error handling (i.e., **no** `On Error Goto 0` or `On Error Resume Next`), then no pop up message will be generated. — Parfait, May 11 '20 at 17:20

Parsing JSON into Access table

1 Answers1