Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Getting data from a website into Access database

Hi, I've got an Access 2007 database.
I want to update some of the tables in this database with data that is online. The online company has given me the Authorization key and some PHP examples of how to use their API.
However, I don't know PHP, and just know VBA with Access. From what I have been able to determine (correct me if I'm wrong), I will have to use XMLHTTP to send and receive the data.
I've attached an example of a sample of code their website gives me using PHP:
And I've attached my attempt, which doesn't work (it gets me a response that looks like it's sending their entire webpage back to me.)
What I need help with is exactly what command to send using XMLHTTP.
// Get the companies
$companies = $stat->get_companies();
// Check to see if it was successful
if ( $stat->success ) {
    foreach ( $companies as $company ) {
        // Do stuff...
} else {
    // Show response
    echo $stat->message;

Open in new window

Dim oHttpRequest As Object
Dim key As String
Dim url As String
Dim postdata As String
Dim Response As String
Public Function realstats(Optional Email As String) As String
Set oHttpRequest = CreateObject("Microsoft.XMLHTTP")
url = "http://api.realstatistics.com/"
postdata = "auth_key = xxxxxxxxxxxxxxxxx & method = get_companies()"
oHttpRequest.Open "POST", url, False

oHttpRequest.Send (postdata)
If (oHttpRequest.Status >= 200 And oHttpRequest.Status < 300) Then
 Response = oHttpRequest.responseText
End If
Dialog.Box Response

Open in new window

1 Solution
Dave BaldwinFixer of ProblemsCommented:
A REST interface uses web methods but...

"All responses are in JSON and more can be read about it at that link."  http://www.json.org/

See if you can find a JSON parser for Access VBA.  Maybe here: http://ramblings.mcpher.com/Home/excelquirks/json
LearningToProgramAuthor Commented:
Thanks for pointing me in the right direction. I'll check these out

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now