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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code To Automatically Refresh Xml Imported Data (RSS Feed), Access 2016    
 
   
rmcgaffic
post Jun 1 2019, 02:11 PM
Post#1



Posts: 171
Joined: 18-June 06



How to you automatically update the import of an RSS feed (XML) into Microsoft Access?

I have successfully imported a CNN news feed on Middle East news into my Access 2016 database using the simple instructions provided in this link:

"http://inv-man.blogspot.com/2011/07/adding-cnn-RSS-feeds-to-microsoft.html" (Sorry for the quotes, but other wise the link is messed up)

I chose the Microsoft Access option "Import structure and data".
The import worked beautifully using the path "Import & Link>New data source>From file>XML file" when I provided the URL link to the CNN RSS feed (http://RSS.cnn.com/RSS/edition_meast.RSS}.

Microsoft Access automatically created all the needed database tables and populated the item's headlines, URL link, publication date, etc. Wonderful!

My question is how do I use VBA code to automatically update these tables say every hour without manually repeating the process, which will choose the option Append data to existing tables.

The author in the above link says his next article will provide details by my google searches are coming up empty handed.

I found only one article in the code archive on RSS and none in the forums.

I thought rather than coding this myself to build a macro and then convert to VBA.

There is a macro folder for Access 2016 Data import/export which contains only the following five items:
Add contact from Outlook
Email database object
Export with formatting
Save as outlook contact
WordMailMerge.

I would like to place a button on my form, and when the user clicks it, the RSS feed information is automatically updated or optionally, to update the information every hour.

Any suggestions would be most appreciated.
This post has been edited by rmcgaffic: Jun 1 2019, 02:20 PM
Attached File(s)
Attached File  CNN_RSS_feed.png ( 269.39K )Number of downloads: 7
 
Go to the top of the page
 
rmcgaffic
post Jun 1 2019, 03:19 PM
Post#2



Posts: 171
Joined: 18-June 06



Sub ImportXMLFilelntoAccessTable()
'Import the MEMBERS XML file that came from the OLSl database
Application.ImportXML _
"E:\Access Power Programming\MEMBERS.xml", acStructureAndData
End Sub

I found the above code in the book Access 2003 Power Progamming with VBA (Chapter 18).

I will test later and report back my findings. Note this is for simple feeds without hierarchical file organization.
Go to the top of the page
 
rmcgaffic
post Jun 2 2019, 04:08 PM
Post#3



Posts: 171
Joined: 18-June 06



Good news!

The code snippet worked.

I have attached my database with a command button to update it.

But as you can see, the update doesn't recognize what records have already been imported and reimports everything.

Will work next on that issue.

Does anyone have any ideas or suggestions on how to do this: How to limit imported items to new items since last import?

Thanks
This post has been edited by rmcgaffic: Jun 2 2019, 05:05 PM
Attached File(s)
Attached File  RSSFEED.zip ( 67.79K )Number of downloads: 3
 
Go to the top of the page
 
sxschech
post Jun 4 2019, 11:18 AM
Post#4



Posts: 12
Joined: 2-October 18



Didn't open your file. If your question about adding only new records, could you do a Left Join or Not exists subquery and append only those records that don't exist?
Go to the top of the page
 
ADezii
post Jun 4 2019, 01:03 PM
Post#5



Posts: 2,460
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. First of all, change the Data Type of the [description] Field from TEXT {255} To MEMO. Data in this Field is getting truncated when Appended.
  2. I am assuming that the [title] Field is Unique. If that is the case then make it the Primary Key, If it is not Unique, then create a Composite Primary Key consisting of Fields that are Unique.
  3. Create the following Sub-Routine Procedure in the Form's Class Module:
    CODE
    Private Sub ImportXML()
    On Error Resume Next
    'Clear Import Errors Table if it exists
    CurrentDb.Execute "DELETE * FROM ImportErrors", dbfailonerror

    On Error GoTo Err_ImportXML
    DoCmd.SetWarnings False
      With Application
        .ImportXML "http://RSS.cnn.com/RSS/edition_meast.RSS", acAppendData
        .RefreshDatabaseWindow
         Forms![frmItem].Requery
      End With
    DoCmd.SetWarnings True

    Exit_ImportXML:
      Exit Sub

    Err_ImportXML:
      If Err.Number <> 31550 Then       'RI violation, let User know
        MsgBox Err.description, vbExclamation, "Error in ImportXML()"
      End If
          Resume Exit_ImportXML
    End Sub
  4. In the Click() Event of your Command Button call this Routine:
    CODE
    Call ImportXML
  5. When the Code is executed, it will DELETE all Records in the ImportErrors Table if it exists.
  6. Only those Records that do not violate the Primary Key Integrity will be Appended while the remainder will populate the ImportErrors Table. This specific Error is trapped (31550) while others are ignored.
  7. Set the Timer Interval Property of the Form to 3600000 (3600000 milliseconds = 60 min. = 1 hour).
  8. In the Timer() Event of the Form call the Sub-Routine:
    CODE
    Private Sub Form_Timer()
      Call ImportXML
    End Sub
  9. The idea is that the Timer() Event will fire every 1 hour calling the ImportXML() Routine. Naturally, the Form must be Open for this to work.

P.S. - Some of the Code is Air Code and some is minimally tested and appears to work quite well. iconfused.gif The rest I leave to you.
This post has been edited by ADezii: Jun 4 2019, 01:04 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 12:38 PM