Hi there, I'd like to connect my MS Access front end with my Woocommerce shop via REST API. However I need help with establishing the connection, using the JSON parser (https://github.com/VBA-tools/VBA-JSON) for the start. After that I'll be able to build the sqls to pull the data I need. Has anybody ever done this and can someone maybe share the basic code? Thanks a lot. Frank
Sorry for being not specific enough - this is the REST API documentation for Woocommerce: https://woocommerce.github.io/woocommerce-rest-api-docs/#introduction - however VBA is obviously not mentioned there. It starts with the authentification process (example for PHP below) and continues with pulling e.g. order data. My question is how I can adapt this to VBA & MS Access? Is it possible? Frank
<?php
require __DIR__ . '/vendor/autoload.php';
use Automattic\WooCommerce\Client;
$woocommerce = new Client(
'https://example.com',
'consumer_key',
'consumer_secret',
[
'wp_api' => true,
'version' => 'wc/v3',
'query_string_auth' => true // Force Basic Authentication as query string true and using under HTTPS
]
);
?>
I use this header where strUrl is the url of the RESTApi and apiKey is your key.
This example does a GET. The statement .setRequestHeader "Cache-Control", "max-age-0" avoids your app using cached results. Took us a while to realise we needed that in some cases. I puzzled a lot of this out for myself, as there's so little real help out there.
I just edited this to include the "wait" loop.
Then you need a JSON parser to split the .responseText string into useable data, which is where Colin's stuff or other tools come in, as there's no MS JSON parser (similar to MSXML) that you can use.
I hope this helps.
Code:
DoEvents
Set objRequest = Nothing
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = RESTurl
blnAsync = False
With objRequest
.Open "GET", strUrl, blnAsync
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Bearer " & apikey
.setRequestHeader "Cache-Control", "max-age-0"
.send
'wait for the response
While objRequest.ReadyState <> 4
DoEvents
Wend
If .Status = 200 Then 'maybe some other codes are good responses too
varResponse = .responsetext
Else
'unsuccessful
end if
end With
etc
I use this header where strUrl is the url of the RESTApi and apiKey is your key.
This example does a GET. The statement .setRequestHeader "Cache-Control", "max-age-0" avoids your app using cached results. Took us a while to realise we needed that in some cases. I puzzled a lot of this out for myself, as there's so little real help out there.
I just edited this to include the "wait" loop.
Then you need a JSON parser to split the .responseText string into useable data, which is where Colin's stuff or other tools come in, as there's no MS JSON parser (similar to MSXML) that you can use.
I hope this helps.
Code:
DoEvents
Set objRequest = Nothing
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = RESTurl
blnAsync = False
With objRequest
.Open "GET", strUrl, blnAsync
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Bearer " & apikey
.setRequestHeader "Cache-Control", "max-age-0"
.send
'wait for the response
While objRequest.ReadyState <> 4
DoEvents
Wend
If .Status = 200 Then 'maybe some other codes are good responses too
varResponse = .responsetext
Else
'unsuccessful
end if
end With
etc
I use this header where strUrl is the url of the RESTApi and apiKey is your key.
This example does a GET. The statement .setRequestHeader "Cache-Control", "max-age-0" avoids your app using cached results. Took us a while to realise we needed that in some cases. I puzzled a lot of this out for myself, as there's so little real help out there.
I just edited this to include the "wait" loop.
Then you need a JSON parser to split the .responseText string into useable data, which is where Colin's stuff or other tools come in, as there's no MS JSON parser (similar to MSXML) that you can use.
I didn't even manage to pass the REST API basic authentification with Postman with both keys I had generated in Woocommerce. So I'm giving up... I now turned to pulling data simply via ODBC which is working well via MySQL Connector. Thanks for helping. Frank
note that on this page it says this about authentication.
"By default, the API provides information about all available endpoints on the site. Authentication is not required to access the API index."
this is the example, but I think example.com is not a real site. Do you have a real site I can try instead of example.com, and then I can post some code for you.
I didn't even manage to pass the REST API basic authentification with Postman with both keys I had generated in Woocommerce. So I'm giving up... I now turned to pulling data simply via ODBC which is working well via MySQL Connector. Thanks for helping. Frank
I am not familiar with Woocommerce, but I am surprised they let you connect to their data via ODBC. It's good that they do, which probably means you also don't have to parse JSON data anymore, correct?
That's good. But like I said, I am surprised a commercial (assuming that's what they are) is allowing their customers to directly connect to their data. I guess that's good news for you. Good luck!
I would still be interested in seeing if your WooCommerce site responds to my read. That read doesn't recover anything confidential, it's just more of a test, so it would useful if you could include your actual site name. Any chance of a PM with the info.
Hi Gemma, I would like to try your code, but I don't have a Bearer token. Woocommerce / Wordpress gives you a Consumer Key and a Consumer Secret issued specifically for my user. With these codes, I should be able to read and write data. In PHP, it would be the code below (https://woocommerce.github.io/woocommerce-rest-api-docs/?php#authentication-over-https), however there is no documentation for VBA. Thanks, Frank