Function Query Example
Need to use an API to get data from a website into an Access database? This is a fairly common requirement and we show a great example on this page. Here we are going to retrieve the current price of stocks trading out there in the stock market. We will use Yahoo Finance’s API for this.In the example below we first create a Public Function containing the API code. The we call that function while using a Select Query to retrieve our stock symbols from our table.
Once see how to dynamically retrieve the current stock prices you can show them in a form or change to an Update Query to save the in a table along with the current date. If you wanted to you could create an Access database that would wake up once a day on update your database of stock quotes. This way you can plot them over time.
Here is the table we are using for this SQL API function example:
M_StocksStock_SymbolINTCIBBVOXF
Next is the function we have created:
And the Access query contains this query code:
And when we run the query we get:
You can also read more about Yahoo Finance’s stock quote api. There is a lot more information about each stock that can be pulled using the API.
Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016