Solved

How to extract data from a webpage and enter into an Access Database?

Posted on 2014-11-19
4
222 Views
Last Modified: 2014-11-20
I have a Microsoft Access 2010 Database with a table and a form to read data from a webpage.
When I enter a number into the form, the form must have code to open the corresponding webpage and read a range of data from fields in the web page into the table in the database.
I only want the pages as I enter the numbers (SAQA QUAL ID = fugures long). I don't want the whole website as the information may change any time.
Typical page addresses are
http://allqs.saqa.org.za/showQualification.php?id=62322
http://allqs.saqa.org.za/showQualification.php?id=59769 
Note the 5 digit number at the end. So I think that will be simple to just add the number to the first part of the url. But what then?
I want the attach the VBA code to the provided button on the form.
Database is attached
Where and how should I start?
0
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40452638
You can use the function below to download the page and save as, say, "saqa.html"
This file you can attach via "External Data". Select More, HTML Document.
The wizard opens and you will have listed all the tables inside the document.
It is most likely the SAQA4 you will need. Finish the wizard and you the table attached.
However, it is in spreadsheet style so you are on your own how to proceed from here.

To read another page, you can download that page and overwrite the file linked to.
Option Compare Database
Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
    ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) _
    As Long


Public Function DownloadFile( _
    ByVal strURL As String, _
    ByVal strLocalFilename As String) _
    As Long
  
' Download file or page with public access from the web.
' 2004-12-17. Cactus Data ApS, CPH.

' Usage, download a file:
' lngRet = DownloadFile("http://www.databaseadvisors.com/Graphics/conf2002/2002ConferencePicsbySmolin/images/dba02smolin27.jpg", "c:\happybassett.jpg")
'
' Usage, download a page:
' lngRet = DownloadFile("http://www.databaseadvisors.com/conf2002/conf200202.asp", "c:\dbaconference.htm")

' Returns 0 if success, error code if not.
' Error codes:
' -2146697210 "file not found".
' -2146697211 "domain not found".

' Limitation.
' Does not check if local file was created successfully.

    Dim lngRetVal As Long
      
    lngRetVal = URLDownloadToFile(0, strURL & vbNullChar, strLocalFilename & vbNullChar, 0, 0)
    
    DownloadFile = lngRetVal
  
End Function

Open in new window

saqa.PNG/gustav
0
 

Author Comment

by:Fritz Paul
ID: 40452843
I will try that thanks. Many thanks.
0
 

Author Closing Comment

by:Fritz Paul
ID: 40454471
Thanks Gustav,
I got it in Excel and I know how to read from Excel.
Fritz
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40454474
OK, great.

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Excel ADO SQL question 8 48
Compress Newid value ms sql Mssql 4 45
Getting the 7Z zip dll to work with MS Access 8 55
Excel graph in access report 1 32
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

739 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