UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Web Service In Vba, Access 2016    
 
   
andywal6
post Oct 4 2019, 06:52 AM
Post#21



Posts: 169
Joined: 11-January 08



Hi David
I have attached the wsdl file as a zipped .xml file
I hope you can make sense of it.
Andy

Attached File  PricingService.zip ( 1.8K )Number of downloads: 6
Go to the top of the page
 
cheekybuddha
post Oct 4 2019, 07:20 AM
Post#22


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


>> Do you think that I should put the 'HOST' Header back in? <<

Did you try?

What response did you get?

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Oct 4 2019, 08:39 AM
Post#23



Posts: 169
Joined: 11-January 08



Yes I put it back in and it made no change to the soap fault.

Can you think why they would be using 'tempuri' and not a permanent namespace?
This post has been edited by andywal6: Oct 4 2019, 08:42 AM
Go to the top of the page
 
cheekybuddha
post Oct 4 2019, 11:41 AM
Post#24


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


It's quite common.

You can try and sustitute the other namespace.

I'm afraid it's going to be trial and error for a bit shrug.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 6 2019, 09:36 AM
Post#25



Posts: 169
Joined: 11-January 08



Hi again All
Yes I'm back on this one. but at last we are getting results from our code.
'**Working Version**
CODE
Public Function GetPriceList()

    Dim objHttp As Object
    Dim sURL As String
    Dim sEnv As String
    
     sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
     sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
     sEnv = sEnv & "  <soap:Body>"
     sEnv = sEnv & "   <GetPrices3 xmlns=""http://tempuri.org/"">"
     sEnv = sEnv & "    <rwcust>"
     sEnv = sEnv & "     <UserId>" & lngUser & "</UserId>"
     sEnv = sEnv & "     <Password>" & strPass & "</Password>"
     sEnv = sEnv & "    </rwcust>"
     sEnv = sEnv & "   </GetPrices3>"
     sEnv = sEnv & "  </soap:Body>"
     sEnv = sEnv & "</soap:Envelope>"
    
     sURL = "http://ultranet.ultraframe.co.UK/PricingSvc/PricingService.asmx"
    
    With CreateObject("MSXML2.XMLHTTP")
    .Open "Post", sURL, False
    .setRequestHeader "Host", "ultranet.ultraframe.co.UK"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "SOAPAction", "http://tempuri.org/GetPrices3"
    .setRequestHeader "Content-Length", Len(sEnv)
    .Send sEnv
    Debug.Print .responseText
    End With
    
    GetPriceList = (Err = 0)
End Function

The part that stopped it working before was the ProxyAccount which as it turns out was not necessary.

The response is a huge price list in XML format.
Given that the response expected is as they suggest and as in the code box below.
my question is: how can I convert this daunting response into a Table named "UFPriceList" in my database?

CODE
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length  '**I assume this will be the same as in the request code, i.e. Len(sEnv)**

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetPrices3Response xmlns="http://tempuri.org/">
      <GetPrices3Result>
        <Status>int</Status>
        <Error>string</Error>
        <Parts>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
        </Parts>
        <Services>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
        </Services>
        <Bundles>
          <RWBundle>
            <BundleCode>string</BundleCode>
            <BundleProductCode>string</BundleProductCode>
            <BundleFinish>string</BundleFinish>
            <LongDesc>string</LongDesc>
            <PartCode>string</PartCode>
            <ProductCode>string</ProductCode>
            <PartFinish>string</PartFinish>
            <PartLength>string</PartLength>
            <Quantity>double</Quantity>
          </RWBundle>
          <RWBundle>
            <BundleCode>string</BundleCode>
            <BundleProductCode>string</BundleProductCode>
            <BundleFinish>string</BundleFinish>
            <LongDesc>string</LongDesc>
            <PartCode>string</PartCode>
            <ProductCode>string</ProductCode>
            <PartFinish>string</PartFinish>
            <PartLength>string</PartLength>
            <Quantity>double</Quantity>
          </RWBundle>
        </Bundles>
      </GetPrices3Result>
    </GetPrices3Response>
  </soap:Body>
</soap:Envelope>


If I haven't driven you to distraction, your help or suggestions would be most appreciated.
Andy ^_^
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 10:55 AM
Post#26


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


Hi Andy,

Is that the exact response you receive, or are there actually values where you have the datatypes?

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 6 2019, 01:58 PM
Post#27



Posts: 169
Joined: 11-January 08



Sorry David.
The second code panel shows what they say should be the results of the request.
When I run the code in the first panel it returns a huge output Like this format <PartCode>GHT2331</PartCode><LongDesc>object description text</LongDesc> etc. etc.

We have certainly moved forward and are getting the data requested.

My project now is to find a simple way to parse the responseText to a table in my database.

I didn't know if this code was usable in some form.

Application.ImportXML _
DataSource:=responseText, _
ImportOptions:=acStructureAndData

Thank you for following up.
Andy
This post has been edited by andywal6: Nov 6 2019, 01:59 PM
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 02:47 PM
Post#28


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


OK, cool!

There are multiple ways to get the data into Access tables.

The easiest would probably be to write the response to a file and then use Access' Get External Data button to use the wizard.

You can use a function like the following to write a text file:
CODE
Public Function fTextFileFromString(strFullPath As String, _
                                    strText As String) As Boolean

  Const c_ForWriting As Integer = 2

' Write data to .txt file, using late-bound FSO
  With CreateObject("Scripting.FileSystemObject")
    If .FileExists(strFullPath) Then
      .Deletefile strFullPath
    End If
    With .OpenTextFile(strFullPath, c_ForWriting, True)
      .Write (strText)
      .Close
    End With
  End With
  fTextFileFromString = (Err = 0)

End Function


Instead of Debug.Print .ResponseText you would use:
CODE
  Call fTextFileFromString("C:\Path\To\Save\File.xml", .ResponseText)


The wizard will help you create the tables from the xml, and from there you can insert into your own table structure.

I think once you have done one import you will then be able to use a saved import spec to do future imports programatically.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 6 2019, 06:06 PM
Post#29



Posts: 169
Joined: 11-January 08



Hi David,
We now have a Products price list with 142,000 lines.
What can I say??
Your assistance in this has been invaluable.
You have really gone the extra mile with me and helped me to solve his problem that I have been struggling with for far too long.
I commend you for your efforts.
Many thanks.
Andy..
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 06:31 PM
Post#30


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


Brilliant!

Going forwards, I think you should be able to use the method you described in your previous post, and bypass writing to a file:
CODE
' ...
  Application.ImportXML .ResponseText, acAppendData
' ...


yw.gif

Jack and I are pleased we could assist.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 7 2019, 09:59 AM
Post#31



Posts: 169
Joined: 11-January 08



Thanks for that David, I'll give it a try.
Go to the top of the page
 
andywal6
post Nov 10 2019, 07:02 AM
Post#32



Posts: 169
Joined: 11-January 08



Hi David

It really does look like your suggestion should work,
but unfortunately it doesn't.
This is the error i get when I tried it.

Run time error. '-2146697203(800c000d)
The specified protocol is unknown.

I did place it inside the 'With - End With' statement but no result.

Everything is working now and, for security, it finishes by deleting the .xml file after importing to tables to the database .

I just hope the wise men at Ultraframe don't move the Pricelist database or rename anything or it will all fall down.

Andy....
Go to the top of the page
 
cheekybuddha
post Nov 10 2019, 07:12 AM
Post#33


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


I just checked the help file for Application.ImportXML (blush.gif I should have done this before!), and the first argument is a path to the XML file.

So, you can avoid button clicking and using the wizard, but you must still write the file first (and delete after). The flow would be along the lines of:
CODE
' ...
  Call fTextFileFromString("C:\Path\To\Save\File.xml", .ResponseText)
  Application.ImportXML "C:\Path\To\Save\File.xml", acAppendData
  Kill acAppendData
' ...


:thumbup;

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 10 2019, 07:51 AM
Post#34



Posts: 169
Joined: 11-January 08



This is what I have at the moment.
Works very well from a single click of a button in my database settings for Administrators only

The delete tables is a bit long winded but works fine.

CODE
Public Function fTextFileFromString(strFullPath As String, _
                                    strText As String) As Boolean
  '**Write data to .txt file, using late-bound FSO Cheekybudda David 6/11/19**
  Const c_ForWriting As Integer = 2

  With CreateObject("Scripting.FileSystemObject")
    If .FileExists(strFullPath) Then
      .DeleteFile strFullPath
    End If
    With .OpenTextFile(strFullPath, c_ForWriting, True)
      .Write (strText)
      .Close
    End With
  End With
  
'**Delete existing tables**
Call DeleteIfExists("RWPart")
Call DeleteIfExists("RWBundle")
Call DeleteIfExists("GetPrices2Result")

    '** Re-Create tables**
    Application.ImportXML _
    DataSource:=strFullPath, _
    ImportOptions:=acStructureAndData
    Debug.Print "Tables re-created..."
    
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(strFullPath) Then
        .DeleteFile strFullPath
        End If
    End With
    Debug.Print "xml text file deleted..."
  fTextFileFromString = (Err = 0)

End Function

Public Sub DeleteIfExists(tableName As String)

    '**Delete tabless from calls above**
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        Debug.Print "Table" & tableName & " deleted..."
        DoCmd.SetWarnings True
    End If

End Sub


Andy.
Go to the top of the page
 
cheekybuddha
post Nov 10 2019, 10:32 AM
Post#35


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Nov 10 2019, 11:36 AM
Post#36


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


Just thinking about this ...

Do you need to keep track of historical prices? ie Product X, price on 2019-01-01: £5.00, price on 2019-11-01: £7.00

If so, then maybe you should use the imported tables as staging tables and have a routine to write price changes to your own Prices table?

At the moment it seems as if you lose all historical data each time you re-import.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Nov 10 2019, 02:56 PM
Post#37



Posts: 169
Joined: 11-January 08



Hi David

I believe I know what you are thinking, that if you come back to a stored Purchase Order after updating the tables then the prices will have changed.
I get round that by, saving the Purchase order in the clients folder as a PDF so that it can be returned to in future regardless of price changes.
In this way, the minimum of data need be stored for any given Order i.e. Only the Product Code and the Order Number is stored for any record in the Purchase Order.
All the rest is calculated on the fly in the Purchase Order form.
It is also a good way to monitor how prices change over a given time scale for the same products.
A PDF can be opened at the same time as the old order in the database and the prices compared.

At least I think that's what you mean.

Andy. thanks.gif
Go to the top of the page
 
cheekybuddha
post Nov 10 2019, 04:33 PM
Post#38


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


OK, as long as you have taken it in to account! thumbup.gif

Continued success with your project,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:44 PM