469,332 Members | 6,650 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

Can anyone tell me what MS XML is used for?

XML seems to be a hot technology buzzword. And it appears XML is
supported in A2003.

I am wondering if it could be used in the following scenario. I create
an order record for the customer. This customer is one I do a lot of
business with. I could create an email to send him a copy of the order.
A more preferable approach would be to transfer to the customer the
order and order items parts which could then be imported/used to update
their database system.

Is that possible with XML?
Jan 21 '07 #1
14 3516
Hi, Salad.
Is that possible with XML?
Yes. However, most folks use CSV files to transfer the data, 'cause it's
even simpler and takes less bandwidth.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"salad" <oi*@vinegar.comwrote in message
news:5t*******************@newsread3.news.pas.eart hlink.net...
XML seems to be a hot technology buzzword. And it appears XML is
supported in A2003.

I am wondering if it could be used in the following scenario. I create an
order record for the customer. This customer is one I do a lot of
business with. I could create an email to send him a copy of the order. A
more preferable approach would be to transfer to the customer the order
and order items parts which could then be imported/used to update their
database system.

Is that possible with XML?

Jan 21 '07 #2
rkc
salad wrote:
XML seems to be a hot technology buzzword. And it appears XML is
supported in A2003.

I am wondering if it could be used in the following scenario. I create
an order record for the customer. This customer is one I do a lot of
business with. I could create an email to send him a copy of the order.
A more preferable approach would be to transfer to the customer the
order and order items parts which could then be imported/used to update
their database system.

Is that possible with XML?
Since the advent of ADODB you have been able to save a recordset
to a file and email it to someone to be reopened as a recordset
on their end. The format used to save the recordset is xml.
Jan 21 '07 #3
rkc wrote:
salad wrote:
>XML seems to be a hot technology buzzword. And it appears XML is
supported in A2003.

I am wondering if it could be used in the following scenario. I
create an order record for the customer. This customer is one I do a
lot of business with. I could create an email to send him a copy of
the order. A more preferable approach would be to transfer to the
customer the order and order items parts which could then be
imported/used to update their database system.

Is that possible with XML?


Since the advent of ADODB you have been able to save a recordset
to a file and email it to someone to be reopened as a recordset
on their end. The format used to save the recordset is xml.
I probably gave a poor example, rkc. I was thinking more on the lines
of an debit card transaction. I go to a store, select an item to
purchase. I swipe my card, answer a couple of questions (in my
situation I could pass those as parameters to the "bank"), the bank
processes the request, and returns a string of data the tells the store
I've been approved, etc.

Emails would add a layer of complexity to the issue. Maybe not so
complex but adds a layer where the person has to go into the email and
perhaps save the email or attachment to a folder for processing.

A CSV file, like Gunny mentioned, would be just fine. I'd prefer to
have a communication thread with the computer with little to none in the
way of user intervention.
Jan 21 '07 #4
salad <oi*@vinegar.comwrote in
news:cp*******************@newsread4.news.pas.eart hlink.net:
Emails would add a layer of complexity to the issue. Maybe not so
complex but adds a layer where the person has to go into the email
and perhaps save the email or attachment to a folder for
processing.

A CSV file, like Gunny mentioned, would be just fine. I'd prefer
to have a communication thread with the computer with little to
none in the way of user intervention.
Look into examples of using the MS XML libraries. This can allow you
to post and retrieve data directly to and from a web page (assuming
there's a script on the web page to handle the transaction and
return the desired results). If I'm remembering correctly, Douglas
Steele has done quite a bit with this and published articles
explaining how it's done. I haven't done it myself, but I'm going to
implement my first such project using it in the next month or so.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 22 '07 #5
David W. Fenton wrote:
salad <oi*@vinegar.comwrote in
news:cp*******************@newsread4.news.pas.eart hlink.net:

>>Emails would add a layer of complexity to the issue. Maybe not so
complex but adds a layer where the person has to go into the email
and perhaps save the email or attachment to a folder for
processing.

A CSV file, like Gunny mentioned, would be just fine. I'd prefer
to have a communication thread with the computer with little to
none in the way of user intervention.


Look into examples of using the MS XML libraries.
Hi David:
Appreciate your POV. Are you referring to Access help? Or an MS KB
article?

This can allow you
to post and retrieve data directly to and from a web page (assuming
there's a script on the web page to handle the transaction and
return the desired results). If I'm remembering correctly, Douglas
Steele has done quite a bit with this and published articles
explaining how it's done. I haven't done it myself, but I'm going to
implement my first such project using it in the next month or so.
I wish you success and maybe you will share your experiences.

Maybe I'm approaching this wrong. I'll provide another example.

I use Norton Anti-virus. I can open up Norton and check to see if there
are new virus definitions. It will return to my computer the newest
definitions if they exist.

A better example tho would be using the web site. Let's say I have a
quotes system. I open up a web page at HandBagsRUs.com for their
catalog. If needed, I could pass to it username/password to it. At
that time the system knows who I am, who my salesperson is at the
company, what discount rates I get, etc.

I go an select the handbags I want to order and sell. Once I have made
my selections, perhaps by adding them to a shopping cart, I submit my
request for those items, maybe specify this is a quote or PO.

The computer over at HandBagsRUs.com churns away for a second or two and
now has my quote. It can return the results to the web page. I really
don't want that. What I would like is the electronic results be sent to
my computer...if that is possible.

Maybe the results could be returned to me via FTP? Would that be a
better solution?

It would be great if the results could simply be dumped on my computer
and my program could parse the results out.

Some web sites do something "close". The quote might be generated and
then reside at the vendors site. This is pretty much useless. They can
have our quote on their machine but I want the quote on my machine as well.

So I am looking for a communcation from the vendor I can use to update
my application. I believe XML is used to communicate between computers
and was wondering if it could be used in a manner I described. If FTP
is a more doable option that would be great too.


Jan 22 '07 #6
>So I am looking for a communcation from the vendor I can use to update my
>application.
Are you looking to build some kind of "live update" mechanism so your
application can retrieve updates over the web? If so, then why didn't
you just say that from the outset?
>I believe XML is used to communicate between computers and was
wondering if it could be used in a manner I described. If FTP is a
more doable
option that would be great too.

Now you are starting to discuss apples and oranges.

XML is a file format, as is CSV. CSV is simpler and more compact but it
also corresponds to just a single table. XML is more complex but is
also more flexible in that it can describe parent/child relationships
in data, e.g.

<?xml version="1.0" encoding="UTF-8" ?>
<Document>
<Purchase_Order>
<PO_Number>10000</PO_Number>
<PO_Line_Item>
<Item_Number>1</Item_Number>
<SKU>FOO123</SKU>
<Quantity>3</Quantity>
</PO_Line_Item>
<PO_Line_Item>
<Item_Number>2</Item_Number>
<SKU>BAR234</SKU>
<Quantity>5</Quantity>
</PO_Line_Item>
</Purchase_Order>
<Purchase_Order>
<PO_Number>10001</PO_Number>
<PO_Line_Item>
<Item_Number>1</Item_Number>
<SKU>BAZ345</SKU>
<Quantity>21</Quantity>
</PO_Line_Item>
</Purchase_Order>
</Document>
FTP is a transfer protocol, as is HTTP. Both protocols can transmit
data in different formats (such as CSV, XML, HTML, ...).

salad wrote:
David W. Fenton wrote:
salad <oi*@vinegar.comwrote in
news:cp*******************@newsread4.news.pas.eart hlink.net:

>Emails would add a layer of complexity to the issue. Maybe not so
complex but adds a layer where the person has to go into the email
and perhaps save the email or attachment to a folder for
processing.

A CSV file, like Gunny mentioned, would be just fine. I'd prefer
to have a communication thread with the computer with little to
none in the way of user intervention.

Look into examples of using the MS XML libraries.

Hi David:
Appreciate your POV. Are you referring to Access help? Or an MS KB
article?

This can allow you
to post and retrieve data directly to and from a web page (assuming
there's a script on the web page to handle the transaction and
return the desired results). If I'm remembering correctly, Douglas
Steele has done quite a bit with this and published articles
explaining how it's done. I haven't done it myself, but I'm going to
implement my first such project using it in the next month or so.
I wish you success and maybe you will share your experiences.

Maybe I'm approaching this wrong. I'll provide another example.

I use Norton Anti-virus. I can open up Norton and check to see if there
are new virus definitions. It will return to my computer the newest
definitions if they exist.

A better example tho would be using the web site. Let's say I have a
quotes system. I open up a web page at HandBagsRUs.com for their
catalog. If needed, I could pass to it username/password to it. At
that time the system knows who I am, who my salesperson is at the
company, what discount rates I get, etc.

I go an select the handbags I want to order and sell. Once I have made
my selections, perhaps by adding them to a shopping cart, I submit my
request for those items, maybe specify this is a quote or PO.

The computer over at HandBagsRUs.com churns away for a second or two and
now has my quote. It can return the results to the web page. I really
don't want that. What I would like is the electronic results be sent to
my computer...if that is possible.

Maybe the results could be returned to me via FTP? Would that be a
better solution?

It would be great if the results could simply be dumped on my computer
and my program could parse the results out.

Some web sites do something "close". The quote might be generated and
then reside at the vendors site. This is pretty much useless. They can
have our quote on their machine but I want the quote on my machine as well.

So I am looking for a communcation from the vendor I can use to update
my application. I believe XML is used to communicate between computers
and was wondering if it could be used in a manner I described. If FTP
is a more doable option that would be great too.
Jan 22 '07 #7
Gord wrote:
>>So I am looking for a communcation from the vendor I can use to update my
application.


Are you looking to build some kind of "live update" mechanism so your
application can retrieve updates over the web?
Live would be fine. I don't know if it is possible.
If so, then why didn't you just say that from the outset?
If one doesn't know exactly what to ask...
>
>>I believe XML is used to communicate between computers and was
wondering if it could be used in a manner I described. If FTP is a
more doable option that would be great too.

Now you are starting to discuss apples and oranges.

XML is a file format, as is CSV. CSV is simpler and more compact but it
also corresponds to just a single table. XML is more complex but is
also more flexible in that it can describe parent/child relationships
in data, e.g.

<?xml version="1.0" encoding="UTF-8" ?>
<Document>
<Purchase_Order>
<PO_Number>10000</PO_Number>
<PO_Line_Item>
<Item_Number>1</Item_Number>
<SKU>FOO123</SKU>
<Quantity>3</Quantity>
</PO_Line_Item>
<PO_Line_Item>
<Item_Number>2</Item_Number>
<SKU>BAR234</SKU>
<Quantity>5</Quantity>
</PO_Line_Item>
</Purchase_Order>
<Purchase_Order>
<PO_Number>10001</PO_Number>
<PO_Line_Item>
<Item_Number>1</Item_Number>
<SKU>BAZ345</SKU>
<Quantity>21</Quantity>
</PO_Line_Item>
</Purchase_Order>
</Document>

FTP is a transfer protocol, as is HTTP. Both protocols can transmit
data in different formats (such as CSV, XML, HTML, ...).
I guess I'm approaching this as someone not conversant in the subject.
If I were an expert I wouldn't be asking.

I want to be able to select the items to order from a web site, submit
my request, and have the results returned to me. I care not if it is
FTP/HTTP at this point, I want to know if what I would like to do is
possible. The file returned can be in CSV, Excel, XLM formats, that
makes no difference to me at this point. I would prefer the results of
a downloadable file, IOW link, not be returned to me in a web page
because of the file/open dialog box hassles of just getting to the
folder to store it in.

Currently I get on a computer site, search around it selecting items I
want to buy. I know the computer can send me back results but when I'm
sitting in front of a browser I get results back as a html page. A
formatted web page of results is not what I want. I want the results on
my computer. Some folk say screen scraping is the way to go. To me
that seems ill advised.

I know that I will need to work with/ask the vendor's web/IT staff to do
something like this...if it's possible. I know the technology exists,
just not sure how it's implemented.

>
salad wrote:
>>David W. Fenton wrote:

>>>salad <oi*@vinegar.comwrote in
news:cp*******************@newsread4.news.pas.e arthlink.net:

Emails would add a layer of complexity to the issue. Maybe not so
complex but adds a layer where the person has to go into the email
and perhaps save the email or attachment to a folder for
processing.

A CSV file, like Gunny mentioned, would be just fine. I'd prefer
to have a communication thread with the computer with little to
none in the way of user intervention.
Look into examples of using the MS XML libraries.

Hi David:
Appreciate your POV. Are you referring to Access help? Or an MS KB
article?

This can allow you
>>>to post and retrieve data directly to and from a web page (assuming
there's a script on the web page to handle the transaction and
return the desired results). If I'm remembering correctly, Douglas
Steele has done quite a bit with this and published articles
explaining how it's done. I haven't done it myself, but I'm going to
implement my first such project using it in the next month or so.

I wish you success and maybe you will share your experiences.

Maybe I'm approaching this wrong. I'll provide another example.

I use Norton Anti-virus. I can open up Norton and check to see if there
are new virus definitions. It will return to my computer the newest
definitions if they exist.

A better example tho would be using the web site. Let's say I have a
quotes system. I open up a web page at HandBagsRUs.com for their
catalog. If needed, I could pass to it username/password to it. At
that time the system knows who I am, who my salesperson is at the
company, what discount rates I get, etc.

I go an select the handbags I want to order and sell. Once I have made
my selections, perhaps by adding them to a shopping cart, I submit my
request for those items, maybe specify this is a quote or PO.

The computer over at HandBagsRUs.com churns away for a second or two and
now has my quote. It can return the results to the web page. I really
don't want that. What I would like is the electronic results be sent to
my computer...if that is possible.

Maybe the results could be returned to me via FTP? Would that be a
better solution?

It would be great if the results could simply be dumped on my computer
and my program could parse the results out.

Some web sites do something "close". The quote might be generated and
then reside at the vendors site. This is pretty much useless. They can
have our quote on their machine but I want the quote on my machine as well.

So I am looking for a communcation from the vendor I can use to update
my application. I believe XML is used to communicate between computers
and was wondering if it could be used in a manner I described. If FTP
is a more doable option that would be great too.

Jan 23 '07 #8
rkc
salad wrote:
I guess I'm approaching this as someone not conversant in the subject.
If I were an expert I wouldn't be asking.

What you're talking about (I think) is called a web service.
Google it. While you're at it, Google XMLHttp Request. It's one of the
easy ways you can communicate with a web service from Access using VBA.
Other ways are automating Internet Explorer or using the web browser
control.
Jan 23 '07 #9
I am still having trouble understanding what you want to do.

This a beta procedure that gets appointments from my Google (Web)
Calendar and puts them into Outlook (it's run in Outlook, not Access.)
It uses XMLHTTP. It requires a reference to MS XML, v6.0. It adds a
hundred appointments in two or three seconds. (RKC introduced me to
XMLHTTP):
Public Sub AddNewCalendarItems()
Dim ai As AppointmentItem
Dim a$()
Dim d$()
Dim s$
Dim x As XMLHTTP60

Dim z&

Dim pDescription$
Dim pEnd As Date
Dim pLocation$
Dim pStart As Date
Dim pSubject$
Dim pBase As Date
Dim pMinDate As Date
Dim pMaxDate As Date

pMinDate = DateAdd("m", -1, Date)
pMaxDate = DateAdd("yyyy", 1, Date)

Set x = New XMLHTTP60
With x
.Open "GET",
"http://www.google.com/calendar/ical/abigalphanumericstringgroup.calendar.google.com/private-abigalphanumericstring
1b/basic.ics", False
.Send
s = .responseText
End With
Debug.Print s
a = Split(s, vbNewLine)
For z = LBound(a) To UBound(a) - 1
Set ai = CreateItem(olAppointmentItem)
d = Split(a(z), ":")
Select Case d(0)
Case Is = "DESCRIPTION"
pDescription = d(1)
Case Is = "LOCATION"
pLocation = d(1)
Case Is = "SUMMARY"
pSubject = d(1)
Case Is = "DTSTART;VALUE=DATE"
pStart = DateSerial(Mid$(d(1), 1, 4), Mid$(d(1), 5, 2),
Mid$(d(1), 7, 2))
If Len(d(1)) 8 Then _
pStart = pStart + TimeSerial(Mid$(d(1), 9, 2),
Mid$(d(1), 11, 2), Mid$(d(1), 13, 2))
Case Is = "DTEND;VALUE=DATE"
pEnd = DateSerial(Mid$(d(1), 1, 4), Mid$(d(1), 5, 2),
Mid$(d(1), 7, 2))
If Len(d(1)) 8 Then _
pEnd = pEnd + TimeSerial(Mid$(d(1), 9, 2),
Mid$(d(1), 11, 2), Mid$(d(1), 13, 2))
Case Is = "END"
If d(1) = "VEVENT" And _
Len(pSubject) 0 And _
pStart >= pMinDate And _
pStart <= pMaxDate Then
With ai
.Subject = pSubject
If Len(pDescription) 0 Then _
.Body = pDescription
If Len(pLocation) 0 Then _
.Location = pLocation
.Start = pStart
.End = pEnd
.Save
End With
End If
pDescription = vbNullString
pLocation = vbNullString
pStart = pBase
pEnd = pBase
pSubject = vbNullString
Case Else
End Select
Set ai = Nothing
Next z
End Sub

Public Sub RemoveAllCalendarItems()
Dim mf As MAPIFolder
Dim ns As NameSpace
Dim z As Long
Set ns = GetNamespace("MAPI")
Set mf = ns.GetDefaultFolder(olFolderCalendar)
With mf.Items
For z = .Count To 1 Step -1
.Remove z
Next z
End With
End Sub

Jan 23 '07 #10
rkc wrote:
salad wrote:
>I guess I'm approaching this as someone not conversant in the subject.
If I were an expert I wouldn't be asking.

What you're talking about (I think) is called a web service.
Google it. While you're at it, Google XMLHttp Request. It's one of the
easy ways you can communicate with a web service from Access using VBA.
Other ways are automating Internet Explorer or using the web browser
control.
Thank you. I'll certainly check it out.
Jan 23 '07 #11
Lyle Fairfield wrote:
I am still having trouble understanding what you want to do.
Thanks Lyle. I see that there are some possibilities based on rkc's and
your responses.

What do I want to do?

I have a list of items I need to buy. I want to check around and get
some quotes from my vendors. So I go to one of my vendor's web site.

At their site, they have a catalog of items they sell. I search for the
items I will need to purchase. As I find the items, I add it to the
shopping cart at their site, or something similar to a cart, and go on
to the next item I need to buy and get a quote for.

At some point, I have found all of the items on my list that they carry.
At this point, I submit my quote request.

The quote could be generated, on the vendor side, for me immediately if
it has that capability. Instead of a HTML web page being returned to me
with a list of all of the items with prices, discounts, shipping etc and
a prompt to print the page, I want the items returned to me as a file of
some type or as a data stream to my computer system or network. I don't
need a page with the results to print as a confirmation.

I suppose they could create the file and return me a page to download
their quote data. This, I think, would be a manual process since the
user is now forced to go thru a File/Open dialog box and find the folder
the file is to be downloaded to. This could be time consuming
traversing the folder path to download the file to.

I would prefer that after I submit the request the file or data stream
is returned to my computer or network. My application or another
service will accept/retrieve the results and populate my quote record
with the information I requested from their web site.

I do realize that the vendor may not have this capability in place. In
fact, at this time, I know they don't. That is not the issue. I want
to know if the vendor can transmit this info to me after I have
submitted my request for a quote from their web page or is there a way I
can get it from their site if they add the capability. If it can be
done, would FTP work? Web services? Something else? If yes to more
than of these methods, what method would be the easiest to implement?

Any clarification you can provide is appreciated.
>
This a beta procedure that gets appointments from my Google (Web)
Calendar and puts them into Outlook (it's run in Outlook, not Access.)
It uses XMLHTTP. It requires a reference to MS XML, v6.0. It adds a
hundred appointments in two or three seconds. (RKC introduced me to
XMLHTTP):
Public Sub AddNewCalendarItems()
Dim ai As AppointmentItem
Dim a$()
Dim d$()
Dim s$
Dim x As XMLHTTP60

Dim z&

Dim pDescription$
Dim pEnd As Date
Dim pLocation$
Dim pStart As Date
Dim pSubject$
Dim pBase As Date
Dim pMinDate As Date
Dim pMaxDate As Date

pMinDate = DateAdd("m", -1, Date)
pMaxDate = DateAdd("yyyy", 1, Date)

Set x = New XMLHTTP60
With x
.Open "GET",
"http://www.google.com/calendar/ical/abigalphanumericstringgroup.calendar.google.com/private-abigalphanumericstring
1b/basic.ics", False
.Send
s = .responseText
End With
Debug.Print s
a = Split(s, vbNewLine)
For z = LBound(a) To UBound(a) - 1
Set ai = CreateItem(olAppointmentItem)
d = Split(a(z), ":")
Select Case d(0)
Case Is = "DESCRIPTION"
pDescription = d(1)
Case Is = "LOCATION"
pLocation = d(1)
Case Is = "SUMMARY"
pSubject = d(1)
Case Is = "DTSTART;VALUE=DATE"
pStart = DateSerial(Mid$(d(1), 1, 4), Mid$(d(1), 5, 2),
Mid$(d(1), 7, 2))
If Len(d(1)) 8 Then _
pStart = pStart + TimeSerial(Mid$(d(1), 9, 2),
Mid$(d(1), 11, 2), Mid$(d(1), 13, 2))
Case Is = "DTEND;VALUE=DATE"
pEnd = DateSerial(Mid$(d(1), 1, 4), Mid$(d(1), 5, 2),
Mid$(d(1), 7, 2))
If Len(d(1)) 8 Then _
pEnd = pEnd + TimeSerial(Mid$(d(1), 9, 2),
Mid$(d(1), 11, 2), Mid$(d(1), 13, 2))
Case Is = "END"
If d(1) = "VEVENT" And _
Len(pSubject) 0 And _
pStart >= pMinDate And _
pStart <= pMaxDate Then
With ai
.Subject = pSubject
If Len(pDescription) 0 Then _
.Body = pDescription
If Len(pLocation) 0 Then _
.Location = pLocation
.Start = pStart
.End = pEnd
.Save
End With
End If
pDescription = vbNullString
pLocation = vbNullString
pStart = pBase
pEnd = pBase
pSubject = vbNullString
Case Else
End Select
Set ai = Nothing
Next z
End Sub

Public Sub RemoveAllCalendarItems()
Dim mf As MAPIFolder
Dim ns As NameSpace
Dim z As Long
Set ns = GetNamespace("MAPI")
Set mf = ns.GetDefaultFolder(olFolderCalendar)
With mf.Items
For z = .Count To 1 Step -1
.Remove z
Next z
End With
End Sub
Jan 23 '07 #12
MLH
<snip>
Salad, my XML calls are limited to sending data to a server
and snatching what I want from the HTML string returned
by the site. You have to familiarize yourself with the HTML
Tags received in the text block, find those that delimit the
stuff you want, parse out what you want and write it to your
tables. Here's an example...

Private Sub cmdAddrVerify_Click()
'************************************************* *************************************
' Hit the USPS Address Verification Server
'************************************************* *************************************
On Error GoTo ErrcmdAddrVerify_Click
Dim ThisForm As String, WaitTime As Byte, Addr As String, City As
String, State As String, Zip As String, PString As String
Dim msXML As Object, strPageContent As String, MyURL As String, Point1
As Integer, Point2 As Integer, Length As Integer
ThisForm = Me.Name

Commence:
100 If DCount("[TowCoID]", "qryFiniDataUnverified") = 0 Then
110 MsgBox "All records in table FinishedData have been Address
Verified on the USPS Production Server."
120 Exit Sub
130 End If
140 Addr = DLookup("[TowCoAddrLine1]", "qryFiniDataUnverified")
'query returns only a single record
150 City = DLookup("[TowCoCity]", "qryFiniDataUnverified")
160 State = DLookup("[TowCoState]", "qryFiniDataUnverified")
170 Zip = Nz(DLookup("[TowCoZip]", "qryFiniDataUnverified"), "")
180 Set msXML = CreateObject("Microsoft.XMLHTTP")
190 Debug.Print "Sending USPS Production Server an UNVERIFIED
address:"
200 Debug.Print Addr & " " & City & " " & State & " " & Zip
210 Debug.Print "Awaiting reply comprising CLEANSED address..." &
vbNewLine
270 MyURL = "
http://Production.ShippingAPIs.com/ShippingAPI.dll?API=Verify&XML=<AddressValidateReq uest%20USERID="
& Chr$(34) & "ABCDEFGHIJKLMNOP"
280 MyURL = MyURL & Chr$(34) & ">" & "<Address ID=" & Chr$(34) & "1" &
Chr$(34) & "><Address1></Address1>"
290 MyURL = MyURL & "<Address2>" & Addr & "</Address2><City>" & City &
"</City><State>NC</State>"
300 MyURL = MyURL & "<Zip5>" & Zip &
"</Zip5><Zip4></Zip4></Address></AddressValidateRequest>"
320 msXML.Open "GET", MyURL, False
330 msXML.Send
340 strPageContent = msXML.ResponseText
360 Set msXML = Nothing
365 DoCmd.SetWarnings False
370 If InStr(1, strPageContent, "<Error>") Then
'suck out the error number and write it to [NewAddrLine1]
Point1 = InStr(1, strPageContent, "<Number>") + 8
Point2 = InStr(1, strPageContent, "</Number>")
Length = Point2 - Point1
GString = Mid$(strPageContent, Point1, Length)
Debug.Print "Error", GString
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.NewAddrLine1 = GetGString();" 'GString is a
global string var
DoCmd.RunSQL MySQL
Else
'suck out the address and write it to [NewAddrLine1]
Point1 = InStr(1, strPageContent, "<Address2>") + 10
Point2 = InStr(1, strPageContent, "</Address2>")
Length = Point2 - Point1
GString = Mid$(strPageContent, Point1, Length)
Debug.Print GString
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.NewAddrLine1 = GetGString();" 'GString is a
global string var
DoCmd.RunSQL MySQL
'suck out the city and write it to [NewCity]
Point1 = InStr(1, strPageContent, "<City>") + 6
Point2 = InStr(1, strPageContent, "</City>")
Length = Point2 - Point1
GString = Mid$(strPageContent, Point1, Length)
Debug.Print GString
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.NewCity = GetGString();" 'GString is a global
string var
DoCmd.RunSQL MySQL
'suck out the state and write it to [NewState]
Point1 = InStr(1, strPageContent, "<State>") + 7
Point2 = InStr(1, strPageContent, "</State>")
Length = Point2 - Point1
GString = Mid$(strPageContent, Point1, Length)
Debug.Print GString
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.NewState = GetGString();" 'GString is a global
string var
DoCmd.RunSQL MySQL
'suck out the zip5 and zip4, concat 'em and write result to
[NewZip]
Point1 = InStr(1, strPageContent, "<Zip5>") + 6
Point2 = InStr(1, strPageContent, "</Zip5>")
Length = Point2 - Point1
GString = Mid$(strPageContent, Point1, Length)
Point1 = InStr(1, strPageContent, "<Zip4>") + 6
Point2 = InStr(1, strPageContent, "</Zip4>")
Length = Point2 - Point1
GString = GString & Mid$(strPageContent, Point1, Length)
Debug.Print GString
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.NewZip = GetGString();" 'GString is a global
string var
DoCmd.RunSQL MySQL
End If
'Update the [Verified] field to Now()
MySQL = "UPDATE qryFiniDataUnverified SET
qryFiniDataUnverified.Verified = Now();"
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
GoTo Commence

ExitcmdAddrVerify_Click:
Exit Sub

ErrcmdAddrVerify_Click:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
cmdAddrVerify_Click, line #" & Trim$(CStr(Erl)) & ", CBF on " &
ThisForm & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, vbExclamation, "Unexpected Error - " & MyApp$ &
", rev. " & MY_VERSION$
GoTo ExitcmdAddrVerify_Click

End Sub
Jan 23 '07 #13
salad wrote:
I do realize that the vendor may not have this capability in place. In
fact, at this time, I know they don't. That is not the issue. I want
to know if the vendor can transmit this info to me after I have
submitted my request for a quote from their web page or is there a way I
can get it from their site if they add the capability. If it can be
done, would FTP work? Web services? Something else? If yes to more
than of these methods, what method would be the easiest to implement?
TBOMK XMLHTTP will grab whatever shows on the web page. I don't think
it's a question of whether the web page has some capability in place.
On the other hand, perhaps there is a technology that disallows an
XMLHTTP query, but it's hard to see how it will work; the html has to
be downloaded to your computer in order for the browser to display it;
so it's there; how could anything stop its being used? If I had to I
would try some tricks, but probably they could be circumvented.
On the other hand FTP must be authorized. It's easy to turn off, if it
is ever there in the first place.
MS has two technologies that greatly simplify getting the substance of
the data without the form.
One is the Web Control which can be placed on an Access form. One can
navigate to a web page with this and then get that page's innerHTML,
which is the information without the format.
The other is HTA. HTAs are completely trusted (which is why it's a good
idea to use only your own, or those that are REALLY trusted). So one
can use XMLHTTP to get the HTML from a webpage, open one's own window
to view that HTML, and then, again, use innerHTML to get the substance
of the page. HTA's are much skinnier than the MS Web Control but, in
this situation their outcome is similar.
I use an HTA to get Geocache file information and transform it into a
simple format for my Palm pilot.

Jan 23 '07 #14
Lyle Fairfield wrote:
salad wrote:

>>I do realize that the vendor may not have this capability in place. In
fact, at this time, I know they don't. That is not the issue. I want
to know if the vendor can transmit this info to me after I have
submitted my request for a quote from their web page or is there a way I
can get it from their site if they add the capability. If it can be
done, would FTP work? Web services? Something else? If yes to more
than of these methods, what method would be the easiest to implement?


TBOMK XMLHTTP will grab whatever shows on the web page. I don't think
it's a question of whether the web page has some capability in place.
On the other hand, perhaps there is a technology that disallows an
XMLHTTP query, but it's hard to see how it will work; the html has to
be downloaded to your computer in order for the browser to display it;
so it's there; how could anything stop its being used? If I had to I
would try some tricks, but probably they could be circumvented.
On the other hand FTP must be authorized. It's easy to turn off, if it
is ever there in the first place.
MS has two technologies that greatly simplify getting the substance of
the data without the form.
One is the Web Control which can be placed on an Access form. One can
navigate to a web page with this and then get that page's innerHTML,
which is the information without the format.
The other is HTA. HTAs are completely trusted (which is why it's a good
idea to use only your own, or those that are REALLY trusted). So one
can use XMLHTTP to get the HTML from a webpage, open one's own window
to view that HTML, and then, again, use innerHTML to get the substance
of the page. HTA's are much skinnier than the MS Web Control but, in
this situation their outcome is similar.
I use an HTA to get Geocache file information and transform it into a
simple format for my Palm pilot.
Thank's for the pointer to HTAs. I've got a lot to learn.
Jan 24 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Bosconian | last post: by
11 posts views Thread by Robert Schuldenfrei | last post: by
5 posts views Thread by tony | last post: by
66 posts views Thread by QuantumG | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.