Back
Access

Access 2013 and SQL Server

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.

How it Works

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:

Tables

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:

A table in Access 2013.

The resulting table in SQL Server looks like this:

The same table in SQL Server.

Queries

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:

A query in Access 2013.

It is stored as a formatted statement in SQL Server:

CREATE VIEW [Access].[MyQuery]
AS
SELECT 
       [MyTable].[ID],
       [MyTable].[String Field],
       [MyTable].[Date Field]
FROM 
       [Access].[MyTable]
WHERE
       [MyTable].[Date Field] > DATEFROMPARTS(2012, 7, 16)

Data Macros

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.

Events in Access 2013 get translated into AFTER triggers in SQL. 

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

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

SQL Server Schemas

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.

So What?

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 backstage view of a database in Access will give you the information you need to connect to the back-end SQL database. 

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.

Open (or close) connections to the back-end SQL 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.

SQL Server Rocks

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.

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags

Join the conversation

35 comments
  1. 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.

        • 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.

          • @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?

          • @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.

  2. 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.

    • 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.

  3. 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.

    • 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.

  4. 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.

      • The database is a standard SQL Azure database, so you will be able to connect Power View or any other technology that SQL Azure supports now.

    • 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?

      • Sorry, but BCS lists don’t work.. This feature only supports standard SharePoint lists at this time.

        • 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 masterlookup 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.

  5. 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

    • 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.

  6. 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.

    • 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.

    • 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.

      • 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!

  7. 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.

    .

    • 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.

  8. 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!

    • 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.

      • I have made a webapp with Access 2013 for which I want to make a local reporting database. On clicking on File, I can see server name and database name. When I click on make reporting database, a file is created, but a message is displayed to download driver for connecting the reporting database. On clicking yes to this message browser window opens to a survey by Microsoft. Anyway, I downloaded the Server native client 11 and now it shows up in ODBC connecting database drivers lists. I have also created firewall rule for access through 1433 port. However, when I run the ODBC connecting dialogue error in connection shows up asking to allow IP address by logging in the Azure portal. However being a trial user of Microsoft Office 365 for small and medium business, I can not access Azure SQL server.

  9. 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

  10. @Andrew,

    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…

  11. 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?

  12. What about the limitations regarding primary keys? Are we still forbidden of using compound keys?

  13. We have developed a ADP application for sql server over the last 8 years.
    Is it true that Access2013 does not support ADP’s?
    Is ist true that SQL Server 2012 does nor support ADP’s from Access 2010?
    Wat can we do to make our (very very big) Application sustainable ?

    • reccomend to stay with Access 2010 until they fix the SQL Server (ADP) compatability issues. I had heard that they are planning on fixing the SQL Server compatability issues with SP1.

  14. Dear all, I like the fast performance of the new Access Apps on the Web. I got 3 questions, maybe somebody can help:
    1: Is there a way to add created Query in the Menu Structure, so a User using the Web App on the Web can execute them?
    2: Is there a way to add a print button on the Web App?
    3: Is there a way to assign more detailed user permissions on the Web App? Like one user should be able to see only the Customers, anther user only the Suppliers etc.

    Thanks, David

Comments are closed.