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
Hi haloo I like to access my new app to Start To downloud Android free Apps in data to shire in my local service in my Town Muqdisho Somalia Banadir
I think this really is big, "IF" Access 2103 web apps also now support Linked Tables. If that is also true in addition to all of the other features described above, then we now have a tool that can support publishing the thousands and thousands of legacy departmental Access applications that are deployed in enterprises around the world.
So, does Access 2013 web apps support Linked Tables?
Jeff - Let me distinguish between two senses of "supporting linked tables," one of which is supported, the other of which is not:
1 - (supported) - Linking from a desktop Access database to the SQL database that is the back-end of an Access web app.
You could import your legacy Access apps into a new Access 2013 web app. This would cause a SQL Azure / SQL Server database to be created using the same schema as the desktop app. Then, you could go back to those desktop databases and use linked tables to point them at the SQL Server/Azure database that was just provisioned. In the end, the front-end would be provided by the same desktop database files as before (which would be opened in the Access client, not through a browser). The data would be stored in the cloud on SQL Azure (or on-premise in SQL Server). There would also be a browser-based front-end created when you imported the legacy data. You could ignore this if you wanted too, though, and continue to use the desktop database files as before.
2 - (not supported) Using linked tables to expose external data from within a web app.
If you imported your existing desktop databases into an Access 2013 web databases, you would not be able to create linked tables that would expose tables belonging to an external SQL Server or Access database within the web app itself.
Let me know if that made sense.
Hi Andrew.
The fact, that a Access 2013 Web App can't connect to other data sources than the one's in the own app, is cutting one of Access most powerfull and usefull features: Connecing different data sources in a RAD frontend.
I am working in an enterprise environment as Access-Developer and our Access desktop-databases mostly link to MSSQL server-databases. For example nearly every database is linked to our "employee and login administration".
Although we have Sharepoint 2010 with Access Services we do not use the Access Web Apps, because we are not able to link to existing external data. And that will be the same with the new features of Access 2013. We will still use the desktop client.
I really can't understand where this is a big thing. The Access Web Apps are a nice toy for powerworkers.
But in the end you made a tool that leads to data islands which can't be connected and that leads to data redundance.
Both the opposite of what a data architect dreams off.
Will this scenario also work with Sql Server Express or will the user be required to by the full version?
Will it still be possible to work on a hybrid application in a disconnected mode on the local catch, as described for the previous versions of the Access and Sharepoint combination? or has that architecture been changed?
Thanks
I see a huge problem with this arhitecture. the problem revolves around governance. in Access 2010 and SP2010 architecture, a Access web databases translated into a site and tables translated into a list. The great this about this is that many company go not have a very rigid structure around creation of lists and therefore it is easy for end users to create and use this functionality as they want to.
Come 2013, every access web database would translate into a sql server database. this means that like any other database, we have to do 3 types of backup on these (weekly full, daily differential, and 30 minute tlog). we have to write these to tape. we have to mirror them. and also cluster them.
All this results in a huge amount of work for the IT and DBAs. furthermore as I saw in access services that people tend to do a lot of trial and error in creating and publishing many access web databases sites before they get it right. if this is done on this architecture, there are going to be massive number of unused databases.
Let's face it. creating a databases is not the same thing as list. lists don't require DBAs databases do.
there are costs assosiated with the maintenance of databases.
What Microsoft should have done is that all the access databases should go into one (or many physical databses) very similar to how sharepoint sites go into physical content databses.
That way DBA could decide how many access services databases they want to maintain.
If end users start publishing SQL databases on the enterprise farm, then it just makes the life hell for DBA because they have to do maintenance exercise.
In our environment I see our DBA would fight to death to get this thing disabled because they don't want end users to create their own databases at will.
Andrew,
I am currently evaluating both access2013 and LightSwitch.
Will there be a Macro within Access 2013 that will enable calling stored procedures from the SQL sever database?
While this will not be used by power users it will enable professional developers to overcome early limitations in Access 2013 web applications.
.
The whole point of Access is to free oneself from I.T. control.
There comes a time in user developed applications when they need to be professionally enhanced.
That is the point when I.T should get involved.
Hi Andrew,
Yes, your explanation makes sense. Unfortunately, that was not tthe answer I was hoping for.
Just like another commenter (Holger) pointed out, the way many enterprises use Access Desktop databases for departmental solutions is by using a combination of native Access tables and Access Linked Tables. The normal scenario is that the native tables are used for the real data that the application maintains and the linked tables are "master" type of data that exist in line of business databases. The linked tables are typically used in lookup types of relationships.
For instance, let's say an engineering department of a large enterprise needs to maintain an Access database for the department that tracks equipment maintenance history. They develop an Access database for this, with two native tables: Equipment Items and Maintence Record. Equipment Items is the table with the list of all of the individual pieces of equipment that they have to maintain. Maintenance Record is the table that tracks the history of maintenance activities for each Equipment Item. Of course, the two tables are related because when a maintenance activity is entered into the Maintenance Record table an Equipment Item needs to be specified via a lookup.
What I have just described works just fine with Access 2010 Web Databases. The application can be built using all Access 2010 native tables and then published to Access Services 2010. Of course, the data is maintained in SharePoint Lists once it is published, but it works. Your article in this blog post is telling us that in Access Services 2013 the data will no longer live in SharePoint Lists when the database is published to SharePoint, it will live in a new SQL Server database that Access Services 2013 creates.
This new functionality is wonderful. I applaud the Access team for it!
But, it does not solve the problem that limits most enterprises from using Access Web Databases in the real world.
In the real world, in the example Engineering database application I describe above, there is a third table that is needed to complete the application properly and efficiently, AND it really can't be a native Access table. It is another lookup type of table that "lives" in a line of business system in another application inside the company that contains master data about all of the company's Facilities. In essence it is the "golden" master list of Facilites that the company owns. It is maintained by a seperate line of business application that the Engineering department doesn't "own", but does have access to. For sake of argument, let's say the Facilities data is in a database in a seperate SQL Server.
To complete its Equipment Maintenance tracking application, the Engineering department needs to link to the Facilities SQL Server table so that Equipment Items can be related to the facility in which they are installed. For years, this has been incredibly easy to do in an Access Desktop Database using the Linked Table feature. And, I strongly believe that it has been used WIDELY inside enterprises.
My expierence is that enterprises would dearly LOVE to take all of their departmental Access databases and publish them to Access Services and let them live there. The enhancement you have made to Access Services 2013 makes that desire even greater.
But, it again will not be possible because you haven't supported the one feature that the vast majority of these applications use: Linked Tables.
Even if there is some technical or security or performance reason for not supporting Linked Tables in Access Services, I don't understand why you don't at least support SharePoint Business Connectivity Services (BCS) in Access Services 2013. If you did that, it might solve the problem.
Okay, let me get this straight:
In the Access Web App, you cannot connect to an external table, like an SQL Azure table (thus, any other web app table). BUT in a Access desktop app, you can link to an SQL Azure (Access Web App) table.
Does that mean that Access Web Apps can't connect to SharePoint list either?
On the plus side, since Access Web Tables are no longer SharePoint lists, does that mean that we don't have to does with SP List limitations anymore? (i.e: 6 row limit). We haven't played with the "old" Access services but some of the limitations of standard SP lists like 72 max number fields can sometime be a wall when working with financial reports.
We're lucky enough not to have a lot of legacy Access DB so starting correctly will be important. An article on the new Access Web App limitations (vs. desktop, the old Access Web DB or standard SP lists) would help us plan what to us when.
You can connect to an external SQL Azure table from an Access ACCDB (desktop database) but not in a web app. Your summary is correct.
However, you can link to a standard SharePoint list in read-only mode in your web app. In this way, you can use and display information form a SharePoint list on the web.
Each table in an Access web app is a SQL table. The only constraints on data in the table are the constraints defined by SQL Server/SQL Azure. In Access 2010, we did a lot of work to make the SharePoint limitations transparent to the user, but we still had to throttle queries that returned large result sets such as 50,000 rows. Access 2013 has no such issues with large result sets and will actually optimize to only return rows the user is viewing. The query engine is plain SQL Server - you get all of the goodness that comes with this.
I guess that explains why the word "app" was more appropriate than "database" (beyond obvious buzzwords). They'll be powerful tools for self-contained projects but won't play so nice with others. Good to know.
last little question: being Azure objects, will we be able to connect them to Excel/PowerPivot for Excel or PowerView in SharePoint Online BI sites? I have a project in mind for which it could be useful for but would need some good graphing capabilities.
Russell,
What you are saying makes it sound like the following to me:
1. An Access 2013 Web Database can connect to a SharePoint List (presumably to do a lookup) in a read-only manner
2. A SharePoint List can be backed by an external content type that can be a an external database table that is accessed through the BCS (just standard out-of-the-box functionality for SharePoint)
3. So, we can achieve the same "linked table lookup" functionality that is available in Access desktop databases by using Access Web Databases, linked read-only SharePoint Lists and the BCS.
Is what I just wrote correct?
As Russell pointed out, I omitted one sense in which Access 2013 web database can connect to external data--through SharePoint lists (read only). We do understand that having additional types of external data available in web databases is important for the future. Thanks for the feedback.
Comments: (loading) Collapse