CONS Track Day2 - Using Access and SharePoint for Travel Expenses and Pulling up Maps

Sign in to queue

Description

Learn how to build a tool with Access to manage information on the road. Track mileage and meals. Use Bing to map your route and show maps for areas you visit. Calculate distance and get expense reports. Access is for anyone who has data to organize. Office 365 lets you get data when you are at home, in the office, and on the road. No matter where you are, your web database can be there too.

Day:

2

Code:

CONS11

Embed

Download

Download this episode

The Discussion

  • User profile image
    GeoffreyG

    Great Presentation Crystal, Julian, and Brent - You've made some Wonderful Access Applications!  I look forward to seeing more applications like these.  And I think this presentation is a great resource for current Access developers, who have traditionally built desktop applications, to start breaking into creating Access web apps, Access-SharePoint apps, and Access-SQL Server apps!  

    My only concern about Access web apps is overall scalability and the lack of a coding language for Web Forms and Web Reports.  But the great thing about working with SharePoint, Azure, and SQL Server is that a developer can always just create llinked tables to the SharePoint/Azure/SQL Server tables, and still use the traditional Access Froms and Reports in a desktop application.  This allows the developer to choose from multiple avenues of Access application development - the traditional standalone database, a pure Access web application, or a hybrid database application that is a mix of traditional Access Forms and Reports, tied to data in online data sources (i.e. "The Cloud").  After being around for 20+ years, Access STILL has it going on!

  • User profile image
    strive4peace

    Thank you, Geoff. I agree there is a lot of potential with Access Apps for multiple avenues of Access application development. Oftentimes, Access Apps will not be a total solution -- they will be used to input information in the field. Access Apps make good data collectors for inventory counts, travel expenses and mileage, notes, and other data that one wants to record out of the office.  Not everyone will build fancy polished apps like Julian demonstrates.

    Although Access Apps do not have a built-in reporting tool other than Views, Brent shows us how to link to the tables from the desktop to create traditional reports in Access, along with VBA and everything else except modify the table design.  In Excel, the data can be connected to make tables, pivot tables, and pivot charts.  On the desktop, data can be graphed, calculated, manipulated, projected, and related.

    As for scalability ... the logic is exposed in SQL Server Management Studio, which makes it possible to transfer to another application, as Pat mentioned also.  The data is, by nature, transferable. I see Access Apps as a great extension of the desktop.  

    Geoff writes books on Access, and consults -- here is a link to contact Geoff at ImagineThought:

    http://www.imaginethought.org/

    ~~~
    I hope that traditional Access developers will give Access Apps a try -- and integrate them into their desktop applications.  Here is a resource page for building Access Apps: http://gainingaccess.net/AccessWebRef.aspx
     
    warm regards,
    crystal

    ~ be awesome today ~

  • User profile image
    Jenny Duskey

    Thank you all for this clear and interesting presentation of Access Web Apps and how they can be used in conjunction with our old, familiar desktop Access, and with Excel! I've been leading a seminar in our Philadelphia Area Computer Society since 2011. This past academic year we decided to focus on Access 2013 Web Apps, and it was really hard at first to get online help with problems that arose, although Jeff Conrad was often a life-saver in that regard. Now Crystal, Julian and Brent have demonstrated graphically how Access Web Apps can be used as a way to get information from the Web into Access and how Excel spreadsheets and Desktop Access provide a variety of ways to report that information. What a gift to developers and users alike!

  • User profile image
    JennyD

    I just commented above, as Jenny Duskey, but had not signed in.  I want to rate the session, giving it five stars, but haven't seen how to do that.

  • User profile image
    strive4peace

    Thanks, Jenny, and you're welcome. Jenny runs an Access SIG (Special Interest Group) for the Philadelphia Area Computer Society.  Here is a Help page for the group:

    http://pacsnet.org/groupsatpacs/access/

    Happy you want to give us 5 stars! Thank you -- much appreciated. To rate the presentation, click on the right-most star at the top of the page for 5 stars (where they are displayed, when you are signed in) .  There doesn't seem to be any feedback except the number in parentheses changes (eventually).

    I agree that it has been hard to find information on Access Apps -- not having maps seems to go along with blazing trails though ;) Several people have shared information and resources to make it easier for others -- coming to this page will be a link to download the referenced PowerPoint presentation.  Currently, it is available on the reference page linked in my first post.

    I'd love to hear about the apps that your group may be building, do a remote presentation, and help brainstorm ideas with those who have time to develop them.

    This is a very exciting time for Access developers.  I hope more people will reach out, learn, build, and integrate data from cloud apps into desktop client applications they build with Access, Excel, and see where that goes ...

    warm regards,
    crystal

    ~ be awesome today ~

  • User profile image
    Glenn Lloyd

    This video includes excellent introduction to Access for newcomers to Access, a well presented example of Access Web Apps, and a practical guide to taking Access data to the Cloud when a desktop application is part of the equation. All in all the presenters have done an excellent job. This is a must watch video for anyone wanting to expand their Access knowledge and skills.

  • User profile image
    Julian​Kirkness

    Some great comments here about the session - and all credit to Crystal for a great production!

    My experience building Access Web Apps for sale through the Sharepoint App Store has been pretty positive in almost all respects. Microsoft have produced an incredible tool here and let's hope that its capabilities improve even further in the future. I have been amazed at the things I have been able to achieve given that AWAs have 'only' a Macro Language. 

    Microsft themselves have effectively recognised the effectiveness of the technology by recognising our HR app (as shown in the video) in the 2015 Office App Awards. It was the winner in the Best Business Value category.

    i hope that this is just the beginning of a journey for Access Web Apps!

  • User profile image
    strive4peace

    Thank you, Julian, and congratulations on winning an award!  For others reading, here is the link to the award page: https://dev.office.com/app-awards-winners#MostBusinessValueOffice.

    It was a pleasure presenting with you.  Thank you for sharing what you are doing with apps -- here and in forums and blogs too.  Perhaps we can do this again?  Different topics of course.

    Thank you, Glenn, for taking time to watch the presentation and writing an excellent review.  Glenn has an Access blog here: http://officetipsandmethods.com/?p=909

    I have received some very nice emails about this session as well and also look forward to seeing more Access apps in the store.

    Something to keep in mind as apps often compliment desktop applications is building both pieces and making the desktop portion of the application available via download link from the web app, if there is not another procedure in place.  As Brent says, innovate! There are all kinds of possibilities!

    I'd like to hear some of your ideas ...

  • User profile image
    hunterpaw

    I have never before seen a session so packed with valuable information and great ideas that showed such creativity! It stirred me up into thinking of ideas that I could do with Access Web Apps. Crystal showed us some very useful ways of bringing outside data and services into our Access Apps. Crystal also has some very helpful Macros Reference Resources, and who among us does not need help Macros from time to time!

    What Julian has been able to do with Access Web Apps is simply amazing! His presentation showed us so many new possibilities! Brent's presentation was so very clear and helpful. I learned that you could use Excel to get data from the Web App's Azure SQL Database. What a great way to create outstanding Reports for an Access Web App!

    Related to Brent's excellent presentation, I have written an article that shows step-by-step How to Create a Read-Write Desktop Database from Your Access Web App I was privileged to be able to make a small contribution to the session about a Grow-up path for Access Web Apps. You can read more about that at the link above about Crystal's Macros Resources. I have never seen a more positive and encouraging presentation that blows the lid off of the supposed limitations of Access Web Apps!

  • User profile image
    dat​Adrenaline

    This session is awesome!  It touches on  so much information and capability regarding Access Web Applications: Basic database concepts and application; An extensive application that is for sale in the app store; external connectivity to use the data any way you like; and tips for deep investigation via SQL Server Management Studio of how Access Web Applications store and manage your data.  This presentation should excite developers from many specialties of expertise as they merge their innovative ideas with the capabilities of Access Web Applications.  Access Web Applications can truly be the glue that holds many facets of a business or personal process together!  I encourage you to take the time to review this presentation to start the wheels of innovation moving!

  • User profile image
    AdrianBell​MVP

    The production values, as is usual with Crystal's work, is way up there.  Nice work.

    Good contributions from all.

  • User profile image
    Karl Donaubauer

    Well done, folks!

    Just a comment to Brend's "warning" concerning the ODBC dialog: I've successfully used the dialog for this. You have to choose the right settings (e.g. enable "Use strong encription for data") and there are other possible pitfalls. I don't know what went wrong in Brend's trials but you definitely can use the ODBC dialog to create the data source and connect to the Azure db of an Access App.

    cu
    Karl

  • User profile image
    Karl Donaubauer

    Replace(PreviousComment, "Brend", "Brent")

    ;-)

  • User profile image
    strive4peace

    @Karl Donaubauer: thank you, Karl! ... perhaps you would like to do a demo of this? I am lining up presenters for a connectivity presentation.  If you are interested, email me and I will send you a PowerPoint template ;)  btw, you can edit your comment and personalize your profile

    Karl has a site in German for Access here: http://donkarl.com/

  • User profile image
    strive4peace

    @AdrianBellMVP: thank you, Adrian -- even though you weren't involved directly, some of your ideas were (smile)

    Adrian is a big contributor on http://bytes.com/

    and has a website for Access Consulting in the UK here: www.AccessConsultantUK.co.uk

  • User profile image
    strive4peace

    @hunterpaw: Thank you Pat -- what you do for others is impressive.  Thanks for setting up the reference page and Azure space.  And thanks for your wealth of information on Azure, friendship, helping other developers have a smoother path, and giving them comfort that their applications can grow up too ~

    Pat has a web site with lots of developer information here:

    www.GainingAccess.net

     

  • User profile image
    strive4peace

    @datAdrenaline: Thank you Brent, for showing how to bring the flexibility of the cloud down to the power of the desktop.  Your instructions are so clear and easy to understand ... must be that engineering mind (smile).  Even with all that is going on in your life, you found time to create an outstanding presentation. You are a great teacher and friend.  Because of what you demonstrated, I believe many more will open the door.

  • User profile image
    cawright78

    A really good presentation, learned a lot about capabilities, especially how to use mapping.  Seems like a great way for small businesses using Office 365 to get web access to their data.  I personally would like to see a presentation on how to link documents that are stored in an Office 365 document library to records in an Access Web App table. 

  • User profile image
    strive4peace

    @cawright78: Thanks, Curt ~ great idea! We'll see what we can do.  SharePoint does have a document library and Access apps can link to documents stored there -- parsing them to glean information is a more complicated matter.  I would imagine, given your business (O&G distribution? apologize if I am using the wrong term) that you have information that comes from data collectors ... Volume, Time, Pressure, Temperature ... for instance.  In those cases, you will probably have an easier time to link to data by the collectors from the desktop where large amounts of information can be stored efficiently.  However, as you are in the distribution business, I imagine you go on the road a lot.  Perhaps in the past you have stored documents with information you need to really be structured in another way ... all I can say about those is once you have a remote tool set up to collect whatever it is, you can then convert your historical information to the proper format. 

    As for POS (Point-Of-Sale), that is probably best left on the desktop too ... but there could be some sort of timed program that runs periodically to update numbers in/from the cloud so you can consolidate them all in your main office on the desktop where you have more power.

    On mapping. it was actually hard to get information about how the parameters should be constructed (for the route anyway), which is why they are displayed (I'll bet you paused ... or will).

    Thanks for commenting, Curt

  • User profile image
    cawright78

    @strive4peace: Where I was really headed with this, is we usually have 2 to 4 different documents associated with each delivery that I would like to scan, upload to Office 365 and then link to a web app to have access to.  The reason I want it for web app is that I would eventually like to allow my customers as external users to have access to their information and download their own documents.  Controlling access for external users is a bit complicated but not terrible with Access Web Apps, although I am still trying to figure this out on the SharePoint side, since external users would have access to the sub-site with the document library.  We are interrupted 5 or 6 times a day by customer asking us to re-send an email or resend a fax with the delivery documentation.  Would really like for them to be able to download it themselves.

  • User profile image
    strive4peace

    thank you, Curt.  Document Storage with security is an excellent topic and will be addressed in the next Access presentation we put together, which will be on connectivity -- something Access is known for.  Thanks for your feedback -- great idea!

  • User profile image
    strive4peace

    Several people have asked me where Bill "MrExcel" Jelen's presentation is ... unfortunately the recording could not be posted.  However, Bill has an amazing channel on YouTube with lots of videos on Excel here:

    https://www.youtube.com/user/bjele123

    happy travels to Florida, Bill!

    Another channel on YouTube with great Excel videos is Mike "ExcelisFun" Girvin:

    https://www.youtube.com/user/excelisfun

  • User profile image
    strive4peace

    Thank you all for commenting here and other places.  There have been some questions on how to set up an Access web database in SharePoint.  I also have a channel on YouTube with free video tutorials.  This tutorial shows how to move data from the desktop to the web and how to create an Access App (web database) in SharePoint using Office 365.

    How To Migrate Data from Desktop to Access App (cc) (32:35)

    https://www.youtube.com/watch?v=5joYQaj--08

    My YouTube channel (LearnAccessByCrystal) has a Featured Channels list on the right ... I created this, it is not an ad.  Links to Bill Jelen's channel, Mike Girvin's channel and other good video tutorial producers such as Glenn Lloyd, Chris Downs, TheSmileyCoder (Anders Ebro Christensen), Tom Urtis, Heather Ackmann, Adrian Bell, and more are in my Featured Channels list.

    In the Videos list on my Access channel are a few guest videos related to Access such as Albert Kallal (Access 2010: Outlook-Style Calendar in Browser) and Tom van Stiphout.(Access Utilities: Simil Fuzzy String Matching).  Both videos have captions (cc).  Tom's captions have also been translated into Dutch and French.  There is a link in the video description to go to Tom's site on AccessMVP.com where you can download the source code for Access and the DLL.

     

     

  • User profile image
    strive4peace

    There is a little confusion because, with the other Office products (ie, Excel, Word, PowerPoint, Outlook), "apps" are different than "apps" for Access.

    For information on building an Office App using JavaScript, look for the "API Tutorial for Office" in the app store.  Here is a video tutorial explaining it:

    Office App Store: JavaScript API Tutorial for Office using Excel 2013 (cc)
    music by Jubal Lee Young
    https://www.youtube.com/watch?v=cjpuEsm7Ad4

    and here is where you can download the JavaScript API for Office from the Office Dev Center:
    https://msdn.microsoft.com/en-us/library/office/fp142185.aspx

    With Access Apps, you don't need to write JavaScript, you create macros.  User Interface macros are actually stored as JavaScript, and Data Macros are stored as T-SQL, which Brent shows us how to get to using SQL Server Management Studio.  Access Apps are the whole thing, not in a window that interacts with your document.

    At some point in the future, Access may get the same capability with Office Apps that other Office products have ... then it will really get confusing!

     

     

  • User profile image
    strive4peace

    It is free to Join Channel 9 ... and I encourage you to do so since it is Microsoft's site for developer video tutorials and presentations. When you are signed in, you can ask questions! 

    As a member to Channel 9, you can also be notified when new videos are posted -- Click on the "Subscribe to Channel 9" link in the upper right. 

    If you are not signed in, you can create a free account. Click on the SignIn link and then choose Sign Up Now

    If you already have a Microsoft account, I believe you can link it, as I did ...

    thanks again for the comments here and in other places ~

    warm regards,

    crystal

    ~ have an awesome day ~

  • User profile image
    Angel Hernandez

    Big kudos to Crystal and the guys for putting together and presenting such an insightful and interesting topic on Access development.

    Cheers,

    Angel

  • User profile image
    Kent Gorrell

    When deploying your app you will need to chose whether to publish a locked or unlocked app.

    Unfortunately you cannot get the Connection Information for a locked app so you can't link to it from your desktop. Your only way to get your data to your desktop, for reporting or even creating a verifiable backup, is to use the Download to Excel feature.

    When Developing your unlocked app you should bear in mind that you are connected to live data, yes the same data your users may currently be editing! There is currently no way to setup a separate development or test environment. So you need to be very careful. You should be able to develop on one database then redirect the connection to a different backend when you publsh but currently you can't.

    Neither option is ideal. Hopefully MS will soon address these issues to make Access Web Apps a truely viable solution.

  • User profile image
    strive4peace

    Thank you, Angel.  Angel is a Microsoft MVP for C# and has done some presentations for the MVP Award Program.  I found his presentation on developing Windows 8 apps especially informative.  Angel is a great teacher.  What he says about Windows 8 apps generally applies to Windows 10 apps too.

    MVP Mentor Angel Hernandez Matos delivers his session "Developing Windows 8 Apps"

    https://www.youtube.com/watch?v=Zt4OZFYjzYM

    Kent, thanks for passing on valuable information about Locked and Unlocked apps.

    Kent is an Access consultant in Australia and has a help page for Access here:

    http://osel.com.au/

     

     

  • User profile image
    strive4peace

    A great place to go for help while you are building Access Apps is Julian's LinkedIn Forum

    Access Web Apps

    https://www.linkedin.com/grp/home?gid=7454675

    Help is free, friendly, and first-class!

     

     

     

  • User profile image
    davidjpp

    It is a really good session, but I do wish it mentioned Visio for data visualisation too. I have written a blog at http://blog.bvisual.net/2015/09/16/linking-an-access-web-database-to-visio/ to show how an Access Web Database can be linked to Visio Pro.

  • User profile image
    strive4peace

    awesome, David, thank you!  We look forward to having you join us for our next presentation, which is on connectivity and will be posted on Channel 9. 

    As many of you know, getting information about web database structure can be tricky. However, you can create a report database from your Access App (explained in the presentation) and run the Analyzer for Microsoft Access on it.  This is a free tool, written in Access, that runs on desktop Access databases, including report databases made by Access Apps.  It documents data structure, relationships, and more.

    free Analyzer for Microsoft Access (on CodePlex, Microsoft's sharing site for developers)
    http://Analyzer.CodePlex.com

    to download the latest version, click the big "download" button in the upper right of the Home page.

     

Add Your 2 Cents