XML import from https - error

[dlugirapfr]

Hi,
in my database I have macro which download xml file from url https .

Simply macro with that code

Application.ImportXML _
 DataSource:="https:// xxxxxxxx type=.xml", _
 ImportOptions:=acAppendData

It worked perfect. But from some update of Windows 10 or Office 365 32 bit I receive error: Run-time error '-2147024891 (8007005)'
When I press debug I see Visual Basic like this:

I don't know why. When I copy paste url from vba code to web browser I can download manually xml table. If I write in vba not url like https:// but local like c:/base/test.xml it will import file without errors. I think it is something with downloading XML by Access VBA from https.
Do you have similar problem? Please help - it is waste of time to download manually 15 tables from urls.

Karol

 

[theDBguy]

Hi Karol. I don't have O365, so I can't tell you what's wrong. But maybe, you could try using the HttpRequest class.

UtterAccess.com

www.utteraccess.com

 

[cheekybuddha]

I just tried importing an xml file from the web using Access 2007 and it worked (albeit with some import errors).

If there is a new security block with your version of Access, perhaps download the file (through code) and import locally?

 

[dlugirapfr]

I tried to make import by integrated tool. So I press on ribbon -> External data ->New data source -> from XML file and paste URL. When I press OK Access don't do anything. Just close window with URL.
My download xml file from URL I don't need any authorization - just unique url.

 

[dlugirapfr]

Still I have problem with it. Do you have similar problem with it? Do you have link to any sample xml table which I can use to check download process ?

 

[theDBguy]

Still I have problem with it. Do you have similar problem with it? Do you have link to any sample xml table which I can use to check download process ?

Hi. Did you try my suggestion? Just curious.

 

[cheekybuddha]

In addition to DBG, have you tested with another XML source?

Try these tests.

First, paste into a new module the following code (based on the link that DBG gave):

Function HTTP_Get(webServiceURL As String) As String

  Const XMLHTTP             As String = "Microsoft.XMLHTTP", _
        METHOD_GET          As String = "GET", _
        HTTP_STATUS_SUCCESS As Integer = 200
    
  With CreateObject(XMLHTTP)
    .Open METHOD_GET, webServiceURL, False
    .Send
    If .Status = HTTP_STATUS_SUCCESS Then
      HTTP_Get = .ResponseText
      Debug.Print .getAllResponseHeaders
    Else
      Debug.Print .Status & ": " & .StatusText
    End If
  End With
  
End Function

Then, in the Immediate Window (Ctrl+G), run the following:

?HTTP_Get("https://xkcd.com/rss.xml")

Application.ImportXML "https://xkcd.com/rss.xml"

?HTTP_Get("https://Your/URL/yourFile.xml")

Let us know the results.

d

 

[dlugirapfr]

I'm really shocked. I made new empty database and I checked code

Application.ImportXML "https://myurl.xml"

And it works. :O But in my original database doesn't work. I put compact & repair database and it doesn't work.
In my original database I don't have table but link to table.

 

[dlugirapfr]

Ok I found solution.
At head of my code VBA I add

?HTTP_Get("https://mysecreturl/file.xml")

And now everything works ! Thank you very much for your help. Now I will not waste my life time to manually download. But to be honest I don''t know why it works in past and now it doesn't

 

[theDBguy]

Ok I found solution.
At head of my code VBA I add

?HTTP_Get("https://mysecreturl/file.xml")

And now everything works ! Thank you very much for your help. Now I will not waste my life time to manually download. But to be honest I don''t know why it works in past and now it doesn't

Hi. Glad to hear you got it sorted out. Good luck with your project.