You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
This post was written by Russell Sinclair, a Program Manager on the Access Team.
Access 2013 web apps feature a new, deep integration with SQL Server and SQL Azure. In Access 2010, when you created a web application on SharePoint, the tables in your database were stored as SharePoint lists on the site that housed the application. When you use Access 2013 to create a web app on SharePoint, Access Services will create a SQL Server or SQL Azure database that houses all of your Access objects. This new architecture increases performance and scalability; it also opens up new opportunities for SQL developers to extend and work with the data in Access apps.
When you create a web app in Access 2013, you'll choose a SharePoint site where you want it to live. Your app can be accessed, managed, or uninstalled from this site just like any other SharePoint app. In the process of creating your app in SharePoint, we provision a SQL Server database that will house all of the objects and data that your application requires. The tables, queries, macros, and forms are all stored in this database. Whenever anyone visits the app, enters data, or modifies the design, he'll be interacting with this database behind the scenes. If you create an app in Office 365, the database is created in SQL Azure. If you create an app on a SharePoint server that your company hosts, Access will create the database in the SQL Server 2012 installation that was selected by your SharePoint administrator. In either case, the database created is specific to your app and is not shared with other apps.
As you build your app, you can add tables, queries, views, and macros to deliver the functionality you and your users need. Here's what happens in the database when you create each of these objects:
When you add table to your Access app, a SQL Server table is created in the database. This table has the same name you gave it in Access, as do the fields you create in the client. The data types that are used in the SQL Server database match the types you would expect: text fields use nvarchar; number fields use decimal, int or float; and image fields are stored as varbinary(MAX).
Consider the following table in Access:
The resulting table in SQL Server looks like this:
When you add a query to your app, Access creates a SQL Server view (or a table-valued function (TVF), if your query takes parameters). The name of the view or TVF matches the name you used in Access. We even use formatting rules when generating the T-SQL, so if you view the definition directly in SQL Server, it will be easy to understand.
This is a query designed in Access:
It is stored as a formatted statement in SQL Server:
CREATE VIEW [Access].[MyQuery]ASSELECT [MyTable].[ID], [MyTable].[String Field], [MyTable].[Date Field]FROM [Access].[MyTable]WHERE [MyTable].[Date Field] > DATEFROMPARTS(2012, 7, 16)
Data macros come in two flavors: event data macros and standalone macros.
You can create event data macros by opening a table in design view and clicking on any of the Events buttons in the Table ribbon.
Event data macros are implemented on SQL Server as AFTER triggers on the table to which they belong.
You can create a standalone macro from the Home ribbon by clicking the Advanced button in the Create section and choosing Data Macro from the list of items. This type of macro can take parameters and is persisted as a stored procedure in SQL Server.
Views in Access 2013 are the parts of your app that display your data in the browser—database experts might call them forms. They are also stored in the database. Since they are HTML and JavaScript rather than SQL objects, they are stored as text in the Access system tables
Within the database, Access makes use of three separate SQL Server schemas: Access; AccessSystem; and AccessRuntime.
The AccessSystem schema contains system tables that store the definitions of each object in a format that Access Services understands, as well as bits and pieces of information that are necessary in order for the item to work well in the runtime or design time surface.
The Access schema contains all of the tables, queries, and macros created by you, the app designer. Everything in this schema is the implementation of the objects you designed in SQL Server.
The AccessRuntime schema contains a number of items that we use in Access Services to optimize the runtime behavior of your application.
You might be wondering why these details are important. For some users, the only visible effect of the new SQL Server back-end will be increased speed and reliability. They don't need to worry about the technical details. More advanced users, though, can directly connect to the SQL Server or SQL Azure database from outside of their Access app, which enables a whole new frontier of possibilities for advanced integration and extensions. This is big!
To enable external connections, simply click on the File menu to go to the Backstage. Under the Connections section, you'll find the SQL Server login credentials that you can use to connect to your database in SQL Server Management Studio, ASP.NET, or any other application that supports SQL Server.
The Manage connections button contains a number of commands that allow you to manage connections to the SQL Server database. You'll find that you can generate a read-only login and a read-write login. Use the read-only login when you want to connect to the SQL Server database from a program or app that doesn't need to modify the data, such as a reporting tool. Use the read-write login when you want to connect to the database and modify or enter new data. For example, you could create a public website in ASP.NET that allowed internet users to submit applications that get stored in your Access database.
Please note, however, that this functionality is not currently available in the Office 365 Preview. If you'd like to try it out, though, you can download the Microsoft SharePoint Server 2013 Preview and set it up on your own servers.
We are really excited about these changes to Access 2013 and we hope you are as well. SQL Azure and SQL Server give Access 2013 a powerful data engine to house your data. They also enable many new scenarios for advanced integration and extension. We can't wait to hear about the great new apps that you'll build with Access.
Comments: (29) Collapse
Gilad - if you want to deploy Access 2013 web databases on-premise, the full version of SQL Server 2012 is required. By far the easiest way to get web databases, though, is through Office 365. In that case, Microsoft takes care of all the hosting details through SQL Azure. We understand that not everyone is ready to make that transition, we think as time goes on and the value Microsoft is able to deliver through the cloud becomes more and more compelling, this will be less of an issue.
2013 web apps don't support hybrid applications in the same way as 2010 web databases. In 2010 web databases, tables are stored in SharePoint lists. When the Access desktop client opens a 2010 web database, there are locally cached versions of these SharePoint lists that can be modified while offline and synced back to the server later. In 2013 web databases, the data lives in the SQL Server / SQL Azure database only. When you view data it the Access desktop client, you need to be able to connect to this server in order to modify the data or the design.
BI.DW - I understand your concerns about manageability. By far the easiest way to address these worries is to let Microsoft handle it for you. If you subscribe to Office 365, we'll host your Access 2013 web databases on SQL Azure, which provides backup and geo-replication. The management cost from your end is zero.
If on-premise SharePoint and SQL is necessary, one thing to tell your DBA is that because the SQL databases associated with Access 2013 web databases are, for the most part, very lightweight, he will likely be able to pack them quite efficiently on a single server. We'll have details in a future post about exactly what to expect on this--and how to plan.
OK, thanks for the clarification on this Russell.
Well, to sum up my conclusion on the subject -
This is the third version of attempts to integrate Access with SharePoint (MOSS 2007, SharePoint Server 2010 and now SharePoint Server 2013). Each version has made significant improvements and the Access product team is to be congratulated on that. Unfortunately, the lack of being able to integrate data from outside the SharePoint farm (i.e. from line of business systems) is going to continue to hinder the adoption of Access Services in a major way, in my opinion.
I know because my company has been one of the market leaders in advanced public and private SharePoint training since 2003. I have probably trained at least 5000 SharePoint professionals over the last nine years. The vast majority of them are from medium to large enterprises in the U.S. When we talk about Access and Access Services in our classes there is a lot of interest because there are so many departmental Access solutions that they would dearly love to move off the network drives into SharePoint. But, almost universally they use Linked Tables in their existing Access applications for master\lookup types of data. Without support for this type of data in Access Services, the whole thing is a non-starter for them.
What is frustrating to me is that we have now had three versions of Access integration with SharePoint and we are no closer to solving this basic problem AND we will have to wait another three years to see if it gets solved in the next release of Access and SharePoint. In the meantime, I don't see that this is a feature that can realistically be provided by an ISV Partner. So, once again, we are stuck doing without.
Thank you Access Team! This is huge, Huge, HUGE! I would have to agree that the inability to used external linked tables is a problem. I suspect the reason is because of limitations in the access services web interface. I can see this being a problem because you're not using sharepoint BCS entities and not all data sources available as linked tables in desktop apps are available in BCS. However, what's the technical reason for not having links to tables in other Access Web app dbs?
Another question...Do Office365 subscribers have the ability to publish to SQL AZURE instance of our choosing? If not, can we get the credentials and servers so we can access SQL AZURE administration for a given Access web app? If answers are no and no then that's another problem.
When will you release the Web Reporting story for 2013? We're hoping the SQL Reporting multi-tenancy thing got figured out so web reporting will be availabe in O365 this go-round. Thanks again this is a big deal!
It's still not clear to me how SQL Server/Windows Azure SQL Dabase (formerly SQL Azure) will handle multi-valued lists without getting SharePoint lists involved.
Cheers,
--rj
Finlay - When you create standalone macros in an Access 2013 web database, they get translated into stored procedures on SQL. There is currently no supported way to write arbitrary T-SQL directly in a stored procedure on the database and then call this function from within the Access macro language.
We do understand, though, that enhancing the ability for professional developers to extend Access web apps is an important thing for the future. Thanks for the feedback.
Jeff - As Russell pointed out--and I neglected to mention--Access 2013 web databases support read-only connections to SharePoint lists. I can see, though, that this wouldn't address some of the scenarios you were talking about.
We hear you loud and clear that this is important, and we'll take this feedback into consideration for the future.
Fredric - You're correct to note that since Access 2013 web databases don't store their tables in SharePoint lists, they don't have the limitation that 2010 web databases had in terms of numbers of fields and sizes of tables.
That's a great idea to provide an overview of the limits of various database types. We'll put that post on the schedule.
jbooker - when you publish an Access 2013 web app to Office 365, we handle the SQL Azure database creation for you--you can't currently choose to create the SQL Azure database somewhere else. However, we do make it easy to get the connection information to the database - see the last part of the post.
As far as reporting goes, we do provide an easy way to hook up a Access desktop database to the SQL Azure / SQL Server database. You can then take advantage of all the powerful reporting tools already present in previous versions of Access.
@Andrew,
The more I hear and understand about the way the MS-Access Dev team is approaching this Web migraitno path, the more convinced I become that they simply do not understand the market niche that their product actually occupies out in the real world. This also explains why my plaintinve ciesy for a MS-Access Professional Developers RoadMap have gone unheeded for the last few years.
Producing any verison of MS-Access which totally lacks, out of the box, any ability to interface with the larger workd around the database applications which would be produced using it is nothing short of lunacy. This has been done not only in one dimension surrounding the product now, but in two vital areas. First, in the move form VBA into this new Macro-based development paradigm, there is NO ability to interface with the platofrm APIs UI APIs, or to otherwise fill in the gaps of otherwise inadequate programmability. Since we don't have VBA or COM/ActiveX availabel via Macros, we also don't have the ability to call out to WIndows APIs or WEB APIs. Now, on top of that, we don't have the ability (at elaset not easily, out of the box) to directly reference existing corporate databases/spreadsheets/documents for external data too?
Wow. This new Web-oriented MS-Access direction may well define an entirely new category of "crippleware".
Tell me this: I have an application being built new as an Access DB applciation which was being moved up from an Excel spreadsheet. It is used for scheduling/projecting labor across upcomming projects for a large company. In order to make the UI for this, we could either creata an acess form with 12+ monthly-data subforms OR automate Excel for that part of the UI. With the monthly data subforms, there are problems synchronizing the windows scrollbars to make the data aoo move together, this is handled by making Windows API calls.
With the WEB App, how could this type of UI requirement be handled? (There are also business rtules surrounding the creation of new columns and rows which must also be enforced, so your solution must also provide the necessary event hooks to implement this business logic). This is typical of the sort of application requirement we face as consultants. I would dearly love to understand how the new web environment can even come close to matching the client-siude functionality required to produce this sort of application (did I forget to mention the need to interface with 3rd-party HR systems and other data sources to pull the data into this applicaiton also?)
Additionally, "BI DW Architect" nailed another aspect of this: in organizations of any size or complexity, "IT Governance" rules MUST be observed. The sort of "Professional-level users" or "Power users" who have traditionally done the early work developing these sorts of productivity applications at the departmental level for which MS-Access is famous(/infamous?) will *NEVER* have the permissions granted to them to build the new database entities in the corporate SQL Server environments that is envisioned/required in this Access-for-the-web pathway. The IT department and the DBAs/Managers will go APE at the thought of granting their users the necessary authority to do that sort of thing - else how can they manage backups/ data storage estimates/ permissions. user groups . etc.. etc.
Yeah, I know - Here goes Mark, the voice of Doom and Gloom again, but seriously, have you guys really thought this all through?
Finlay,
In any larger organizations - and by "larger" I mean: any organization large enough to have an organized IT Department - then IT Governance rules enter the picture. Once this happens in any enterprise, then the ability to "free range" SQL Server databse entities stops because SOMEONE becomes RESPONSIBLE for manageing and protecting the databses and resources under IT's control. Backpus are required, and policies to manage and protect the databases and resources become the priority. Sooner or later formalized Change Control procedures enter the picture also.
The point here is that all of that lies across the road as a barrier manned by IT department staffers between any Web-enabled/SQL-Server-backed application vision desired by departmental-level users/managers and the realization of those designs/hopes/dreams. It becomes a chicken-and-egg question at that point. I have seen this already time and time again with "Classic" Thick-client MS-Access applications. Once the server-based resources under IT's control become a required element of MS-Access application creation/design, then everything that BI DW Architect said enters the picture - but NOT "later on ...when moving it into production", by which point a demonstatable value-addd case for involving IT department resources and staff time can be made to management to gain the needed approvals (because the Access application presumably exists and can be demonstrated for them already), but now all that "management overhaed" becomes FRONT LOADED onto ALL the procedures for any such software development thoughts for department-level Power-Users or managers. Once that happens, they have to answer the "Why do this in Access?" question _and be able to defend their answer from a hostile/critical IT management mindset_ at the inception point of their ideas for the software project.
THIS ALONE WILL KILL MANY MORE ACCESS APPLICATIONS BEFORE THEY ARE BUILT THAN IT WILL PERMIT!
...or am I the only one here who can see the pattern of this developing?
From my POV, this looks way more like a great plan to KILL OFF Access rathern than being a plan to save it!
@Mark
We are aware that the new web databases won't be able to be used in all cases, particularly the more complicated scenarios. We saw the move to the web--and to a SQL server back-end--as a necessary step to bring access databases into a new era. Providing ways for advanced developers such as yourself to hook into the platform and provide the missing functionality is an area that we know is important for the future. Thanks for your feedback here.
Today, though, there are still plenty of ways to take advantage of 2013 web databases while still providing options for advanced extensibility. You could use the web interface for most tasks, but connect a desktop access database (or any other software) directly to the SQL back-end to provide last-mile functionality.
It's also important to note that desktop databases are still supported in Access 2013. We expect that these solutions will continue to provide value as we build out and improve the web platform.
About the governance question--we think that Office 365 provides by far the easiest path for businesses large and small to take advantage of Access 2013 web apps. Organizations that choose this route don't have to worry at all about provisioning SQL databases. For IT departments that need to host things in-house, there's an option available. Once they understand how efficiently Access databases can be packed onto a SQL Server, we think some will be persuaded of the value.
About your specific scenario, I don't quite know what you mean by "12+ monthly-data subforms" It is entirely possible that an Access web database would be able to do this, but I'd need more details about the scenario to tell you for sure.
I have posted a follow-up reply to your answer to me on the governance question, but in order to avoid hijacking this discussion or comments any further, I moved my reply over to my LInkedIn.Com group's discussion area. It is under the Professional Microsoft Access Developers Network group (PMADN) under the discussion title "Dear Andrew Stegmaier..." I hope you can find some time to help me better understand Microsoft's thinking on this topic either in this discussion area or that one...
I get that the Access Web App can't have linked tables that connect to another database, but once you have the Access Web database created on SQL, will you be able to create a view in the SQL database that can pull data from another SQL database or other data source, then the view would be available to the Access Web App, or can you only have views to the local database?
Comments: (loading) Collapse