{"id":5166,"date":"2018-10-13T05:31:43","date_gmt":"2018-10-13T05:31:43","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5166"},"modified":"2020-05-27T12:40:01","modified_gmt":"2020-05-27T12:40:01","slug":"import-rest-api-ms-access-load-json-soap-xml","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/import-rest-api-ms-access-load-json-soap-xml\/","title":{"rendered":"Import REST API in MS Access (Load JSON \/ SOAP XML)"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"json<\/a><\/p>\n

In this post, we will learn how to import REST API in MS Access (JSON or SOAP XML data).\u00a0 We will use\u00a0ODBC PowerPack<\/a>\u00a0to connect and query a JSON file \/ URL.<\/p>\n

JSON stands for JavaScript Object Notation and it is an Open and Standard format to read an object with attributes and values.\u00a0 JSON is replacing XML because it is faster to read data, it is easier to parse data, it does not require end tags, it is shorter and it can use arrays.<\/p>\n

We will load the data using the ZappySys JSON \/ REST API driver<\/a> and then import the data in Access. You can use similar techniques to load SOAP Data using XML \/ SOAP Driver<\/a>.<\/p>\n

 <\/p>\n

Requirements<\/span><\/h2>\n

This article assumes the following requirements are met before you can follow the steps listed in this article.<\/p>\n

    \n
  1. Make sure that Microsoft Access installed<\/li>\n
  2. Also, the driver\u00a0ODBC PowerPack<\/a>\u00a0installed<\/li>\n<\/ol>\n

    An introduction to Rest API and OData<\/span><\/h2>\n

    In this example, we will use OData (Open Data Protocol) to consume REST API. REST API\u00a0(Representational State Transfer Application Program Interface) allows handling the interoperability between computers and the internet.<\/p>\n

    In REST API we can handle web services in different formats. In this example, we will work with the Northwind example. The Northwind example is available in this URL:<\/p>\n

    \n
    \n
    https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc<\/pre>\n<\/div>\n<\/div>\n
      \n
    1. By default, the data is displayed in XML format. To show the data in JSON use this URL:\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/?$format=json<\/pre>\n<\/li>\n
    2. There are collections of data like Categories, CustomerDemographic, Customers, Invoices, etc. For example, the following URL will show the data of the categories collection:\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Categories?$format=json<\/pre>\n<\/li>\n
    3. In the next steps, we will use ZappySys drivers to connect to this URL and query using OData.<\/li>\n<\/ol>\n
      Configure ODBC DSN for ZappySys JSON Driver<\/span><\/h2>\n
      ODBC driver can be accessed in two modes.<\/p>\n
        \n
      1. Using DSN<\/li>\n
      2. Without DSN (Supply direct Connection String e.g.\u00a0DRIVER={ZappySys JSON Driver}; \u2026\u2026..<\/strong>\u00a0)<\/li>\n<\/ol>\n
        In this article, we will use the DSN approach (User DSN).\u00a0We will first add the ZappySys JSON Driver in the ODBC Data Source Administrator.<\/p>\n
        Follow these steps to accomplish the task:<\/p>\n
          \n
        1. First,\u00a0Windows search<\/strong>, write\u00a0ODBC<\/strong>\u00a0and select the\u00a0ODBC Data sources (32 bits)<\/strong>\n
        2. As a second step, in ODBC Data source Administrator press the\u00a0Add<\/strong>\u00a0button.\n
        3. In this step, create the new data source, select\u00a0ZappySys ODBC Driver.<\/strong>\n
        4. Here we have several properties, write a data source name. In this example, the name will be ZappySys JSON to Excel.<\/li>\n
        5. The Data Source (URL or file path) can specify the URL of the source or if it is a local file, you can specify the local path. In this example, the URL is:\n
          https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Invoices?$format=json<\/pre>\n
          You can also specify a local file path as Data SourceFor a single file:\u00a0 c:\\data\\myfile_1.json
          \nFor multiple files: c:\\data\\myfile_*.json<\/li>\n
        6. Configure the ODBC JSON Driver like this:\n
          \"Configure<\/a>
          Configure JSON Driver for REST API Call<\/p><\/div><\/li>\n<\/ol>\n
          <\/a>How to import<\/span><\/em> REST API data to Access<\/span><\/h2>\n
            \n
          1. In MS Access, go to External Data<\/strong>\u00a0Ribbon and select\u00a0New Data Source<\/strong> and\u00a0select From Other Sources<\/strong> and\u00a0ODBC\u00a0Database<\/strong>.\n
          2. Select the Source and destination of the data, select\u00a0Import the source data into a new table in the current database<\/strong>\u00a0and press OK.\n
          3. Select your newly created JSON Driver from the Machine Data Source and click\u00a0OK.
            \n<\/strong><\/strong><\/strong><\/p>\n
          4. Select tables from the list of tables in the Import Objects screen and click\u00a0OK.
            \n<\/strong><\/strong><\/p>\n
          5. Select Save Import Steps<\/strong> from the screen and click on Save Import<\/strong> button.\n
          6. If everything is OK, you will be able to see the data.\n
            \"Data<\/a>
            Data Imported Successfully in MS Access DB Table<\/p><\/div><\/li>\n<\/ol>\n
            How to link<\/u>\u00a0<\/i>REST API data to Access<\/span><\/h2>\n
            The linking process is very similar to importing data into the Access table. Follow steps of the previous paragraph –\u00a0How to import REST API data to Access<\/a>\u00a0– but on step 2 select the second option to create a linked table<\/strong><\/em> <\/i><\/b>instead. Then once asked to select a unique record identifier, don’t select anything:<\/p>\n
            \"Link
            Linked table approach – Don’t select a unique record identifier<\/p><\/div>\n
            REST API \/ XML SOAP Pagination Settings for MS Access<\/h2>\n
            Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that\u00a0ZappySys ODBC Driver<\/em> includes many options to cover virtually any pagination method.<\/div>\r\n
            Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):<\/span><\/div>\r\n
            https:\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/<\/a><\/div>\r\n
            Paginate by Response Attribute<\/h3>\r\nThis example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.\r\n
            SELECT * FROM $\r\nWITH(\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/pagination_nextlink_inarray_1.json'\r\n,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']\r\n)<\/pre>\r\n
            Paginate by URL Parameter (Loop until certain StatusCode)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
            SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'\r\n,PagingMode='ByUrlParameter'\r\n,PagingByUrlAttributeName='page'\r\n,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'\r\n,PagingByUrlCheckResponseStatusCode=401\r\n,IncrementBy=1\r\n)<\/pre>\r\n
            Paginate by URL Path (Loop until no record)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
            SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/cust-<%page%>.xml'\r\n,PagingMode='ByUrlPath'\r\n,PagingByUrlAttributeName='<%page%>'\r\n,PagingByUrlEndStrategy='DetectBasedOnRecordCount'\r\n,IncrementBy=1\r\n)<\/pre>\r\n
            Paginate by Header Link (RFC 5988)<\/h3>\r\nAPI like GitHub \/ Wordpress use Next link in Headers (RFC 5988<\/a>)\r\n
            SELECT * FROM $\r\nLIMIT 25\r\nWITH(\r\n\t Src='https:\/\/wordpress.org\/news\/wp-json\/wp\/v2\/categories?per_page=10'\r\n\t,PagingMode='ByResponseHeaderRfc5988'\r\n\t,WaitTimeMs='200' --\/\/wait 200 ms after each request\r\n)<\/pre>\r\n <\/div>\n
            REST API \/ SOAP Web Service Connection Settings for MS Access<\/h2>\n
            If you need to authenticate or authorize your user to access a web resource, you will need to use one of\u00a0the Connections:<\/em><\/div>\r\n