Solved Connect Access with Woocommerce via REST API (1 Viewer)

[perlfan]

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

 

[MsAccessNL]

I have experience with Rest Api and Json parsing, but not specifically with Woocommerce.You have te be moore specific with your question.

 

[theDBguy]

Hi Frank. I've used VBA-JSON before but don't have the code available at the moment.

Sent from phone...

 

[GPGeorge]

Check out the JSON Analyzer and parser on Colin Riddington's website. Colin is active here as isladogs.

I've used his JSON tools and can vouch for them.

 

[perlfan]

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
]
);
?>

 

[gemma-the-husky]

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.

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

 

[perlfan]

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.

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

Thank you, I'll try it.

 

[perlfan]

Thank you, I'll try it.

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.

 

[perlfan]

@gemma-the-husky Could you be more specific about

.setRequestHeader "Authorization", "Bearer " & apikey

? I get a "Access denied message". I guess I need to insert my username and the Consumer Secret key, but how? Thank you, Frank

 

[theDBguy]

@gemma-the-husky Could you be more specific about

.setRequestHeader "Authorization", "Bearer " & apikey

? I get a "Access denied message". I guess I need to insert my username and the Consumer Secret key, but how? Thank you, Frank

Did you use this endpoint to create the api key first?

https://woocommerce.github.io/woocommerce-rest-api-docs/#authentication

 

[gemma-the-husky]

Yes - the apikey is just your personal login key issued by the RESTApi site.
Store it as a constant.

it will be a long random looking string.

 

[perlfan]

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

 

[gemma-the-husky]

@perlfan

I am trying to connect using the example on the Woo Documentation here

Index – WooCommerce REST API Documentation - WP REST API v3

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.

curl https://example.com/wp-json/wc/v3

 

[theDBguy]

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?

 

[perlfan]

No, no parsing required You simply fetch your data like you do from any other MySQL db. Frank

 

[theDBguy]

No, no parsing required You simply fetch your data like you do from any other MySQL db. Frank

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!

 

[cheekybuddha]

Afaiu it's your own data. Woocommerce is not a service

 

[theDBguy]

Afaiu it's your own data. Woocommerce is not a service

That would make sense then.

 

[gemma-the-husky]

No, no parsing required You simply fetch your data like you do from any other MySQL db. Frank

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.

 

[perlfan]

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

<?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'
]
);
?>