Join us in Las Vegas March 3-6 for SharePoint Conference 2014 (SPC14), including sessions on Microsoft Access. Product team members will show you Access web app features available in Access 2013, Office 365, and SharePoint 2013.
Sign up for the Access Services sessions at SPC14!
If you’re attending SPC14, be sure to sign up for the following Access Services sessions:
SPC204 – Anyone can build a SharePoint App with Microsoft Access
Speakers: Jeff Conrad and Chris Usher
Come to this session to learn how power users can easily and quickly build SharePoint apps with a full SQL back end without writing any code. You’ll learn how to create rich UI SharePoint apps using Access with low to no involvement or impact from IT.
SPC338 – The ‘how to’ guide for selling and managing SharePoint Apps built using Access
Speakers: Bob Piper and Kevin Bell
SharePoint applications created by Access are real SharePoint apps that you now can put in the Office Store. These no-code data centric web apps are part of Office 365. To make the publishing of these apps complete, Access has added features to help you deploy upgrades to existing apps. So you can deploy new versions of an app in production without losing their data. Come see how it all works.
SPC335 – Rich extensions to SharePoint Apps using Microsoft Access
Speakers: Gary Devendorf and Arjun Raja
“Apps for Office” is a new technology for extending Office applications. You may have seen exciting Apps for Office in Excel, Word and Outlook. Well, the newest version of this technology is coming to SharePoint apps created with Access. Come see some cool Apps for Office (like Maps and Charts) that extend the value of Access web apps. We will show how to build, test, and deploy your own Apps for Office as well as how to use those in the Office Store.
In addition to the above three Access-specific sessions, you might also consider attending this session:
SPC348 – Update on InfoPath and SharePoint Forms
Speakers: Greg Lindhorst and Sonya Koptyev
In addition to learning more about Microsoft Access 2013 and Access Services 2013 at the SharePoint Conference events, you can find out about the future of Microsoft products, solutions and services directly from the leaders, complete with news, announcements and demos. Unlike traditional classroom training with a fixed set of topics, the SharePoint Conference allows you to personalize your learning experience. Mix and match the sessions and session types you want to attend, including breakout sessions, hands-on labs, keynotes, and more. Each of the four days provides you the opportunity to completely immerse yourself in sessions that offer insight into the products you are working with.Stay ahead
The food, the pools, the nightlife, and the mini-monuments are all tempting reasons to sign up. The real reason to attend is to stay ahead of the curve. SPC14 gives you the latest and greatest in SharePoint innovation, industry technologies, best practices, and unparalleled access to Microsoft and industry experts. The knowledge, connections, and opportunities that happen at SPC14 won’t stay in Vegas. Discover new ways to connect, to reimagine, and transform. Discover the new way to work together.Personalized for you
SPC14 has dedicated tracks for Developers, IT Professionals, and Executives. The speakers, sessions and labs you know and love are back and better than ever. Broader themes focus on migration, infrastructure solutions, business insights, social collaboration, and much more. Learn how you can get more out of SharePoint and Yammer.More than content
SPC14 is more than just presentations. Join the community and step into the fast-track with those who are changing the paradigms of how people and information interact within the changing world. Meet tomorrow’s movers and shakers. Create lasting friendships and partnerships as powerful as the experiences you’ll have.
SPC14 will be the largest and most comprehensive SharePoint and Yammer event on the planet. Are you going?Join us in person
In today’s online and always-connected world, there is tremendous benefit in having dedicated time away from your office to focus on advancing your expertise, minus the distractions. By attending the SharePoint Conference in-person, you are giving yourself the best chance to learn, connect and explore and take advantage of everything the event has to offer.
We hope to see you there! Register now at http://www.sharepointconference.com/.
After a successful sold-out event in the fall, J Street Technology is organizing another one-day conference on Microsoft Access. Join us Wednesday, April 16 in Irvine, CA for the next Access Day featuring internationally-respected presenters, including Access MVPs, book authors, and even a member of the Access development team at Microsoft.
Access Day will include sessions by Kevin Bell, Tom van Stiphout, Alison Balter, George Young, and Armen Stein and cover a mix of Access client techniques and new Access Web app development. The intended audience is Access developers and advanced users who want to learn ways to make their applications more powerful and develop Access solutions for the web.
Here’s what people said about the last Access Day event:
Great information, great networking and well worth your time! –Andy T.
Access Day has provided a welcoming environment for professionals to meet and get acquainted with peers… –Craig R.
The presentations focused on real-world solutions and scenarios and reminded me how much I like Access. –Luis C.
I really enjoyed the great mix of topics and the enthusiasm of each presenter was excellent. –Craig A.
Register by Feb 21 for a $30 early bird discount at www.AccessDay.org.
This post is brought to you by Steve Kraynak, Program Manager for Excel spreadsheet management features. Special thanks to SQL MVP Scott Stauffer for contributing to this article.Who’s using my data?
Do you need to find out where your data is being used? More specifically, how can you find Excel workbooks that have data connections to your databases?
We went to SQL MVP Scott Stauffer to help answer that question. Scott is helping his client launch a data warehouse that will be the preferred source of data for many areas of the business. The client is also planning to switch to a new Enterprise Resource Planning (ERP) system, which means that legacy systems will eventually be turned off. A new data warehouse is great, because it can provide a smooth transition from a legacy to a new ERP system, but people have lots of Excel files with connections to the legacy systems. Scott and his team need a way to find these spreadsheets and their owners to communicate information about the new data warehouse.The Discovery and Risk Assessment tool
In Office 2013, Microsoft released a tool called Discovery and Risk Assessment (DRA) which can answer these questions. DRA gives organizations the ability to create an inventory of their Excel and Access files and then analyze them so they can determine which ones are the most complex, impactful, and risky. DRA does this by crawling specified network paths and SharePoint sites to find the Excel and Access files, and then it analyzes and gives each file a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.
What’s interesting to the owner of a database or data warehouse is that Discovery and Risk Assessment can tell you about all the data connections found in the Excel files. We’ll show how you how to use DRA to get the list of all the files with data connections and see the Last Saved By property of each file. With this list, you’ll be able to search the data connections to find which workbooks are connecting to important data sources.How Discovery and Risk Assessment helps your business
By using Discovery and Risk Assessment to find out who owns the spreadsheets with connections to the old data sources, Scott helps his client to realize the following direct benefits:
In addition to these benefits, Scott’s team will be able to improve the quality of spreadsheet usage throughout the company, which is the prime reason that Office released DRA and several other tools in Office. They will also:
For more information about Discovery and Risk Assessment (DRA), as well as the other spreadsheet management features available in Office and SharePoint, please see our announcement, the overview of the tools, and a spreadsheet management white paper on TechNet.How to use DRA in your own business
To use Discovery and Risk Assessment in your own business, first set up DRA through the Volume Licensing Service Center for volume customers, then follow the TechNet instructions for installing and configuring it. When you configure DRA, you’ll run a discovery, which means you’ll have a database containing a list of all the Excel files DRA discovered, as well as a wealth of information about each spreadsheet, plus the list of data connections for each file.
To save you the effort of trying to figure out the database schema, follow these steps to bring the list of workbooks and their data connections into Excel. This example uses Microsoft Power Query for Excel, but you can still use the traditional ways of getting external data into Excel. (Note: It is recommended that you do not perform the following steps on a live production database, since it could impact performance. It is recommended that you use an offline copy of the database.)
1. Go to the Power Query tab, then follow these steps:
a. Choose From Database -> From SQL Server Database.
b. Enter the server name of your SQL Server, including the instance name, if applicable.
c. Enter the database name of the database that DRA is connected to. You can find this in the Configuration settings in DRA.
d. Expand the SQL Statement box and copy and paste the query below into it.
Here’s a detail view of the query in the screenshot.
FP.Value AS FilePath,
FN.Value AS FileName,
MD.Value AS Modified,
S4.Value AS LastModifiedBy,
N1.Value AS ConnectionCount,
T1.Value AS DataConnections
DiscoveryAccounts AS DA
INNER JOIN UDKDefinition AS UDK1 ON DA.AccountID = UDK1.AccountID
INNER JOIN UDKDefinition AS UDK2 ON DA.AccountID = UDK2.AccountID
INNER JOIN UDKDefinition AS UDK3 ON DA.AccountID = UDK3.AccountID
INNER JOIN UDKDefinition AS UDK4 ON DA.AccountID = UDK4.AccountID
INNER JOIN UDKDefinition AS UDK5 ON DA.AccountID = UDK5.AccountID
INNER JOIN UDKDefinition AS UDK6 ON DA.AccountID = UDK6.AccountID
INNER JOIN UDKDefinition AS UDK7 ON DA.AccountID = UDK7.AccountID
INNER JOIN Strings AS FP ON UDK1.UDKID = FP.UDKID
INNER JOIN Strings AS FN ON UDK2.UDKID = FN.UDKID AND FN.InventoryVersionID = FP.InventoryVersionID
INNER JOIN Dates AS MD ON UDK3.UDKID = MD.UDKID AND FN.InventoryVersionID = MD.InventoryVersionID
INNER JOIN Strings AS S4 ON UDK4.UDKID = S4.UDKID AND FN.InventoryVersionID = S4.InventoryVersionID
INNER JOIN Numbers AS N1 ON UDK6.UDKID = N1.UDKID AND FN.InventoryVersionID = N1.InventoryVersionID
INNER JOIN Text AS T1 ON UDK7.UDKID = T1.UDKID AND FN.InventoryVersionID = T1.InventoryVersionID
UDK1.NAME LIKE ‘FilePath’
AND UDK2.NAME LIKE ‘FileName’
AND UDK3.NAME LIKE ‘Modified’
AND UDK4.NAME LIKE ‘LastModifiedBy’
AND UDK6.NAME LIKE ‘DataConnectionsCount’
AND UDK7.NAME LIKE ‘DataConnections’
AND N1.VALUE > 0
2. Depending on the size of your database, in a few minutes the Query Editor will appear and you can simply click Apply and Close to send the data to a worksheet.
3. Now you have the data you need in Excel. You have the file path, file name, modified date, Last Modified By property from the file, and for each file you have a text string that contains all the data connections for the file.
4. You can filter to suit your needs, but for example, if you need to find all workbooks that have a connection to your legacy ERP system, filter the Data Connections column by rows containing “MyERPSystem” (substitute the real name of your ERP system).
5. Now you have a list of all the files that have a data connection to your ERP system, and you can see who last modified them, so you know who to contact about switching to the new data warehouse.
Let us know in the comments how you use DRA in your own organization.
–Steve Kraynak, Program Manager, Excel spreadsheet management features
Matt Evans is a writer on the Access team.
If you test out Access apps, but are waiting to use them in a production environment, you can now move full speed ahead.
What does general availability mean? Customers can now create, share, and use Access apps with full confidence and the same level of guaranteed availability as the rest of Office 365. Access apps have been part of Office 365 subscriptions since Office 365 General Availability was announced in February 2013. Until now, Access apps were in preview and weren’t supported under the Office 365 service-level agreement (SLA) and compliance standards.
General availability brings several benefits:
For information about getting started with Access apps, see Manage data in Access apps in the Office 365 Business Learning Center. Also check out Access apps for developers for information about creating Access apps for SharePoint.
Frequently Asked Questions:
Q: Which Office 365 customers benefit from general availability of Access apps?
A: Access apps are now generally available for Office 365 Small Business, Midsize, Enterprise, and Education customers. Office 365 Government customers are still in preview.
Q: What are Access apps compared to SharePoint apps?
A: SharePoint apps, officially known as Apps for SharePoint, are self-contained pieces of functionality that extend the capabilities of a SharePoint website. Users find and download these apps from the SharePoint Store or from their organization’s private App Catalog and install them on their SharePoint sites. Access apps are web database apps designed in Access and hosted in SharePoint.
– Matt Evans
What are the top 3 updates you’d like to see in Access web apps? We have our own list, but we’d love to see yours–we rely on your feedback to continually improve Access. Complete our short survey or tell us your top 3 wish list items for Access web apps in the comments below.
We have grand plans for the next full release of Access, but in the meantime, we are constantly building and shipping improvements to Access 2013 web apps. Have you tried Cascading Controls yet?
You asked, we listened! It’s just the nature of shipping software in a “services world” with Office 365: When we hear your feedback, we can respond with new features that fill your needs. In the case of Access 2013, we heard loud and clear from our first Access 2013 web app users that we needed to implement Cascading Controls as a part of our custom web app feature set.
It used to be a 3-year wait for new features in Office. However, because we are increasing the speed of our ship cycles, we are proud to announce that as of now, Cascading Controls are available to all of our Access 2013 Office 365 customers! The changes are on the server side which means you do not need to update your Access client program at all. This update is available immediately for customers using Office 365 with Access Services so look for this feature within your web apps and try it out.How do Cascading Controls work?
It’s a common scenario. The app developer wants to make the options in a control, such as a dropdown, relevant to the option chosen from a different control. For example, you might want to associate company names with people names in a business contact database, or maybe you’d like to connect the make, model, and trim level for cars in a product database. These scenarios are now possible in Access 2013 custom web apps for SharePoint for Office 365 users.
Consider the following scenario of an app tracking a consultant’s projects. When entering a new project, you want to choose a company for whom the project will be completed and the contact for that project at that company. If you have lots of contacts at different companies in the app, you might want to see only a list of the contacts at that specific company. To enable this scenario, use a List Details or Blank view that has more than one combo box or autocomplete control. In the following screenshot, you can see a sample List Details view of a Projects table.
In this case, the Company autocomplete control will be the parent control and the child control will be the Contacts combo box. Note that the parent control can be an autocomplete or a combo box control, but the child control must be a combo box. For the child control, click the Data callout to see the list of properties available for the control. When you specify a Row Source (can be either a table or a saved query) for the control, additional properties appear, including a property called Parent Control. In the following screenshot, you can see the new Parent Control property at the bottom of the property list.
The dropdown list for the Parent Control property will display only the names of autocomplete and combo box controls defined on the current view. Select the control you want to be the parent from the dropdown list. In the example screenshot below, we selected CompanyAutoComplete. After you select the appropriate control for the Parent Control property, Access displays an additional property called Related Field. Select the field from the dropdown list on this property that serves as the “linking” element between the parent and child controls and Access will take care of the rest.
When you view your web app within your browser at runtime, Access Services disables the child control until you select a value from the parent control. In the following screenshot, you can see that the Contact combo box initially is not an active control.
After you select a value from the Company autocomplete control, Access Services activates the Contact combo box, filters the list to only display those contacts associated with the Company in the parent control, and then allows you to select an appropriate contact. If you select a different value from the Company autocomplete control, Access filters the Contact combo box again to only display those contacts associated with the new value in the Company autocomplete parent control.
We hope you enjoy this new Cascading Controls feature within Access web apps for SharePoint that opens up more scenarios for your custom web apps built on top of Office 365. Give it a try and let us know what you think!
It’s a common scenario: There is a collection of things that people borrow and return. And any collection has common problems: How do you track the items? Are they available? If not, who has them? Where are they? When will they be returned? What is their condition?
For McKinstry, a builder and manager of energy-efficient commercial buildings, the problem centered on a collection of tools, from meters and sensors to hammers. McKinstry employees and subcontractors use the tools to complete energy audits and small-scale construction projects. Their solution for tracking the tools was simple: a clipboard hung in the tool room. If someone needed a tool, they would go find it in the tool room and, hopefully, mark the tool as checked-out on the clipboard.
The drawbacks are obvious: People who needed a tool had to go to the tool room to see if one was available, and sometimes they’d forget to mark the tools when they checked one out. Tools were not where they were supposed to be, often getting left on job sites or forgotten about and not returned. Over time, the problem got worse and ended up costing the company thousands of dollars to replace lost and damaged tools.
Enter Access 2013 and Office 365. Microsoft worked with McKinstry to create a tool tracker app using Access 2013 (and hosted on Office 365) for the company to try in their Colorado office. The app features a table for tools, with views that show which are available or checked out, displayed by their storage location. There are also tables for people, jobs, and storage locations, which make it easy to associate this data with each tool. A checkout history shows the usage history of each tool displayed within the new Related Items control. The checkout history displays the last time the tool was checked out and who checked out the tool.
The Access web app UI provides a clear, easy-to-use layout for navigating the data in an Access app, a built-in Related Items control that shows the items associated with the record being viewed (think customers and orders), and a fast text-based search for data. Initial feedback was very positive, but some tweaks were requested, such as a home page with basic directions and buttons for the most common tasks (like checking out a tool).
With the early feedback addressed, daily use began at McKinstry. People could now find out whether a tool was available online from their office before going down to the tool room. They could see where the tool was stored, the condition of the tool, and its usage history. They could see who had the tools and when they were expected back.
While the UI and ease-of-use was appreciated, searching for a tool by typing in the tool name proved to be too much overhead for the people in a hurry to get out the door to their job site. They asked if a faster solution was available, such as a barcode scanner like those used for checkout in retail stores. The feature appears complicated but was actually quite simple to implement in Access. The scanner simply sends keystrokes through a USB connection, so Access uses built-in search to instantly identify the tool based on the code in the record for that tool. A data macro launched from a custom Action Bar button checks out the tool and adds a record to the checkout history with a simple button click.
McKinstry uses the Access app in their Colorado office using an Office 365 account, and plans wider adoption into other offices using either Office 365 or an on-premise app once they deploy SharePoint 2013. They know where their tools are and their current condition–improving productivity and providing a significant cost savings. Watch the video to see the tool tracking Access app in action.
Today’s post is from Suzanne Emberton, a mother of six and full-time IT support analyst in Tennessee. Suzanne is an avid Office user, and in this post, she explains why Office 365 Home Premium is the right fit for her busy family.
I am busy. VERY busy. Not only do I have a large family of 4 kids and 2 step kids, I work full-time and volunteer often. I’m also a self-professed geek, so I am always looking for ways to make my life easier through technology. One amazing tool I’ve found to help my busy life is Office 365 Home Premium. With one subscription, I get all the Office products on up to five devices! Since our family seems to have 2 or more devices per person, this gives us plenty of Office goodness to spread around.
Traditional Office applications like Word, Excel, PowerPoint and even Outlook are basics I’ve always known and loved. Outlook is my family planning hub. I can easily sort through work and personal email quickly and I manage my calendars and tasks lists here as well. Outlook 2013 has great features like Quick Steps that make managing email a breeze. With an account at Outlook.com, all of the information syncs to the cloud and back to my Windows Phone, so I’m always connected to this essential information. I call it my brain, because I’d be lost without it!
Word and PowerPoint are used extensively in our home as well, for school reports and projects. The kids adore the new feature that allows you to search and insert images from Bing directly into the document. With their Microsoft accounts and SkyDrive making cloud storage so easy, they have access to their documents anywhere. My step-daughter has even started work on an essay for school in Word on her Windows Phone. She saved the initial draft to her SkyDrive and opened it in Word on our desktop at home to finalize it. As the official family proof-reader, the ability to share and manage these documents anywhere makes assisting with homework a much easier task for me. We even have the grandparents helping with homework now through using Skype video chat and sharing and editing a PowerPoint presentation together. Talk about family collaboration!
The Office 365 subscription also comes with a few applications that weren’t traditionally available for a Home and Student license scenario, Publisher and Access. Publisher is fabulous for creating flyers, newsletters, invitations and more. In our family, it has saved us from late night trips to the store, when invitations for a birthday party were needed by the next morning! A diverted family argument equals a happier home! Access is also great for creating databases, such as family heirloom recipes or DVD inventories. Templates on Office.com make getting started with this powerful program super easy.
My favorite program in the Office 365 Home Premium package though, is by far OneNote. With OneNote, I am attempting to go paperless in our home. I keep our chore lists, grocery lists, scans of important documents, scans and pictures of the kids art projects, scans of owner’s manuals to all those electronics and appliances we seem to accumulate, scans of various teacher notes and school reminders and more. I spend a little time each week scanning this stuff into OneNote and it keeps the mountain of paperwork in our house down to a much more manageable pile.
With OneNote on my computer, and my notebooks stored in the cloud and OneNote on my Windows Phone, I have access to all this information everywhere. The rest of the family also has access to the shared notebook and can add items during the week that they need, like notebook paper or shampoo. I make my grocery list at my desk computer, and then head off to the store, with my list on the phone. No more scrap paper or forgotten lists on the fridge!
At only $99 a year, it is a great value as we can easily share one license between all of our household computers and keeps us up to date with the latest Office. I feel like Office 365 Home Premium helps makes me a better mom overall and more focused on the relationships in our house, not keeping up with schedules, schoolwork and the mountain of paper. Office 365 Home Premium has been an amazing tool for our family that makes technology actually useful!
Register now to join us May 3-5 for the Portland Access User Group (PAUG) 2014 Database Designer Conference. This year’s speaker lineup includes industry experts F. Scott Barker from AppsPlus, Luke Chung from FMS, Alison Balter from InfoTech Services Group Inc., Armen Stein from J Street Technology, Juan Soto from IT Impact, and Kevin Bell and Kevin Nickel from the Microsoft Access team.
This is the 16th annual Database Designer Conference and will once again be held in the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing. The conference is limited to 42 attendees, which creates a very intimate setting with ample access to the experts, but also means it sells out every year. There are currently only a few drive-in spots available so don’t delay—register now. We hope to see you there!
This post was written by Steve Greenberg, a Program Manager Lead on the Access team.
Every few months, the Microsoft Access engineering team receives a high priority e-mail from someone in our worldwide sales org. It’s always the same root issue: an IT department is frustrated with the proliferation of Access databases and the manageability headaches that ensue. Recently, we talked with the CTO of a British company who compared databases to feral cats wreaking havoc on his organization’s work.
We find that this focus on manageability is nearly universal among customers we talk to, both large and small. Organizations want to empower users in business units to do self-serve development of apps that improve efficiency and reduce costs. But empowerment creates headaches. Data must be secured so that only the right people can access it. It must be backed up so that hardware failure is minimally disruptive. And there must only be one copy of the data, so that all parts of the organization make consistent decisions.
This release, we have a number of exciting things to announce about managing databases with Access. First, let’s talk about some new tools for managing desktop databases:
These tools help manage desktop databases. But most people agree that the right place to store data is on a server. Data that’s centrally stored on a server is inherently more reliable and secure than data floating around as files on file shares and desktops. As you evaluate your data tracking options going forward, you should consider Access 2013 Web Apps. Manageability is one of the most significant reasons we chose to build Access 2013 Web Apps on Office 365 and SQL Azure. Here are some of the benefits:
Reliability: Office 365 and SQL Azure are both enterprise-class cloud services backed by robust Service Level Agreements. To learn more, check out the Office 365 Trust Center. Also, here’s a recent blog post about fault-tolerance in SQL Azure Databases.
Security: You can use SharePoint permissions to control who can create new apps, modify the tables, queries, views and macros of existing ones, edit data and read data.
Monitoring: IT organizations often want visibility into the usage of applications in their organization. To meet this need, SharePoint collects data about each Access Services app. To access this information, visit the Site Contents page. Hover over the app, and click the “…” icon. Then click Details.
Here’s an example of what you’ll see. The App Details page lists properties of the app, allows you to see any errors encountered by users of the app, and gives usage statistics to help you understand how often the app is being used.
For the administrator who loves dashboards, SharePoint allows you to configure monitoring so that you can visit one web page and see the usage of selected applications in your organization.
As a team, we’ve always cared deeply about manageability and believed fundamentally that data is more manageable when it’s on a server than when it’s on a desktop. A SQL Server in a datacenter is the best place for data to live. But for years, the cost and technical barriers to creating such a server meant that it was only available for mission-critical projects. With SQL Azure Databases, we finally have the technology available to allow end users to easily and affordably create real SQL databases in a datacenter. Check them out using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.
There are many ways to build useful apps to run your business with Access 2013. You can download an app via the Office Store, build an app using a Web App Template, or create a Custom Web App. You also have the ability to make a desktop database, just like you always have in Access. Each method offers a fast and easy way to get started. Several members of the Access team explain more in the video below:1 – Use the Office Store
Getting an app from the Office Store is a quick way to start using a database that’s been professionally designed for a specific purpose. To use the store, you’ll need to sign up for the Office 365 preview. (Be sure to choose one of the plans for business: Small Business Premium or Enterprise). After you’ve logged into your account, click the elipsis (…) in the upper-right-hand corner of the screen, then click “Office Store.” You can easily search for apps related to your specific business. You’ll find a wide array of SharePoint apps available—the ones that include the Access 2013 logo are Access 2013 web apps. After you find the app you want, you can click the Add button to install it directly to your SharePoint site and start using it right away.
If you want, you can customize the app with Access 2013. For example, you could easily add a field to a table to track some information that’s specific to your business. You could even add new tables and new relationships to track additional types of things. When you install an Access app from the Office store, you can get up and running fast, but there’s still plenty of room to grow.
2 - Use Web App Templates
You can also make web apps directly from Access 2013. The easiest way to do this is by clicking on one of the web app template tiles that you see right after opening Access. You can search for additional templates if you don’t see what you’re looking for. These templates are fully-functional web databases, but instead of immediately seeing them in action, you’re taken straight to the customization step. In our case, you can select “Task management” (outlined in blue).
Once you’ve selected your template, give it a name and pick a location. If you’re logged into your Office 365 preview account (see above), you will see two available locations by default. The first is for personal apps that you don’t want to share with others. The second is for your company’s default SharePoint team site. This is a great place to put an app that you want other people in your organization to be able to use.
When you click Create, Access makes an app in the location you selected. The template you selected will include tables, relationships, and views that make it useful from the start. You can start adding data immediately by clicking Launch App in the ribbon. Or, you can customize the app in Access to your heart’s content.
3 – Build a Custom Web App
You can also build an app by starting from scratch. You can create custom tables and fields for your precise needs or search and add tables from our library of Table Templates. To build a custom web app, open Access 2013 and click on “Custom web app” (highlighted in blue). Just like with Web App Templates, you’ll need to give your app a name and a location.
You’ll be initially given a blank database with no tables or schema. The easiest way to start building your app from here is to use table templates. To search for a table template related to what you’d like to do, simply type something in the search box and hit enter. You’ll see a list of search results from the library. For example, if you type “Orders,” then click on the Orders table template from the results, you’ll find that a bunch of useful stuff related to tracking Orders has been added to your blank database.
Each template includes all the tables, fields and views you’ll need to create a working app. Some templates come along with other, related tables. For example, the Orders template also gives you a place to track related Customers, Employees, Products, Suppliers, and Categories.
Access includes a large searchable library of these templates. Whether you want to manage products, employees, customers, projects, tasks, or ideas, it’s likely you’ll find a template that’s tailored to your needs.4 – Build a Desktop Database
If you don’t have Office 365 or SharePoint, you won’t be able to create web apps (options 1-3 above). You can, however, create desktop databases that run on your machine. If you’ve used previous versions of Access, these are the databases you’re already familiar with, and you’ll find it easy to work with files you’ve created in the past. Desktop databases have all the powerful features, such as VBA, that has made Access such a popular way to run a business.
To create a new desktop database, open up Access and click on any tile that uses the word “Desktop” such as the “Blank desktop database” or “Desktop asset tracking” (highlighted in blue). Just like with web databases, you can start from scratch or choose from a large library of templates. Once you’ve selected the template you want, give it a name and click create to start editing.
When Access is finished downloading your template, you’ll be presented with the familiar, rich database building experience you’ve come to expect from Access, complete with reporting, advanced forms, and VBA.Conclusion
No matter which method you choose—the Office store, Web App Templates, custom Web Apps, or Desktop Databases—Access 2013 will help you get started quickly. Try it out today by signing up for the Office 365 Preview. If you’d like to take advantage of Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.
Need a quick tutorial on Access 2013? Here’s your chance. You’ll learn how to create your first database in no time. First, we’ll go over the two kinds of databases you can create: browser-based Access apps and desktop databases. Then we’ll show you how easy it is to download a template–a readymade database with all the parts in place.
You just fill it out and you’re good to go. From there, we’ll step you through the basic parts of a database: tables, queries, reports, forms and macros.
Want a sneak peek? Here’s a 30-second trailer.
What you will learn at Tuesday’s webinar
References for this webinar
Go to http://aka.ms/offweb for more information on how to join the series.
If you can’t make it to Access Day in Seattle on November 21, why not attend the UK Access User Group 2013 National Seminar at the Microsoft Thames Valley Park in Reading?
The UK Access User Group conference is presented by and for Access enthusiasts, and the fall 2013 seminar features 5 sessions on a variety of topics:
Shape Up Or Ship Out! Microsoft Access Case Study (Presenter: Paul Millennas)
Paul will show how Microsoft Access has been used at Babcock Engineering at Rosyth on £12billion worth of projects including the construction of aircraft carriers, ships and submarines.
Adding Value To Your Applications By Integrating to Accounts Software with Specific Examples
using Sage 50 (Presenter: Tony Bayliss)
Nearly every Access project includes integration, and Tony will show how this has been achieved on some of his recent projects.
The Patient Death–What Happens Next? How a Hospital Handles and Manages the Human Side of a Patient Death (Presenter: John Baker)
John will present a case study of an application used in a key area of the National Health Service.
Web Application in Access 2013 Case Study (Presenter: Alan Cossey)
Alan will take us through the stages of development of an Access 2013 web database which includes both a standard Access front end and a web UI.
The Way Forward: A Discussion Session (Presenter: Rod Gordon)
If you are asked to develop a new system in the years to come, then what technology will you use? Building on the day’s presentations, Rod will lead a discussion session considering the different options available to you today and in the coming years.
Join us Thursday, November 21 for a 1-day Microsoft Access conference in Bellevue, WA. Armen Stein, long-time Access MVP and president of J Street Technology, is organizing Access Day, which features internationally-respected presenters, including Access MVPs, book authors, and members of the Access development team at Microsoft.
Kevin Bell, Luke Chung, Tom van Stiphout, George Hepworth, Andy Couch, and Armen Stein will lead sessions on a mix of Access client techniques and new Access web app development using SharePoint and SQL Azure. The intended audience is Access developers and advanced users who want to learn ways to make their applications more powerful and develop Access solutions for the web.
The conference will be held at The Bravern in downtown Bellevue, which is walking distance from the transit center and many hotels.
Check out www.AccessDay.org to register and to learn more information and schedule details.
After a long, hard effort by many people, the stars have finally aligned. The Access team is pleased to announce the release of the Access 2013 Runtime! We know lots of you are very anxious to get your hands on the Access 2013 Runtime. You can now go and download the Access 2013 Runtime from the following location:
The Access 2013 Runtime is available for download in 38 languages.
Microsoft Access 2013 provides a rich platform for developing database management solutions with easy-to-use customization tools. If no end user customization is required (including report modifications), you can choose to distribute those Access 2013 solutions so that they run without requiring a full installation of Access 2013. To do so, you must package and distribute your application with the Access 2013 Runtime.
Web-based apps for SharePoint that are built with Access 2013 do not require a runtime–only a supported web browser is required.
Join our own Kevin Bell as he takes a break from his summer vacation to show off the new Access 2013! Kevin will present to the Denver Area Access User Group meeting on Tuesday, July 2 at 6-9pm MDT and to the Colorado SharePoint Users Group on Thursday, July 11 at 6-8pm MDT.
Both meetings will be held at the Microsoft office located at 7595 Technology Way, Suite 400, Denver, CO 80237.July 2, 6-9pm: The best of both worlds: Hybrid apps with Access 2013
Access 2013 web apps allow you to quickly create a database in Office 365, but if you are familiar with traditional Access desktop development, you may encounter some limitations. One of the best features in Access 2013 is that web apps created on Office 365 use SQL Azure to store their data. Unlike traditional SQL Server databases, SQL Azure is cloud-based, so your data can be accessible anywhere. However, every byte of data requested from the Access client is now traveling over the internet, so you need to be very diligent about returning the least amount of data possible. Kevin will show several techniques to help you convert your existing desktop applications to an Access 2013 ‘hybrid’ app. Go to www.daaug.org to learn more about the Denver Area Access User Group.July 11, 6-8pm: Real SharePoint apps, real fast with Access 2013
With the new Microsoft Access 2013 and Access Services 2013, anyone can build SharePoint Apps backed by SQL Azure in just about 60 seconds. In this presentation, Kevin will show you how easy it is to create, share, and collaborate with Access 2013 in an IT managed environment. The new Access allows you to satisfy the need for agile development from your business units and still rest assured that the data is secured, backed up, and managed appropriately. Kevin will also talk about how legacy Access desktop applications within your organization can be brought forward to the web on SharePoint and SQL Azure. Go to www.cospug.com to learn more about Colorado SharePoint Users Group.About the presenter
Kevin Bell is a test engineer on the Microsoft Access team in Redmond, Washington. Prior to joining Microsoft in 2008, Kevin was a partner in a consulting practice in Colorado that specialized in building software solutions for small businesses and departments in large corporations, primarily working with Microsoft Access and SQL Server. Over the years, Kevin has been a member, officer, and frequent presenter at the Denver Area Access User Group and now regularly presents at the Seattle Access User Group and the annual Portland Database Designers Conference. This past November he had the pleasure of presenting at the 2012 UK Access Groups National Seminar in London.
This blog post was originally published in September 2012, and we’ve updated it to announce the recent release of the server applications. It is brought to you by Steve Kraynak, Office Program Manager focusing on spreadsheet management features.
In April, we released 2 important new spreadsheet management server-based applications to complement the desktop spreadsheet management features we introduced with the release of Office 2013.
Now available are Audit and Control Management Server (ACM), and Discovery and Risk Assessment, which are both designed to help you manage the use of spreadsheets and Access databases. Now there are 5 features designed to help you manage the use of Excel spreadsheets and Access databases: We talked about these applications in a post last September, and now they’re available to customers:
The acronym EUC, which refers to End-User Computing applications, has become a common way to say “critical spreadsheets and databases created by end users without the involvement of IT.” The 2 new applications are aimed at helping organizations deal with the significant risks introduced by EUCs.
In many ways, EUCs are a very good thing. A powerful tool like Microsoft Excel gives the end user a fantastic amount of ability, agility, and efficiency. However, with great power comes the chance to make some mistakes. In many situations, these EUCs are very important spreadsheets or databases that are designed, developed, tested, accepted, and used by a single person or a small group of people. It’s the norm that EUCs are created on the fly, without a structured development process (design, develop, test). Since we know that humans occasionally make mistakes, it’s a good bet that mistakes will make their way into the EUCs and that these mistakes will go unnoticed until something bad happens.
The data produced by the EUCs is typically trusted and accepted by stakeholders, and often becomes the basis for critical decisions and reporting. Without proper controls, use of EUCs can lead to financial and operational loss, through undetected errors, unexpected mistakes, and even fraud.
With the 5 new applications mentioned above, we now offer much-needed tools to help organizations begin to gain control of their EUCs:
Three of the new tools are available on the desktop with Office 365 ProPlus or Office Professional Plus 2013. These include Spreadsheet Inquire, Spreadsheet Compare and Database Compare. Because Spreadsheet Inquire is an Excel Add-in, you need to turn it on in your Excel Options to start using it. Spreadsheet Compare and Database Compare will show up as shortcuts along with the rest of your Office tools in the Windows Start Menu.
The server products, ACM Server and Discovery and Risk Assessment, are available through volume licensing and enterprises can talk to their Microsoft sales representative to learn more about licensing with SharePoint and Office.
One of the first steps in tackling any problem is to understand the problem. If an organization hasn’t taken stock of their EUCs, and hasn’t put in place adequate procedures and controls, then chances are good that no one knows how much risk they have with EUCs, or even how many EUCs they have. Discovery and Risk Assessment gives organizations the ability to create an inventory of their EUCs and analyze them so they can determine which EUCs are the most complex, impactful, and risky.
Simply having an inventory is one important aspect of controlling EUCs in an organization. The new Discovery and Risk Assessment application also helps you determine which are the most risky, so you can start taking further steps to reduce your risks. It does this by crawling specified network paths and SharePoint sites to find the EUCs, and then it analyzes each EUC and gives them a rating for complexity, materiality (impact to the organization), and risk based on configurable criteria.Finally! Comparison tools for Excel (and Access, too)
The new desktop tools provide some great efficiency improvements. If you ever wanted an easy way to compare 2 spreadsheets, now you have it. Spreadsheet Compare lets you pick any 2 workbooks and compares them in a fraction of the time it would take to you to do it manually. Also, the differences between spreadsheets are categorized so it’s easy to focus on important changes, like changes to formulas or VBA. You can also see changes to the data in cells. Spreadsheet Compare makes it easy to distinguish between the different types of changes that can occur in a spreadsheet. Also, Spreadsheet Compare is able to determine when rows or columns have been inserted or deleted and it factors those changes into the equation before comparing the cells that may have shifted as a result. Rather than showing differences just because thousands of cells moved down by one row, Spreadsheet Compare can simply report that a row was inserted.
Database Compare provides similar capability for Access databases. Now you can pick any 2 Access databases and get a report of the differences between the tables, queries, modules and more. If someone changes an important query, now you can easily see exactly what was changed.Powerful analytical capabilities
Spreadsheet Inquire gives you the ability to perform a detailed analysis of a workbook in Excel. You can think of the Workbook Analysis as an MRI for spreadsheets. The Workbook Analysis feature gives you the ability to find and report on almost 50 different aspects of your spreadsheet. For example, you can easily find all the formulas with errors, inconsistent formulas, or invisible cells, and you can create a report with all this information so you can begin to document and understand your spreadsheets better, which is a critical aspect of any good EUC control program.
Another highlight of Spreadsheet Inquire is the Workbook Relationship Diagram. This feature maps out the data lineage of your workbook. If you have linked workbooks or data connections, now you can quickly see of where all the data is coming from, whether you have broken links, and whether the data is current. On the other hand, maybe you’re in the process of developing a new spreadsheet. In that case, you can use these same tools to help you build a better spreadsheet from the start.Keep track of changes in all of your mission-critical EUCs
The workhorse of all these new products is Audit and Control Management (ACM) Server, which provides powerful change management features for Excel and Access files. ACM Server keeps a close eye on your critical EUCs and gives you the ability to see the “who, what, where, and when” for changes in EUCs down at the cell level. You can see if someone has modified formulas or VBA code, and ACM Server will show you the old value and the new value after the change. ACM Server also keeps the revision history for your files, so you can easily compare any 2 versions, or even restore a prior version.
To get more information about ACM Server and the rest of the spreadsheet control tools, please read Spreadsheet Management with the new Office.
Join us in New Orleans for Microsoft TechEd North America June 3-6and in Madrid, Spain for Microsoft TechEd Europe June 25-28.
Scott McFadden and Jeff Conrad from the Microsoft Access product team will present on the new Access web app features available in Microsoft Access 2013 at this year’s event in New Orleans. Harrison Shapley and Kevin Nickel from the Microsoft Access product team will share a similar presentation at the TechEd event in Madrid.Sign up for the Access Services session at TechEd
If you’re attending Microsoft TechEd this year, be sure to sign up for the Access Services session:
SES-B309 – Real SharePoint Apps, Real Fast with the New Microsoft Access 2013.
Come see how Microsoft Access 2013 and Access Services 2013 will let anyone build SharePoint Apps backed by SQL Azure in just about 60 seconds. In this session, learn how to create, share, and collaborate in an IT managed environment. Office 365 provides everything you need to enable end users to create high-value, “no code,” collaborative business apps. The new Access allows you to satisfy the need for agile development from your business units and still rest assured that the data is secured, backed up, and managed appropriately. Also, hear about how legacy Access desktop applications within your organization can be brought forward to the web on SharePoint and SQL Azure.
In addition to learning more about Microsoft Access 2013 and Access Services 2013 at the Microsoft TechEd events, you can find out about the future of Microsoft products, solutions and services directly from the leaders, complete with news, announcements and demos. Unlike traditional classroom training with a fixed set of topics, TechEd allows you to personalize your learning experience. Mix and match the sessions-and session types-you want to attend, including breakout sessions, hands-on labs, keynotes, and more. Each of the four days provides you the opportunity to completely immerse yourself in sessions that offer insight into the products you are working with.
Connect with thousands of your peers and Microsoft product team members who build the products you work with every day, as well as industry experts who are deploying and building Microsoft solutions that may benefit your projects. Explore vendor solutions that can help you achieve your goals. Discuss challenges and brainstorm potential solutions with like-minded individuals at structured networking opportunities including the TechExpo Welcome Reception, Ask the Experts, and the Attendee Party in North America and Country Drinks in Europe.
Explore products and tools that can make your job easier and cut down on the time you spend troubleshooting issues. There is no better way to learn about Microsoft products than to play with them in the hands-on labs, at demo stations and elsewhere throughout the conference. TechEd is your best chance of the year to see how Microsoft products were made to be used and learn how to use them.Join us in person
In today’s online and always-connected world, there is tremendous benefit in having dedicated time away from your office to focus on advancing your expertise, minus the distractions. By attending TechEd in-person, you are giving yourself the best chance to learn, connect and explore and take advantage of everything the event has to offer.
We hope to see you there!
Access 2013 web apps are great for collaborating around a common set of data. When people work together in this way, the changes that one person makes to the data often require the attention of someone else.
For example, if a small team is working together to track and process maintenance requests, an Access app would be an ideal place to centrally track those requests and their status. Further, imagine that I process a maintenance request and assign it to another team member who needs to take action on it. But how does he or she know? Wouldn’t it be great to be automatically informed? That’s what notifications can do.
In this article, we’ll describe a technique to build e-mail notifications into your Access 2013 web app using the power of SQL Server and a third-party service called Zapier.How it works
In the above example of our maintenance request tracker, people need to get an e-mail when a maintenance request is assigned to them, letting them know of a required action. In the Maintenance Requests table, there’s an Assigned To field that is a lookup to the Employees table. Whenever that field changes, an e-mail should be sent to the person to whom the Request is now assigned.
Building this notification functionality involves three steps, detailed further below:
By the end, you’ll have something that looks like this:Step 1: Create a “Messages” table
First, you’ll need to create a Messages table to store information about the email notifications that you want to send. This table will need to have fields that specify all the details about the email message, including:
Click on Create > Table in the ribbon, then select the add a new blank table link. Add fields in the table designer so that it looks like this:Step 2: Create a data macro on the Maintenance Requests table
You want messages to be sent whenever the Assigned To field changes in a record on the Maintenance Requests table. Data Macros are the perfect tool for the job. Data macros are commands that run whenever records on a table change. For a given table, you can write macros that run when records are added, deleted, or updated. In our case, you’ll want to write an On Update macro on the Maintenance Requests table.
To do this, first, open the Maintenance Requests table in the table designer, go to the Design tab, and click Events > On Update.
You’ll only want to send the notification if the Assigned To field has changed. To do this, use an If statement that compares the old value of this field with its new value:
If that condition is true, you’ll want the macro to create a record in the Messages table. Since you’ll want this new Messages record to have the users’ e-mail address in the To field, you need to first look that up and store it in a variable:
Finally, use this email address to create the message:
Then, this macro will add a new record to the Messages table whenever the Assigned To field of a record in the Maintenance Requests table changes.
Since, in this example, you also want people to get notifications when a new Maintenance Request is created and assigned to them, you’ll create an After Insert macro that looks like this:
The only difference here is that we’ve changed the If expression at the beginning to run the macro if the Assigned To field is not blank.Step 3: Configure Zapier to monitor the Messages table and send messages
Zapier is a third-party tool that you can use to build simple connections between one service, like an Access 2013 web app, and another, like email. Zapier calls these connections “Zaps.” To get started building our zap, go to zapier.com and sign up for a free trial account, and click Create New Zap
You’ll then find the services that you’d like to connect. In our case, we’ll choose SQL Server > New Row as the “trigger” > Gmail > Send Email as the “action.”
Click Create Zap and you’ll be prompted to enter some more information about these connections. Start with the SQL account. You’ll need to tell Zapier how to get permissions to look at the SQL database that powers your Access 2013 web app. Zapier will want to know things like server name, database name, username and password. To find out these things for your database, open it in Access, click File > Info > Manage Connections.
From this menu, you’ll want to click From Any Location to disable firewall rules that would otherwise prevent a third-party service like Zapier from connecting. Then, click Enable Read-Only Connection which will generate a username and password that Zapier can use. To view these details, click View Read-Only Connection Information, which will show you something that looks like this:
Back in Zapier, you can copy this information into the Zap. Be sure to use port 1433 to connect:
Next, you’ll want to tell Zapier which table it should monitor for changes and how it can tell which rows are new. We’ll pick the Messages table, and tell it to examine the ID field. Since the ID field is a number that is automatically increased by one as new rows are added, this will be a reliable way for Zapier to tell the new rows from the old ones.
Next, you’ll want to enter the credentials to your Gmail account so that Zapier can send the emails. Gmail is just used as a means of sending-the notifications can be sent to any inbox, such as Outlook, Yahoo, etc.
After your Gmail account is configured, you have to tell Zapier how to map the fields in the Messages table in SQL to the fields that Gmail expects. This part is pretty straightforward: the To field in Gmail maps to the To field in SQL, and so on:
That’s it! You can easily test out your Zap by choosing “Click to load samples!” This will load the first row that Zapier finds in the Message table, and you can click to send a sample message.
The final step is to make your Zap live. This means that Zapier will run it every 15 minutes to check for new records in the Messages table, sending emails if it finds any.
You can try Zapier out for free for 15 days. Since SQL connections are a premium service for Zapier, you can sign up for a paid plan when the trial expires. See www.zapier for pricing details.Conclusion
This article has outlined how to use notifications for a specific example, but there are many other situations where this capability will be useful. However, regardless of the scenario, you can follow the three steps above. We can’t wait to hear about the cool things you’re able to do with this technique!
This post was written by Doug Taylor, a Program Manager on the Access team.
As you manage your business in an Access 2013 web app, the amount of information it holds will naturally grow over time. The default views and navigation are great for getting started quickly, but eventually, wading through all of that information by scrolling through lists may not be the most efficient setup. Is there an easier way to create a different view of the data? In Access 2013 web apps you can create a customized filtered view in just a couple of minutes.
For example, let’s say you work for a marketing company that deals with local Car Dealerships and you have created an Access app to track the projects you manage for your customers. If so, you might end up with a set of Projects that looks something like the example below. Here you have a list of all your projects from A-Z.
Over time, you will have quite a few projects to manage, so you may start to break them into categories as we have above. Each project in this list belongs to a category: Research, Marketing, or Design. As you can see, the Templeton Triangle project shown above is a Research project.
Now, one of your research assistants – let’s call him Dave – asks if you can create a view to help him see an overview of current and upcoming Research projects. So, you take a minute to talk with Dave and the two of you decide that a view like the one shown below would be great for him. This is a list of all Research projects, sorted by Priority and Start Date.
To create a view like this, start by creating a new query – open up the app in the app designer and click on Advanced->Query in the ribbon.
This will open the Query designer where you can setup some rules to filter and sort a set of data.
Choose the source table for your query. If you want to see some of your Customers, then you would likely choose the Customers table here. In our example, we’re going to select Projects.
Once selected you’ll see the query design screen. Here you can double click on fields from the Projects table to add them to your query.
Selecting fields this way determines which of them you would like to see and use in the new view. As we decided with Dave earlier, we’re going to select the following fields:
NOTE: If you would like to EDIT from the view, then you MUST INCLUDE an ID field in the query as we have done here.
Now that you have chosen the fields, there are two things left to do.
First, Dave wants to see the Projects in PRIORITY order first, followed by START DATE. To do this, fill in the “Sort” box in each of these fields as Ascending (i.e. A to Z).
Second, since Dave is a research assistant, he’s only really interested in Research projects. So, we’ll want to filter out any other Project types as well. To do this, find the Category field in the query and add the CRITERIA “Research” (including the quotes). This tells Access to only show Project that have a category of Research.
When you’re finished, the query should look like the example below. Now you can save it, and give it a name. We’ve named our example “Projects_Research Only”.
Great! Now that we’re done with that, we can go about building the view. Go back to the App Designer home screen, navigate to where you would like to add the view, and click on the button. Give the view a name, select Datasheet for the View Type, and be sure to select the Query you just made as the Record Source.
Now, when you click “Add New View” you will have a nice summarized view of your Research projects, sorted by Priority and Start Date.
And that’s how to design a simple filtered view in Access 2013! Try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.