Get Data From Website to Access Form
Insert Data Into Access Database From HTML Page
Internet API: It is sometimes useful to be able to send a request out to a website to retrieve live and timely data. Stock quotes are a good example for this functionality.
Here we have a demo which retrieves data from Yahoo Finance website and display the data on an Access form. You can use the same code to bring timely data to a report or within a query.
Here is an image of the objects involved:
The table is simply a list of stock symbols:M_StocksStock_SymbolAPPLIBBQQQXOM
Here is the function which does all the work:
Public Function quote(strticker As String)
Dim strurl As String, strcsv As String, strRows() As String, strColumns() As String
strurl = “http://download.finance.yahoo.com/d/quotes.csv?s=” & strticker & “&f=l1&ignore=.csv”
Set http = CreateObject(“MSXML2.XMLHTTP”)
http.Open “GET”, strurl, False
strcsv = http.responsetext
quote = Nz(CCur(strcsv), 0)
Set http = Nothing
This is a screen shot of the form in design view:
Note that the Stock Quote field passes the Stock_Symbol to a visual basic function. The function returns the current value of the stock quote. As you type in new symbols on the form the current price will be retrieved. You can use the ‘Refresh All’ menu choice at the top of the form to get a fresh quote.
Feel free to download our Access database example for using a website API to retrieve data into Microsoft Access.
And finally an image of the form running with all the stock symbols with current value. Hopefully when I look at this in two years all the values will have doubled so that I can retire.
Microsoft Office Forms:
MS Access 2003
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016