Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Expert 23

Next: Access Expert 25 >

Access Expert Level 24

Expert Microsoft Access Tutorial - 1 Hour, 33 Minutes
 
 
In Access Expert 24 we will learn how to import real-time data from web sites using their RSS feeds. We will display current news and weather conditions. Then, we will learn how to share our Access data with others over the Internet by setting up an inexpensive and easy-to-use online SQL Server database that will host our tables. Topics include:
 
  - Import RSS Feed Data
  - Download Latest News from Web Sites
  - Display Current Weather Conditions

  - Use the WebBrowser Control
  - Display Images from a Web Page
  - Program Timer Events
  - Launch a Macro at Timed Intervals
  -
Setup Online SQL Server Database

  - Create ODBC Connections & DSN Files
  -
Share Access Data via the Internet

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 24
Description: Access Expert Level 24
Versions: Recorded with Access 2013. Most of the material should work with 2007 and 2010 but I'm not absolutely positive. These lessons definitely will not all work with Access 2003.
Pre-Requisites: Access Expert Level 23 strongly recommended. This class builds on the import/export topics covered in the last couple of classes.
Running Time: 1 Hour, 33 Minutes
Cost: $26.99


Today we will begin by importing RSS feed data from web sites. This allows you to pull in any data that the web site decides to share, and that can include current events, news, weather conditions, blog posts, etc. Many popular web sites offer RSS feed (news feed) data via XML files that you can find by searching their site. Look for the RSS logo:

rss feed logo

 

We will pull in the Latest News feed from MSNBC.com and the Tech News feed from CNN.com. Once you have it imported into your database, you can do whatever you like with it. These lessons will show you how to import it.

Next, we will learn how to display current weather conditions using the same technique. There are a couple of free weather feeds out there, including Weather.com, but if you want the best (free) source of weather data, you need to use Weather Underground. I'll show you how to register for a free API key (so you can use their site) and pull down all of the weather data you can imagine.

current weather conditions

 

We'll make a button that you can click once to update the data, and you'll learn how to use a form event timer to have the data automatically refresh itself every couple of minutes. We'll also use the WebBrowser control in an Access form so we can display a picture from their web site (the little logo for the current weather conditions).

weather form webbrowser control event timer

 

Finally, we'll cover one of the most requested topics that people email me about: how to share your Access data with people over the Internet. Everyone wants to know how to take your Access database, upload the tables to the Web somehow, and then share the database with others in different locations. I will show you how to set up an SQL Server database on a web server. I will use GoDaddy for this. They are my ISP and I've been with them for about 5 years right now (it's currently July 2014). As of the time of this class, you can get web hosting with unlimited SQL Server databases for about $5 per month, so it's an easy, inexpensive solution if you want to share data.

sql server access

 

I will show you how to configure the SQL Server database on GoDaddy's site (most other ISPs that I've worked with are very similar). We will then connect to the database from our Access application, upload our tables, delete the local tables, and then link to the tables on the SQL Server. Once that's done, you can then share your Access front-end file with anyone you want (and trust) and they can use your data. You can both share information no matter where you are. It's a quick-and-dirty way to allow remote access to your data via an Access database.

linked sql server tables

 

This is the 24th class in the Access Expert series. There are a lot of highly-requested topics in this class: importing live data from the web, using the WebBrowser control, launching timer events, and sharing your Access data over the Internet. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 24

00. Intro (9:47)

01. Import RSS Feed Data (14:21)
Pull News from Web Site
RSS Feeds are XML Files
Really Simple Syndication
Get the 599CD News Feed
External Data > Import XML
ImportErrors Table
Channel and Item Tables
GUID: Globally Unique Identifier
Field truncation
Can't Save Import Steps if Error
Import Latest News from MSNBC.com
Import Tech News from CNN.com


02. Import Real-Time Weather (14:02)
How to find an RSS Feed not advertised
Weather.com RSS Feeds
Weather Underground API
Register for an API key
Import Weather XML Feed
03. Display Real-Time Weather (20:56)
Convert RFC822 to Access DateTime Value
Query Sorted by Observation Date
Form to Display Current Conditions
Button to Run Macro for Import
RunSavedImportExport
Requery Form Data
WebBrowser Control
Display Images from a Web Site
Event Timer
Timer Interval
On Timer Event

04. Online SQL Server Database (28:39)
Share Access Data via Web Server
Use Access Front End, SQL Tables
Set up SQL Server DB on GoDaddy
Database Friendly Name
Username and Password
Export to ODBC Database
File Data Source
Machine Data Source
User Data Source
Create New Data Source > SQL Server
Set up a File DSN
Complete Export of CustomerT
Link to the CustomerT on SQL Server

05. Review (5:47)

 


 
Keywords: Import RSS Feeds from Web Sites, Display Real-Time Weather, Share Data with SQL Server Online Database, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, Pull Data from News Feed, Import XML Web Data, Get News from MSNBC and CNN, Weather Underground, RunSavedImportExport, WebBrowser Control, On Timer Interval, Timer Event, Share Access Data via Web, SQL Server on GoDaddy, ODBC Database, DSN Data Source Name
 
 

Student Interaction: Microsoft Access Expert 24

Richard on 7/16/2014:  Microsoft Access Expert Level 24 is 1 hour, 33 minutes long. In this class we will learn how to import real-time data from web sites using their RSS feeds. We will display current news and weather conditions. Then, we will learn how to share our Access data with others over the Internet by setting up an inexpensive and easy-to-use online SQL Server database that will host our tables. Topics include: - Import RSS Feed Data - Download Latest News from Web Sites - Display Current Weather Conditions - Use the WebBrowser Control - Display Images from a Web Page - Program Timer Events - Launch a Macro at Timed Intervals - Setup Online SQL Server Database - Create ODBC Connections & DSN Files - Share Access Data via the Internet Click here for more information on Access Expert Level 24, including a course outline, sample videos, and more. This course was recorded using Access 2013. This class follows Expert Level 23. The next class in the series is Access Expert 25.
Patrick Russell on 7/16/2014: Thanks Richard for the Online SQL Server segment. I would love to see more both with SQL Server and SharePoint.
Robert Miller on 7/22/2014: Richard, I have been waiting for a LONG time for this lesson on SQL server databases! Thank you so much!

I have a question: Is it possible to set all this up and then share a version of the Access database with someone that does not have Access on their machine? I know a few people that have MS Office but not the version with Access so I cannot share the front-end database with them.

Thanks again for all of this information and for everything you do!

Rob


Reply from Richard Rost:

You SHOULD be able to create an Access database, connect it to your SQL Server backend, and then package and deploy that database using the Access Runtime Edition which means they wouldn't need a purchased copy of Access - HOWEVER I haven't tried this myself, so this is just a theory. Let me know if it works.

James Gray on 7/23/2014: Rick,
This would be a great way to have all the members of our volunteer EMS staff sign up for their standby time right? I could just upload the ManningT to the web, have them use the database through a form designed specifically for sign ups and it would then be just like me doing it from the "master" copy. Am I thinking straigt?

Reply from Richard Rost:

Yep. Just remember... whoever has access to that front-end database has the password for your tables. It's contained in the Access database and a hacker can get it out if determined.

Jim Gray on 7/24/2014: Rick,
Do you have to upload tables singly or can you do them all at once?

Reply from Richard Rost:

You have to export them one at a time to ODBC sources.

Larisa Kiseleva on 7/24/2014: Thanks for another good lesson- I just can't wait when you release seminar about sql server and SharePoint. Do you have any idea when you can start work on this seminar? Thanks

Reply from Richard Rost:

Sorry, I really can't put a date on it right now. I've got so much other stuff I have to finish first.

Kenneth Breig on 7/24/2014: I would love to see a whole seminar on event timers

Reply from Richard Rost:

I'll be covering them in more detail soon.

Wayne Ayotte on 7/26/2014: Thanks for this lesson Rick, I would like to see more about this lesson but mostly I would to see a lesson about SharePoint
James G on 7/28/2014: Maybe I could just create a database with only one form in it that would update the ManningT without distributing the whole master front end. I could then just access the ManningT from the master copy and charge forward. Would that work?

Reply from Richard Rost:

That should work just fine.

Richard Lanoue on 8/2/2014: I got a Godaddy account but it looks like I got a wrong package... I got something called Cpanel and there is different icons when I click Launch and No SQL SERVER Icon
Rick

Reply from Richard Rost:

Yep - we talked about this in email. You ordered a LINUX server. You need a WINDOWS server. Linux has MySQL which SHOULD work, but it's not the same as the SQL Server which comes with Windows hosting.

Richard Lanoue on 8/3/2014: Is there a RSS Feed site you recommend with things like.. The current interest rate by the Fed Reserve, stock quotes, ballgame scores????
Rick

Reply from Richard Rost:

I have not spent the time to look for ANY of those things. Try Googling them.

MUBEEZI MICAH on 8/8/2014: thank you Rick. I need to see more of SQL server and or sharepoint as well.

Reply from Richard Rost:

More SQL Server will be coming up this fall, for sure.

Richard Lanoue on 8/9/2014: In a previous lesson I learned about creating a log:

Private Sub LogIt(Description As String, Notes As String)

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO LogT (Description, Notes) " & _
"Select '" & Description & "', '" & Notes & "'"
DoCmd.SetWarnings True

End Sub

and it worked like a charm... until I uploaded it to a server where the default values of the date/time didn't transfer with the tables... therefore, I can't seem to time stamp the event in the log.

is there a code like an update query in sql to place just before the sequence of the log? or is there a better way?

Rick

Reply from Richard Rost:

You should be able to set up a default value on the server field, but you'll have to do that in your server's table config. It's different for each server, unfortunately.

The alternative is to just send the date/time value with the other two fields and make your function handle it.

Samuel M on 8/14/2014: Owesome ! Very good video. But Tell me what time you expect SharePoint to be available

Reply from Richard Rost:

I really can't say, Samuel. I'm behind schedule on several other lessons that need to get finished first. Hopefully this fall.

Warren G on 8/16/2014: Hi Richard- The connection to the SQL database online course was excellent. However would it be possible to do a course on connecting to SharePoint online- IE Sql Azure. It would be great to share that database to external users using sharepoint online. Thanks

Reply from Richard Rost:

Yep. That's not too much more difficult. I have an Office 365 Business account with Sharepoint and I'll be talking about how to use that soon.

Warren G on 8/17/2014: Out of interest- If you have a access web app hosted on SharePoint online- can you still connect to 3rd party web services such as the weather as you showed in the coarse?

Reply from Richard Rost:

I have not tried that from an Access Web App; only a desktop Access application. Not sure if it's possible or not.

Samuel M on 8/17/2014: Great. but what about SQL Sever in a branch office or in a single local network (LAN). can anyone use the same technique. I have tryed in vain can you help ?

Reply from Richard Rost:

Yes, but the SQL Server has to be set up with Web access. This may involve changing firewall settings and port forwarding and it a lot more complex than I can go into here. I'll be putting together a full seminar on SQL Server that will discuss setting it up in YOUR office and not using a hosted solution.

Samuel M on 8/18/2014: Great I am staying tuned waiting for this solution
Michael Adams on 8/21/2014: Hi Richard,

Echoing another comment (the squeaky wheel gets the grease as you say), I would love to see more on SQL server, securing it and optimising it etc (i am guessing that by optimising it you mean choosing which queries/views on the server side and which to have on the application side. Is that right? Just learning all this lingo).

Thanks, Mike

Reply from Richard Rost:

You're not alone. Lots of people have been asking for more SQL Server classes, and your request SHALL be honored... as soon as I finish the Windows 8 and Office 2013 classes that I'm WAY behind on.

Robert R on 9/29/2014: Hi Richard,
I need to share my database with multiple users but they all need a different username and password to access records that I hae assigned to them....will you be having a class on this or does one exist?
Thanks

Reply from Rick Rost:

Hi Robert. This is covered in the SECURITY SEMINAR.

Brian Farley on 11/13/2014: For those stuck with a MySQL database figured I'd post what I needed to do...

You will need to download the driver here: http://dev.mysql.com/downloads/connector/odbc/5.1.11html
Then pick that out of the driver pick list, it should be near the bottom labeled "MySQL ODBC 5.1 Driver" you should be able to follow the rest.

The Microsoft ODBC for Oracle (maker of MySQL) doesn't work.

Keep up the great work Rick!!

Reply from Alex Hedley:

Thanks Brian

Selby Halfpenny on 1/27/2015: Dear Richard, thanks for this excellent lesson, your the coolest
Selby

CHARLES FULGHAM on 2/7/2015: Excellent course Mr. Rost! It would be beneficial for me to know how to utilize online database hosting strictly to BACKUP table records on a nightly basis. I m sure you cover this somewhere already, but just thought I d throw it out there. Thanks again!
Brauna Rosen on 6/11/2015: Hi Richard,
your tutorials are so clear but when I went to configure on Go Daddy all their screens have changed and I cannot seem to be able to get to the part where I can find the host name....

Reply from Alex Hedley:

My Account
Web Hosting - Manage (button)
OPTIONS & SETTINGS
Databases
SQL Server

Actions
Details

Brauna Rosen on 6/16/2015: Thanks for the response, however, I am still having difficulties. I called GoDaddy support and they say that they do not support Access on their server any longer. Am I just having communication issues with them? I am about to give up unless you can help me further.

Reply from Alex Hedley:

Are you not uploading your Access Tables to your MS SQL Server Instance?

Brauna Rosen on 6/17/2015: Please disregard my last message. It worked!!!Thanks

Reply from Alex Hedley:

Glad you got it to work.

Brauna Rosen on 7/30/2015: Hi,
I am getting a "Write Conflict" message when trying to edit a record that has been previously saved. It is " This record has been changed by another user since you started editing it." It will not allow me to save the record at all.
Any idea what I am doing wrong?
Thanks

Reply from Alex Hedley:

Are you the only user using this db?
Is it stored locally on your machine or on a network drive?

Brauna R on 8/3/2015: Hi Alex,
Yes, I am the only user at this time. The front end is stored on the desktop of my laptop..
Thanks,
Brauna

Reply from Alex Hedley:

Do you have multiple forms using the same record source?

MS Article
Set the RecordLocks property of the form to Edited Record.

Brauna Rosen on 8/5/2015: I am the only user at this time and I do have a few forms depending upon the table.
Thanks

Reply from Alex Hedley:

Did you try the RecordLocks or get a chance to read the previous linked article?

Brauna Rosen on 8/26/2015: Hi Alex,
I have not yet resolved this. I tried the Record Locks on Client Settings. The database is stored locally, on my machine. Godaddy could not help me with this issue.
You mentioned a "previously Linked article" but I did not see it. Could you send me the link for that.
Thanks
Brauna

Reply from Alex Hedley:

The blue underlined words in a post are links. There is one that says "MS Article", just click on that.
Is the backend on a GoDaddy MS SQL Server? Is that why you asked them for help?

Brauna R on 8/31/2015: Thank you for your response. Yes, the backend is on GoDaddy SQL server. I was able to export the tables but not able to edit records (Write conflict). I tried the resolution suggested on one of the articles you sent but it still does not work.Perhaps it is because SQL stopped supporting Access with the 2003 version (I have 2010).
I am almost ready to give up..

Reply from Alex Hedley:

SQL Server still supports later versions of Access. Where did you read otherwise?
I have many dbs connected via Access to MSSQL without a problem,
Are you connecting with a DSN?

Brauna R on 8/31/2015: That is encouraging. I did read that on the article you linked "Support for Office 2003 has ended
Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected."
I am using a DSN source created as per the tutorial. I did notice however, on the same article, that the Access Database is referred to as an .mdb file whereas all my databases are .accdb. Could that be a problem?

Reply from Alex Hedley:

Yeah it's an old article, so 2003 isn't supported but XP has just gone EOL so that's likely.
It mentions mdb as it's 2003 which is the file extension used there. 2007 onwards uses .accdb.

Can you edit the db in the online SQL Server admin GoDaddy provides?

Brauna R on 9/1/2015: I have not been able to edit in "MyLittleAdmin" I can see the tables but have not found a way to edit.

Reply from Alex Hedley:

GoDaddy provide their own.
Login to your account
WebHosting - Manage
Under Databases - SQL Server
Click on "Admin Tool"
Login
Click on Tables
Choose your Table
Under the last column - Actions you can add/edit your records.

Brauna Rosen on 9/29/2015: Hi Alex,
Finally, it seems that I found the solution: set 0 as the default in table properties.
Thank you
Brauna

Reply from Alex Hedley:

Which value did you set to 0?

Brauna R on 10/2/2015: All fields in the table (except for the Primary key, autonumber of course). Also, all forms have record locking set to Edited Records. I will continue to test, next with a live database.
The other thing I found out is that I had to download Microsoft SQL Server Management Studio to be able to make any changes to the tables already on the server.

Anthony Bardaro on 11/22/2015: I have been chasing down how to access web based data with a desktop front end for awhile. The Online SQL Server database lesson is great!!! I already had a GoDaddy windows hosting account and got it running very quickly. I did add a primary key index that was not automated. Other than that, it works flawlessly. Thanks!!!

Reply from Alex Hedley:

Glad you found it useful Anthony

Dennis Owens on 4/24/2016: After setting up the SQL Server database does the other person using this needs Access loaded on their computer? I am using Access 2016, using an earlier addition should also work, right.

I too would like to see more on this subject of SQL Server database.

Reply from Alex Hedley:

If you want them to connect to your SQL Instance they will need a program to do that,
If you have built the db in Access you will have to share that and the connection details.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP