Connecting to database via RDP using VBA

Question

I would like to know if anyone can help me with a rather specific problem. Currently I have a range of Excel workbooks which are connected to Epicor via Microsoft Query, the problem I have is that Epicor is stored on a remote server so I am having to log in to refresh the data, I am wondering if there is a way I can use VBA (or any other way) to access the remote server and update the data, I have researched on other sites but I haven't found anything relevant. Any help is much appreciated, thank you

Answer 1

Just like using SQL directly on the server, it's one thing to read from a database and another thing to write to it. Writing directly to Epicor's database is pretty risky.

The good news is you can use VBA to call Epicor's REST API.

First you generate your token:

Public Function AA_GetToken()

Set objReq = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://[yourServer]/[yourAppServerName]/TokenResource.svc"
boolAsync = True


With objReq
    .Open "POST", strUrl, boolAsync
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "username", "[User]"
    .setRequestHeader "password", "[pwd]"
    .send
    While objReq.readyState <> 4
        DoEvents
    Wend
    strResponse = .responseText
End With

intStart = InStr(1, strResponse, "AccessToken", vbTextCompare) + 12
intLen = InStr(1, strResponse, "/AccessToken", vbTextCompare) - intStart - 1

AA_GetToken = Mid(strResponse, intStart, intLen)

End Function

Then (for example) you could make the API call of your choice. This example is a GET but you can do a POST or whatever else as well. You can find Epicor's API documentation in Swagger.

Public Function AB_GetCustomer(custNum As Integer, strAPIKey As String)
    
    Dim onjReq As Object
    Dim jsonObj As Object
    Dim strCust As String
    Dim objReq As Object
    
        
    Set objReq = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://[YourServer]/[YourAppServer]/api/v2/odata/[Company]/Erp.BO.CustomerSvc/Customers?$filter=CustNum%20eq%20" + Trim(Str(custNum))
    
    
    strToken = "Bearer " + AA_GetToken
    boolAsync = False
    
    With objReq
        .Open "GET", strUrl, boolAsync
        .setRequestHeader "Authorization", strToken
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "x-api-key", strAPIKey
        .setRequestHeader "Host", "[YourServer]"
        .setRequestHeader "Connection", "keep-alive"
        .send
        While objReq.readyState <> 4
            DoEvents
        Wend
    End With
    
    strCust = objReq.responseText
 
    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = strCust

    'if you need to write a function to parse the JSON response, there's
    'an excellent one here: https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a
    
    
  
    

End Function

For user credentials you could use a separate userform function to collect them or other parameters.