Access 2013 and SQL Server

re: Access 2013 and SQL Server

RustyH — Tue, 21 Aug 2012 19:39:36 GMT

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?

re: Access 2013 and SQL Server

mburns_08109 — Thu, 16 Aug 2012 15:11:01 GMT

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

re: Access 2013 and SQL Server

Andrew Stegmaier — Tue, 14 Aug 2012 18:00:38 GMT

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

re: Access 2013 and SQL Server

mburns_08109 — Tue, 14 Aug 2012 13:30:37 GMT

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!

re: Access 2013 and SQL Server

mburns_08109 — Mon, 13 Aug 2012 14:57:48 GMT

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

re: Access 2013 and SQL Server

Andrew Stegmaier — Fri, 10 Aug 2012 18:56:21 GMT

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.

re: Access 2013 and SQL Server

Andrew Stegmaier — Fri, 10 Aug 2012 17:59:51 GMT

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.

re: Access 2013 and SQL Server

Andrew Stegmaier — Fri, 10 Aug 2012 17:54:59 GMT

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.

re: Access 2013 and SQL Server

Andrew Stegmaier — Fri, 10 Aug 2012 17:06:38 GMT

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.

re: Access 2013 and SQL Server

rogerj — Fri, 10 Aug 2012 16:30:45 GMT

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