MS Access integration with API


Microsoft Access (Office 16) and VBA


I’m brand new to Alpaca and using an API, but I was thinking of reviving an old Access database I used to use to play around with day trading strategies. While I realize I may have to rewrite all that VBA into something else like Java or Python (also new to me), I first wanted to see if there’s a way to send POST messages from Access to the API.

I’ve found some general tips on Access and POST methods, but my initial attempts get a “not found” error when sending to the API url.

Here was one attempt using a JSON string: {“symbol”: “UVXY”, “qty”: 500, “side”: “sell”, “type”: “market”, “time_in_force”: “cls”, “client_order_id”: “123456”}

1 Like

this sounds fun might be able to help a bit if you’d like depending on what it is you want to do.

I’m really just looking to revive this Access database with its VBA code to generate order ideas and send those orders to the Alpaca API (paper trading only). Years ago when I first used the db, I had access to a Bloomberg terminal and could easily import real-time prices to simulate order fills. Now I’d like to have Access send the order message to Alpaca, get the response/fill back, and then track positions as it goes along.

anything specific your stuck on

Getting the POST from Access to actually reach the API!

Here’s the VBA function:

Public Function HTTP_RESP(ByVal sReq As String) As String

Dim byteData() As Byte
Dim XMLHTTP As Object

Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

XMLHTTP.Open "POST", sReq, False
byteData = XMLHTTP.responseBody

Set XMLHTTP = Nothing

HTTP_RESP = StrConv(byteData, vbUnicode)

End Function

And here’s the URL and JSON string I’m trying to pass into the function and through to Alpaca: {“symbol”: “UVXY”, “qty”: 500, “side”: “sell”, “type”: “market”, “time_in_force”: “cls”, “client_order_id”: “123456”}

I’m getting this error in response:
{“code”:40410000,“message”:“endpoint not found”}

would you use php?..

I had a similar issue with integrating Access to SendGrid. Don’t include the braces in the endpoint. Try removing the braces in your JSON (its really not json). In my experience, the XMLHTTP object is better created as “MSXML2.ServerXMLHTTP”. I would also check XMLHTTP.Status after sending along with the responseBody.

Related question: how/where do I include the API Key and Secret?

This worked for retrieving my account.

Const ALPACA_API_KEY As String = “your_key_here”
Const ALPACA_API_SECRET_KEY As String = “your_secret_key_here”

Function HTTP_RESP() As String

Dim byteData() As Byte
Dim objHTTP As Object

Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
Const strEndPoint As String = “

'objHTTP.SetOption 2, 13056
objHTTP.Open “GET”, strEndPoint, False
'objHTTP.setRequestHeader “Content-Type”, “application/json; charset=UTF-8”
byteData = objHTTP.responseBody

Set objHTTP = Nothing

HTTP_RESP = StrConv(byteData, vbUnicode)

I have the code for POSTING. You can email me at and I will give you the code. I can’t post it here. Alpaca limits new users to the number of postings.

i dont remember a limit

I think it’s discourse’s limit to avoid potential spam but over the time as a membership duration increases such limit goes away. Apologies for inconvenience.

BTW I love idea of using MS Access (or any this kind of nice idea)!