What's your biggest current challenge? Have your say in Community Polls along the right column.
Our Partner & Developer boards on the community are moving to a brand new home: the .dev community forums! While you can still access past discussions here, for all your future app and storefront building questions, head over to the new forums.

Re: Integrating with Microsoft Access VBA

Integrating with Microsoft Access VBA

SimonICS
Visitor
1 0 0

Hi, I'm trying to integrate to with a Microsoft Access database for a customer that's recently started selling on Shopify.

 

I want to use VBA to request all Orders and all Products from the store, which I'll then write to a buffer table and  will add/amend Products and Orders into the database as required. I've successfully managed to retrieve information from the stores but the most records it will give me is the 250 most recently added . I've seen about people using GraphQL but am unsure how to use with VBA. If anyone can help I'd really appreciate it. Below is the code I'm using currently:

 

------------------------------------

Public Sub JSONImport()

Dim db As Database, qdef As QueryDef
Dim FileNum As Integer
Dim DataLine As String, jsonStr As String, strSQL As String
Dim p As Object, element As Variant

Dim xlApp As Object
Dim xlWorkbook As Object

Dim strLogin As String
Dim XMLHTTP As Object

strLogin = "https://" & gcstrShopifyName & ".myshopify.com/admin/api/2022-04/orders.json?limit=250"

Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
XMLHTTP.Open "GET", strLogin, False
XMLHTTP.setRequestHeader "Accept", "text/json"
XMLHTTP.setRequestHeader "Content-Type", "application/json"
XMLHTTP.setRequestHeader "X-Shopify-Access-token", gcstrAdminAPIAccessToken
XMLHTTP.send

 

' PARSE FILE STRING
jsonStr = XMLHTTP.ResponseText
Set dic = ParseJson(jsonStr)

 

' IMPORT TO BUFFER TABLE
ImportOrders dic

End Sub

---------------------------

 

Thanks, Simon

Reply 1 (1)

wmartin129
Shopify Partner
9 3 1

Hi Simon,

 

The REST API call you are using will be returning data in pages of 250 entries each. This is common for many Shopify API calls. You can access the other pages by following their pagination documentation here.

 

If you are pulling lots of data from the Shopify API, repeated calls to fetch the data may by a slow process due to rate limiting etc.  If you require super fast read access to your shop data, then might I suggest installing the API Blazor app in your Shopify store. It has a 14 day free trial.

 

This app provides an almost identical API to the Shopify REST API (for reading data), but it is much faster (as it operates from cached data) and does not impose rate limiting.

 

This may even do away with your need to store your data in your own database?

 

Hope that is of some help.

 

Regards,

Wayne