[AccessD] Web page to Access

It can be done by screen scraping via xmlhttp request object, winhhtp or 
wininet.
Essentially you send a http post command like 
www.somesite.com?table=A&recordrange=100
It then returns a text string which is a copy of the html screen
You then parse the returned html for the values desired.
Really easy if html is xhtml compliant (ha ha rarely true) you can then 
use xslt to parse out values
Drawbacks if html page format changes (common) you may have rewrite 
parser code.
also some sites may do page redirects for security to stop this type of 
scraping or have hidden the asp source.
Some of the bigger or more technically adept companies may provide this 
data as a web service.
Needs some knowledge of html forms to get it to work.You may have to 
figure out button code
on original html source of the request page.

If you don't have a formal data sharing agreement with the company, I 
wouldn't bet the farm on it.
for a whole variety of reasons.

I know one VP of  an airline in Canada went to jail or was severely 
fined for screenscraping a competitors route and load info
from an internal website. He ummm borrowed a password from an 
ex-employee of the competitor and then hit the webpage thousands
of time via code using this method, they sort of noticed the spike of 
hits in the middle of the night.

Here is some sample code that works off a webservice for limited US 
Canada address testing.
See here for info.
http://ws.cdyne.com/psaddress/addresslookup.asmx/
You need a purchased license key other than 0  for a large volume.

To use this code elsewhere
You would just change oXMLHTTP.send string to whatever parameters comes 
behind "?"
and then read or dump returned string to a file with the returned html 
text in oXMLHTTP.responseText
and then parse text string for your values.

This site returns xml rather than straight text so easier to parse with 
DOM or XSLT or XPath query.

Sub test()
Dim strAddress As String
strAddress = AddrCorrect("One Microsoft Way", "Redmond", "WA", 
"98052-6399", "0")
MsgBox strAddress
strAddress = AddrCorrect("1 Microsoft Way", "Redmond", "WA", 
"98052-6399", "0")
MsgBox strAddress
strAddress = AddrCorrect("One Lake St", "Upper Saddle River", "NJ", 
"07458", "0")
MsgBox strAddress
strAddress = AddrCorrect("", "Redmond", "WA", "", "0")
MsgBox strAddress
strAddress = AddrCorrect("417 Fifth Ave", "New York", "NY", "10016", "0")
'more than 1 zip code
MsgBox strAddress
strAddress = AddrCorrect("2400 East Bayshore Road", "Palo Alto", "CA", 
"94303", "0")
MsgBox strAddress
Debug.Print strAddress
End Sub
Public Function AddrCorrect(ByRef address As String, ByRef city As 
String, ByRef state As String, ByRef zip As String, Optional LicenseKey 
As String) As String
  'Dim oXMLHTTP As MSXML2.ServerXMLHTTP
  Dim oXMLHTTP As Object
 
  ' Call the web service to get an XML document
   Set oXMLHTTP = CreateObject("Msxml2.ServerXMLHTTP")
  ' Set oXMLHTTP = New MSXML2.ServerXMLHTTP
  oXMLHTTP.Open "POST", _
                
"http://ws.cdyne.com/psaddress/addresslookup.asmx/CheckAddress", _
                False
  oXMLHTTP.setRequestHeader "Content-Type", _
                            "application/x-www-form-urlencoded"
  oXMLHTTP.send "AddressLine=" & URLEncode(address) & "&ZipCode=" & 
URLEncode(zip) & "&City=" & URLEncode(city) & "&StateAbbrev=" & 
URLEncode(state) & "&LicenseKey=" & URLEncode(LicenseKey)
 
  If oXMLHTTP.status <> 200 Then
    MsgBox "Service Unavailable. Try again later"
    Set oXMLHTTP = Nothing

    Exit Function
  End If
  'Dim oDOM As Object
  ' doesn't work why Dim oDOM As MSXML2.DOMDocument50
  Dim oDOM As MSXML2.DOMDocument40
  Set oDOM = oXMLHTTP.responseXML
   'Debug.Print oDOM.xml
  Dim oNL As Object
  Dim oCN As Object
  Dim oCC As Object
  Set oNL = oDOM.getElementsByTagName("Address")
  For Each oCN In oNL
    For Each oCC In oCN.childNodes
        Select Case LCase(oCC.nodeName)
            Case "serviceerror"
                If CBool(oCC.Text) = True Then
                    AddrCorrect = "Service Error.  Try again Later"
                    GoTo leaveit
                End If
            Case "addresserror"
                If CBool(oCC.Text) = True Then
                    AddrCorrect = "Address uncorrectable."
                     Debug.Print oDOM.xml
                    GoTo leaveit
                End If
            Case "servicecurrentlyunavailable"
                If CBool(oCC.Text) = True Then
                    AddrCorrect = "Service Unavailable.  Try again Later"
                    GoTo leaveit
                End If
            Case "addressfoundbemorespecific"
                If CBool(oCC.Text) = True Then
                    AddrCorrect = "Address Found.  Be more Specific."
                    GoTo leaveit
                End If
            Case "deliveryaddress"
                address = oCC.Text
            Case "city"
                city = oCC.Text
            Case "stateabbrev"
                state = oCC.Text
            Case "zipcode"
                zip = oCC.Text
        End Select
    Next
  Next
AddrCorrect = "OK" ' Address corrected
  
 
leaveit:
  Set oCC = Nothing
  Set oCN = Nothing
  Set oNL = Nothing
  Set oDOM = Nothing
  Set oXMLHTTP = Nothing
 

End Function


Public Function URLEncode(inS As String) As String
  Dim i As Long
  Dim inC, outC As String
  For i = 1 To Len(inS)
    inC = Mid(inS, i, 1)
    Select Case inC
      Case " "
       outC = "+"
      Case "&"
       outC = "%38"
      Case "!" To "~"
       outC = inC
      Case Else
       outC = "%" + Right("00" + Hex(Asc(inC)), 2)
    End Select
    URLEncode = URLEncode + outC
  Next i
End Function



Paul Rodgers wrote:

>The client asks: Can information in a table (Name, Address, City, etc) on a
>webpage be fed (coded) automatically straight into Access 2k?  
>
>I said, 'I don't think so.' But perhaps it is possible. Anyone any
>experience, please?
>
>All the best
>paul 
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada