Getting data from a website into Access database

Posted on 2011-10-18
Last Modified: 2012-05-12
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 = ""
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

Question by:LearningToProgram
    LVL 82

    Accepted Solution

    A REST interface uses web methods but...

    "All responses are in JSON and more can be read about it at that link."

    See if you can find a JSON parser for Access VBA.  Maybe here:

    Author Comment

    Thanks for pointing me in the right direction. I'll check these out

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now