Errors appending to a table in Access VBA

user_7554883 0
Viewed 81

I have an Access database with a VBA module that assembles a JSON string in a variable: JsonStr2 that I want to place in a simple table: JSONforAPI with two fields:

  • ID = Auto number and
  • JSON = Long String.

The JSON is valid and Jsonstr2 prints in the immediate window on Debug.Print.

When I run the code line: 

DoCmd.RunSQL "INSERT INTO JSONforAPI (JSON) VALUES (" & Jsonstr2 & ")"

I get:

Run time error ‘3075’ Malformed GUID in query expression ‘{"order":{"CustomerID":"19"’.”

On clicking Help I get:

This command isn’t available. Your organization’s administrator turned off the service required to use this feature.

I get the same even if I create a Query Def.

I would be very grateful if someone could tell me what is wrong.

ms-access vba sql json
2 Answers

Since you are concatenating your value with the remainder of your SQL statement, none of the delimiters or other special characters within the string held by Jsonstr2 will be escaped and therefore you'll end up with all kinds of malformed strings within the SQL statement.

You should instead use parameters to avoid the need to escape the string, e.g.:

With CurrentDb.CreateQueryDef("","insert into jsonforapi (json) values (@json)")
    .Parameters("@json") = jsonstr2
    .Execute
End With
user_75315980

You could try this:

DoCmd.RunSQL "INSERT INTO JSONforAPI (JSON) VALUES ('" & Jsonstr2 & "')"

This will single quote Jsonstr2.

As you did not quote the string, it is passed as GUID, not a simple string.

user_9530970
Related