How to Export REST API to MS Access using VBA Command Button

Introduction

In our previous blog we saw how to export REST API to CSV using c# or Python. In this post we will look at specific example on How to Export REST API to MS Access using VBA Command Button, along with few other topics such as how to make REST API Call, how to read all Customers data from Odata using our ODBC JSON Driver and load it into the Ms Access Table.

We will go through the steps to read data from Odata and Load into MS Access using VBA Command Button.

In nutshell, this post will focus on how to make ODBC Driver Call using VBA.

So let’s get started.

Requirements

1. First of all, you will need ZappySys ODBC PowerPack installed.
2. Secondly, Make sure that Microsoft Access installed for VBA example.

An introduction to Rest API and OData

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

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:

1. By default, the data is displayed in XML format. To show the data in JSON use this URL:
   
   https://services.odata.org/V3/Northwind/Northwind.svc/?$format=json

   1	https://services.odata.org/V3/Northwind/Northwind.svc/?$format=json

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:
   
   https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

   1	https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

3. In the next steps, we will use ZappySys drivers to connect to this URL and query using OData.

Configure ODBC DSN for ZappySys JSON Driver

ODBC driver can be accessed in two modes.

1. Using DSN
2. Without DSN (Supply direct Connection String e.g. DRIVER={ZappySys JSON Driver}; …….. )

In this article, we will use the DSN approach (User DSN). We will first add the ZappySys JSON Driver in the ODBC Data Source Administrator.

Follow these steps to accomplish the task:

1. First, Windows search, write ODBC and select the ODBC Data sources (32 bits)
   

   Open ODBC Data Source

2. As a second step, in ODBC Data source Administrator press the Add button.
   

   Add ZappySys

3. In this step, create the new data source, select ZappySys JSON Driver.
   

   Add new zappysys json driver

4. Here we have several properties, write a data source name. In this example, the name will be ZappySys JSON to Excel.
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:
   
   https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

   1	https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

   You can also specify a local file path as Data SourceFor a single file:  c:\data\myfile_1.json
   For multiple files: c:\data\myfile_*.json
6. Configure the ODBC JSON Driver like this and test the connection:
   

   Configure JSON Driver for REST API Call

7. Now click on Copy Connection string Button.
   

   ODBC Driver : Copy Connection String

8. That’s it Connection string is copied to clipboard. Now let’s use this connection string in the VBA code.

How to import REST API data to Access using VBA

1. Open the access database and create the table with the desire columns to get API data in it.
   

   Create Table in Access

2. Now close the table, and go to Create menu and click on Form Design to add the new blank form in design view.
   

   Access : Create a New Form in Design View

3. It will open the new form in the design view, now add new button with the desire text.
   

   Access : Add Command Button

4. Now right click on button and go to properties and click on … builder button and select Code Builder.
   

   Access : Code builder

5. It will open the new VBA instance with the button click command event. Add the below Function also in it to check that table is open in the access or not.
   
   Public Function IsTableOpen(strName As String) As Boolean IsTableOpen = SysCmd(acSysCmdGetObjectState, acTable, strName) End Function

   1 2 3

   Public Function IsTableOpen(strName As String) As Boolean      IsTableOpen = SysCmd(acSysCmdGetObjectState, acTable, strName) End Function

   

   VBA : Check Table is open in the access

6. Now add the below code under the Command5_Click()
   • Close Table If Open, other wise in the below command it’s throw an error.
   • Truncate Table.
   • Make the API call using that ODBC Driver connection string and insert into access table.
   • Open Table again to view the inserted data.
   ''''''''''1. Close Table If Open, other wise in the below command it's throw an error If IsTableOpen("tblCustomers") Then DoCmd.SelectObject acTable, "tblCustomers" DoCmd.RunCommand acCmdSaveRecord DoCmd.Close End If ''''''''''2. Truncate Table Dim strSql As String strSql = "DELETE FROM tblCustomers;" 'If you have any ID auto increment Integer type column in the table 'To reset the index 'CurrentProject.Connection.Execute strSql 'strSql = "ALTER TABLE tblCustomers ALTER Column ID INT;" 'CurrentProject.Connection.Execute strSql 'strSql = "ALTER TABLE tblCustomers ALTER Column ID AUTOINCREMENT;" 'CurrentProject.Connection.Execute strSql ''''''''''3. Make The ODBC Driver Call to get the data from API and insert into access table 'Define dscn as DB connection Dim dscn As New ADODB.Connection 'dscn.Open "{{Your ODBC Driver Connection String Goes here}}" dscn.Open "DRIVER={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';Filter='$.value[*]'" 'define recd as record set of table and data Dim recd As New ADODB.Recordset recd.Open "Select * from $", dscn While Not recd.EOF If ((IsNull(recd(0)) = False) And (IsNull(recd(1)) = False)) Then strQuery = "" strQuery = "INSERT INTO tblCustomers (CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax) VALUES " strQuery = strQuery & "(""" & recd!CustomerID & """, """ & recd!CompanyName & """, """ & recd!ContactName & """, """ & recd!ContactTitle & """, """ & recd!Address & """, """ & recd!City & """, """ & recd!Region & """, """ & recd!PostalCode & """, """ & recd!Country & """, """ & recd!Phone & """, """ & recd!Fax & """);" 'Or 'strQuery = strQuery & "(""" & recd(0) & """, """ & recd(1) & """, """ & recd(2) & """, """ & recd(3) & """, """ & recd(4) & """, """ & recd(5) & """, """ & recd(6) & """, """ & recd(7) & """, """ & recd(8) & """, """ & recd(9) & """, """ & recd(10) & """)" CurrentProject.Connection.Execute strQuery End If recd.MoveNext Wend ''''''''''4. Open Table again to view the insrted data If IsTableOpen("tblCustomers") = False Then DoCmd.OpenTable ("tblCustomers") End If ''''''''''Refresh the Table if Open 'DoCmd.SelectObject acTable, "tblCustomers" 'DoCmd.Requery 'DoCmd.GoToRecord acDataTable, "tblCustomers", acLast ''''''''''Show Message after loading data Successfully MsgBox ("Customers Data Loaded Successfully!") dscn.Close

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

   ''''''''''1. Close Table If Open, other wise in the below command it's throw an error If IsTableOpen("tblCustomers") Then    DoCmd.SelectObject acTable, "tblCustomers"    DoCmd.RunCommand acCmdSaveRecord    DoCmd.Close End If   ''''''''''2. Truncate Table Dim strSql As String strSql = "DELETE FROM tblCustomers;"   'If you have any ID auto increment Integer type column in the table 'To reset the index 'CurrentProject.Connection.Execute strSql 'strSql = "ALTER TABLE tblCustomers ALTER Column ID INT;" 'CurrentProject.Connection.Execute strSql 'strSql = "ALTER TABLE tblCustomers ALTER Column ID AUTOINCREMENT;" 'CurrentProject.Connection.Execute strSql     ''''''''''3. Make The ODBC Driver Call to get the data from API and insert into access table 'Define dscn as DB connection Dim dscn As New ADODB.Connection 'dscn.Open "{{Your ODBC Driver Connection String Goes here}}" dscn.Open "DRIVER={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';Filter='$.value[*]'" 'define recd as record set of table and data Dim recd As New ADODB.Recordset recd.Open "Select * from $", dscn While Not recd.EOF      If ((IsNull(recd(0)) = False) And (IsNull(recd(1)) = False)) Then             strQuery = ""             strQuery = "INSERT INTO tblCustomers (CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax)  VALUES "             strQuery = strQuery & "(""" & recd!CustomerID & """, """ & recd!CompanyName & """, """ & recd!ContactName & """, """ & recd!ContactTitle & """, """ & recd!Address & """, """ & recd!City & """, """ & recd!Region & """, """ & recd!PostalCode & """, """ & recd!Country & """, """ & recd!Phone & """, """ & recd!Fax & """);"             'Or             'strQuery = strQuery & "(""" & recd(0) & """, """ & recd(1) & """, """ & recd(2) & """, """ & recd(3) & """, """ & recd(4) & """, """ & recd(5) & """, """ & recd(6) & """, """ & recd(7) & """, """ & recd(8) & """, """ & recd(9) & """, """ & recd(10) & """)"             CurrentProject.Connection.Execute strQuery     End If     recd.MoveNext Wend   ''''''''''4. Open Table again to view the insrted data If IsTableOpen("tblCustomers") = False Then    DoCmd.OpenTable ("tblCustomers") End If   ''''''''''Refresh the Table if Open 'DoCmd.SelectObject acTable, "tblCustomers" 'DoCmd.Requery 'DoCmd.GoToRecord acDataTable, "tblCustomers", acLast   ''''''''''Show Message after loading data Successfully MsgBox ("Customers Data Loaded Successfully!")   dscn.Close

   
7. Now save the VBA form code and go to Access.
   

   Save form vba code

8. And open the form and click on the command button to load the data.
   

   Access : Click Command Button

9. It will throw the below error if MS ActiveX reference is not added.
   

   VBA : Error Type not defined

10. So now add the Microsoft ActiveX Data Objects X.0 Library reference in the project.
    

    Access : Add reference

11. That’s it now again click on the the command button and it will make rest api call, insert the data in the access table and open the table to view the inserted records with success message.
    

    Access : Open the table

Conclusion

In this article, we saw how to use VBA to call REST API. We show how to do REST API calls in Access using the ZappySys ODBC driver. Also, we show how to write VBA Code in MS Access for command button to make REST API call, truncate access table and insert record in the access table using VBA code. If you liked this article and you want to try, you can download the ZappySys ODBC installer here.

References

• ZappySys ODBC installer.
• Access ODBC Query
• Using ADO with Microsoft Visual Basic and Visual Basic for Applications