Home > Developments, IT > MS Access VBA, JSON Import and JSON Export

MS Access VBA, JSON Import and JSON Export


Recently stumbled upon a task where JSON string was passed between two systems. While giving a solution for that, I also adapted JSON Import and Export for Ms Access.

 

Import

 

Somewhere similar to Application.ImportXML, it is now possible to create a table from JSON string.  The importXML function allows:

acAppendData => adds new records to the table

acStructureAndData => Creates table and adds data

acStructureOnly => Just creates the table definition.

 

If you are not familiar with my previous vba_tools post, please do have a look at the post and download the sample Access project where you will find many interesting code including this magic one.

 

To import JSON string using the vba_tools.dll call following code:

gdll.ImportJson(JsonArrayAsString, “TargetTableName",acStructureAndData, recreate:=true)

 

I have only tested with JSON arrays and an actual array will look like this. (sample taken from online)

gdll.ImportJson("[{""id"":10,""name"":""User"",""add"":false,""edit"":true,""authorize"":true,""view"":true},    {""id"":11,""name"":""Group"",""add"":true,""edit"":false,""authorize"":false,""view"":true},    {""id"":12,""name"":""Permission"",""add"":true,""edit"":true,""authorize"":true,""view"":true}]","tblJsonTest",acStructureAndData,True)

 

After executing this code I can see a new table in the navigation pane. (F5 if you can’t see it)

image

 

table contents:

image

 

 

Export

 

When we say export, we usually mean to export the content out of the host application. By that, either saving locally or sending/pushing to a different application. ExportToJSon can be used to export json into a file as well as it returns the json string as result. So it can be used to “convert table content to json” too.

In addition, unlike Access traditional export methods, ExportToJson takes an SQL command which can be exported to a file.

 

Method1:

gdll.ExportToJSON("select * from tbljsontest where authorize = true;","MyJson.Txt",overwrite:=false,isRawSql:=true)

above code executes the SQL SELECT command and saves the result set as JSON formatted string.

 

Method2:

gdll.ExportToJSON("tbljsontest ",SaveAs:= "MyJson.Txt",overwrite:=false,isRawSql:=false)

In this method, we have passed a table name/query name to the export function and set isRawSql = false. The export function will then generate SQL statement similar to “SELECT * FROM givenTableName/QueryName;” and perform the JSON Export.

 

If the SaveAs is empty, no file will be exported but the conversion will still happen and converted string will be returned as result.

results in immediate window.

image

 

Saved file content.

image

 

that’s all for now. Have a look at my GitHub Project for latest updates and codes. Enjoy coding Smile

Advertisements
  1. Marius
    September 7, 2018 at 8:42 am

    hello, I would like to use your function to save the content of the Clipboard to a pic.
    I thought it would be necessary to link VBA_TOOLS.DLL in References. But that gives an error: not able to make the reference.
    So I wonder what to do to make it work?

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: