VBA API call standards (1 Viewer)

[ions]

Hello MS Access Expert,

I would like to know if it is possible to make API calls via VBA that meet the below standards:

Ability to adhere to TLS 1.2 encryption for HTTPS connection
Ability to support OAuth 2.0 JSON Web Token (JWT) Bearer Flow

I have a MS Access system that has been in operation for 20 years and would like to incorporate API calls to an external Salesforce system that requires the above technical specifications.

Thank you

 

[The_Doc_Man]

It is always going to depend on whether you have the appropriate .DLL (library) files.

This article suggests that VBA can interact in the required way.

Google Oauth2 VBA authentication - Desktop Liberation

ramblings.mcpher.com

This links to a page of links regarding OAuth2 and Excel, but Excel and Access use the same VBA "driver" so should be compatible.

OAuth2 Examples for Excel

 

[theDBguy]

Short answer: Yes, it is possible. I have done it a few times now.

 

[Minty]

I thought for Enterprise Office licences you could connect directly to Salesforce from Access:

Link to or import data from Salesforce - Microsoft Support

Learn how to use Access to import or link to data sources from Salesforce.

support.microsoft.com

 

[ions]

It is always going to depend on whether you have the appropriate .DLL (library) files.

This article suggests that VBA can interact in the required way.

Google Oauth2 VBA authentication - Desktop Liberation

ramblings.mcpher.com

This links to a page of links regarding OAuth2 and Excel, but Excel and Access use the same VBA "driver" so should be compatible.

OAuth2 Examples for Excel

Thank you for providing these links. I will review the code.

 

[ions]

Short answer: Yes, it is possible. I have done it a few times now.

Good to know it's possible and you have implimented it successfully theDBguy.

 

[theDBguy]

Good to know it's possible and you have implimented it successfully theDBguy.

Just to clarify, I was just answering your question if it's possible to make API calls via VBA that adhere to the standards you listed. I'm not saying I worked with Salesforce. However, I have worked with various Financial Systems, Manufacturing and Inventory Systems, Amazon, and others.

 

[ions]

It is always going to depend on whether you have the appropriate .DLL (library) files.

This article suggests that VBA can interact in the required way.

Google Oauth2 VBA authentication - Desktop Liberation

ramblings.mcpher.com

This links to a page of links regarding OAuth2 and Excel, but Excel and Access use the same VBA "driver" so should be compatible.

OAuth2 Examples for Excel

The_Doc_Man,

I am running the Kyle Beachill code as you suggested. OAUTH2 and VBA

When I attempt to get the AuthHeader the code opens an Internet Explorer window with a BING Search. (See below screenshot) I presume I am supposed to do some kind of user interaction based on the code comments (see below) but I am not sure what.

Any assistance would be appreciated thank you.

 

[The_Doc_Man]

Can't help you beyond supplying those links because I retired before actually having to deal with OAuth. I found the links via a web search.

 

[ions]

Can't help you beyond supplying those links because I retired before actually having to deal with OAuth. I found the links via a web search.

Thanks The_Doc_man.

TheDBGuy are you comfortable sharing some code snippets demonstrating how you successfully implemented OAuth 2.0 or pointing to a source that helped you in your implementation.

Thank you.

 

[isladogs]

I thought for Enterprise Office licences you could connect directly to Salesforce from Access:

Link to or import data from Salesforce - Microsoft Support

Learn how to use Access to import or link to data sources from Salesforce.

support.microsoft.com

Yes you can (though I've never done so) - this is using A365 Enterprise E5

 

[theDBguy]

TheDBGuy are you comfortable sharing some code snippets demonstrating how you successfully implemented OAuth 2.0 or pointing to a source that helped you in your implementation.

Hi. Merry Christmas! This is probably not going to help you, because it's just a snippet of the code I used, and it's the safest one I can share without giving away my client's information (I hope).

Private Function SubmitAmazonRequest(QueryString As String, strXML As String) As Boolean
'thedbguy@gmail.com
'6/11/2020
'Submits the web request to Amazon

Dim objHTTP As Object
Dim strQueryToSign As String
Dim strSignature As String

'build query string
strQueryToSign = "POST" & Chr(10) _
    & "mws.amazonservices.com" & Chr(10) _
    & "/" & Chr(10) & QueryString

strSignature = URLEncode(CreateSignature(strQueryToSign, SECRET_KEY))

QueryString = QueryString & "&Signature=" & strSignature

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

'send request
With objHTTP
    .Open "POST", API_URL & QueryString, False
    .setRequestHeader "Accept", "text/xml"
    .setRequestHeader "Content-Type", "text/xml"
    .Send
    strXML = .ResponseText
    
    If .Status = 200 Then
        SubmitAmazonRequest = True 'success
        
    Else
        SubmitAmazonRequest = False 'failure
        
    End If

End With

End Function

PS. The above is a straight copy and paste from my Module without any edits (just in case you're wondering).
PPS. As for where I got this information - I got it from Amazon's website.

 

[ions]

Hi. Merry Christmas! This is probably not going to help you, because it's just a snippet of the code I used, and it's the safest one I can share without giving away my client's information (I hope).

Private Function SubmitAmazonRequest(QueryString As String, strXML As String) As Boolean
'thedbguy@gmail.com
'6/11/2020
'Submits the web request to Amazon

Dim objHTTP As Object
Dim strQueryToSign As String
Dim strSignature As String

'build query string
strQueryToSign = "POST" & Chr(10) _
    & "mws.amazonservices.com" & Chr(10) _
    & "/" & Chr(10) & QueryString

strSignature = URLEncode(CreateSignature(strQueryToSign, SECRET_KEY))

QueryString = QueryString & "&Signature=" & strSignature

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

'send request
With objHTTP
    .Open "POST", API_URL & QueryString, False
    .setRequestHeader "Accept", "text/xml"
    .setRequestHeader "Content-Type", "text/xml"
    .Send
    strXML = .ResponseText
   
    If .Status = 200 Then
        SubmitAmazonRequest = True 'success
       
    Else
        SubmitAmazonRequest = False 'failure
       
    End If

End With

End Function

PS. The above is a straight copy and paste from my Module without any edits (just in case you're wondering).
PPS. As for where I got this information - I got it from Amazon's website.

Thank you TheDBGuy.