Access 2010 And SharePoint – Welcome To The Hybrid Access Application

Now that we can talk about all the features in Access 2010 I think I have to place my marker immediately on what I believe is the BIG story in this release.

For the past 3 or so years I have kept hearing internally at MS about the Web story in what became Access 2010.  The idea seemed to be that in order to sustain a future for Access it was necessary to “tie its can” to SharePoint’s bumper and offer itself as a somewhat functional database tool for lists and relatively small data requirements inside SharePoint.  I always had trouble with that idea because I was concerned that by doing so they would marginalize the very community that Access needs to survive, namely the worldwide non-network of people using client-side Access (often secretly) to produce Departmental Solutions.

But nonetheless I jumped on the bandwagon a couple of years ago.  Reading the writing on the wall I stuck my neck out and developed a serious application using Client-Side Access 2007 with SharePoint 2007 Lists as the backend.  And it worked !!!  In fact that application has been in production for over a year, and with a little help from Terminal Services for those users spread around the world (literally), this application has worked and worked well (despite the kludges I had to employ to get everything to work well).

But in the background of all of this was with the expectation that Access 2010 would provide a far better story.  Well it’s true !!  But not in the way many expected.

Welcome To The Hybrid Access Application

While a lot of the talk will revolve around the pure Web-story in Access 2010 I believe that is not the REAL story – and any effort to make that the real story would be misguided.

The conception of the Access 2010 Client – SharePoint 2010 Server integration is not only brilliant it was also very, very ballsy.  I cannot imagine a bigger leap of faith than what they did.  That is the REAL story of Access 2010 and Access Services.

In the end Access 2010 allows the traditional client-side Access developer to stage their entire application inside SharePoint on a Subsite created by Access Services.  When an Access application is “Published” to SharePoint all objects (tables, queries, forms, reports, etc.) get stored inside the SharePoint subsite. 

The user needs only a small (200+ bytes) shortcut to subsequently launch this application from SP.  But wait – the first time a user launches the application, Access Services reconstitutes (they use the cute word “Re-Hydate” at MS) in a folder on their client machine C: drive in binary format (not XML).  Then Access launches THAT version locally.  But while the data is cached on the local machine (again in binary format) this data is directly synched to the actual tables INSIDE SHAREPOINT. 

Yes – the data, although cached locally, is synched in real time – BOUND – to the actual live tables inside the SharePoint site.  Any changes to data is instantly synched with the Server version.  So the user gets the benefit of using a cached client-side version of the full-featured client Access application but the data is natively staored on a SharePoint server.

Next time the user launches the application, Access Services synchronizes the local cached version with the SharePoint site and only takes action on objects and data that might have been changed since the last visit to the site.  Therefore in most cases subsequent launches will be close to as fast as when using a local version of the data.

At the same time the developer (or developers by the way) can make changes to the application both front and back and then Synch their changes to the Server.  Subsequent visits to the application by users will then automatically drive those changes to their local cached version to keep them in synch as well.  No more re-deployment hassle !!

In order to even more enhance the performance of the client app, Access Services has its own special caching mechanism ON THE SERVER that works in conjunction with the local cache while the user is in the application.  It is my hope that this will allow client-side Access applications to operate across a worldwide Intranet EVEN using only a VPN connection.  If that is the case (and it is something I have not been able to confirm yet), then suddenly we have the ability to deliver Access applications to anywhere across a corporate Intranet with total real-time concurrency and performance that will be more than acceptable (especially when one considers the distances involved).  Getting access to Terminal Services or Citrix is a pretty easy process in most Corporations anyway if necessary.

Certainly an existing Access application cannot be Published to SharePoint Server without a few changes.  Indexes, relationships, lookups, primary keys will all have to be changed but these requirements will be easily defined and the Compatability Wizard in Access is a BIG help for that.  The pain is minimal and is all manageable (nothing is ever totally FREE in life anyway ). 

One important point is that IF the application is actually backended on SQL Server, those links can be preserved and the application can live on SharePoint without even using Access Services tables on the Subsite. But having the application live and be deployed through SharePoint using Access Services will still be the way to go.  In fact applications that started as Access MDB or ACCDB backends can migrate to SharePoint Tables and then up to SQL Server tables as demand requires …. all that with virtually no changes to the Front-End objects of the database.  Think about that one……

In the end Access 2010 provides the existing Access developer with a path upwards to the wonderful Browser-based world without giving up any of the capabilities of the Client version of Access.  In effect what we are looking at here is what I would have to call a true “Client-Server” scenario – one that truly puts the processing and the objects always in their proper place to produce the best results – how often have we really seen that? 

This is a big deal (IMHO).

Dick

Gemini Is Now PowerPivot

The Gemini code-name for Microsoft’s new BI add-in for Excel and SharePoint has now been officially named “PowerPivot” !!

I just had a great chat with Rob Collie of the PowerPivot team about this product and I must admit that I am more and more optimistic about this application and about the potential for BI to move to the world of REAL users with REAL needs for advanced data analysis without the overhead of Analysis Services and DBA’s, etc., etc.  At the same time when the need grows to the point where the “Big-Iron” needs to come into play PowerPivot will work just fine.  Feel free to visit Rob’s site http://powerpivotpro.com/ ….

Of course I am excited about this from an Excel standpoint, but perhaps the biggest story will be in SharePoint instead.  If so this cannot but help Excel’s legitimacy as a high-end analytical program and as part of serious BI solutions for business small and large. 

It might just make us a little money too .

Dick

 

Dos Cervesas Por Favor

I am now back from two weeks of roaming around Europe with my Wife.  We are in love with both Amsterdam and the Costa del Sol.  I have never returned from a vacation more fired up to get back at it – which is a pretty good sign methinks. 

One funny thing was how the reception our dogs gave us when we walked in the door was so joyful (and we were so tired) that I forgot the suitcases outside the door all night – oops.

The first week of our jaunt was spent in the Netherlands speaking at the SDN conference (http://www.sdc.nl/).  I have spoken around the world in Australia, the UK and all over North America and I have never seen a more professional conference, nor have I ever felt more appreciated as a speaker by those organizing it.  This was also done almost completely with volunteers from the Dutch IT community.  Maybe they could make some extra revenue by consulting to others about how to do it right.  Thanks especially to Joop Precht and Maarten Van Stam for their help and especially to Peter Hamming for saving my ass when I couldn’t get my Macbook Pro to talk through VGA.  I am sorry that other names are not at hand but I hope they would know who they are when I say thanx to all of you.

My sessions included one on Best Practices for using Excel and two on Access 2010.  I will be letting out my content over the next while on here so keep tuned.  But suffice to say, the enthusiasm for Office was obvious amongst attendees who came to my sessions.  I got the distinct feeling that there are people out there interested in the capbilities Office provides overall, not just in the cool new stuff.  There is no doubt in my mind that more emphasis on Office Development, especially Excel and Access would be well recieved out there by many who actually have to suppress their enthusiasm for the technology to be taken seriously. 

As I told one session, it is possible we won’t have to avoid saying we are Access developers when people ask what we do for a living . 

Hopefully.

Dick

Beautiful Amsterdam

I am in Amsterdam today.  What a beautiful place.  Wife and I did the boat-tour thing and it points out that this musta been some place a couple of hundred years ago !!

I am here to speak at the SDN conference about, among other things Access.  I have been told this group started as a “Clipper” user group !!  Wow !!  I’m excited to get to talk to some true believers (I am assured there are some going to be there).

Will keep you posted.

Dick

Oct 19th Will Be A Big Day For Access

On Oct 19th at the “SharePoint Developers Conference” (formerly the Office Developers Conference by the way) in Lost Wages, Nevada I hear there will be a “reveal” of a bunch more of the functionality of Access 2010. 

Of course I can’t tell anyone what is going to be announced, but trust me that it’s going to be very important for the future of Microsoft Access and especially IMHO for existing Access developers of all skill levels both part-time and professional.The big question will be “Will anyone notice?”.  I certainly hope so and to a great extent that will be the responsibility of Microsoft Office Marketing (who I believe DO understand the significance of Access 2010). 

But it will also be up to US to get out there and promote this version to people we know and to people we don’t know.  It’s in all our interests (users, developers) to improve the visibility and explain the positioning of Access in the Corporate world going forward.  With Access 2010 I beleve we will have an opportunity to re-ignite that message. 

If not, then a big opportunity for us, for our clients and potential clients and for Microsoft will be lost – and that would be a shame. 

Dick

My Name’s Tucker – Not Sucker !

This is NOT a discussion of “Precision” of Excel Formulae -  for that see http://smurfonspreadsheets.wordpress.com/2009/09/25/excel-cant-add/ …..    This is about the Business Math of Excel and/or Access development.  (I know you all like short snappers on Blogs but sometimes the issue needs to be fleshed out).

I have always had a difficult time coming up with “fair and balanced” terms whenever I am negotiating with large consulting organizations for work in Excel or Access.  Part of this is the usual problem of how much a “Pimp” is worth (with all due respect of course).

The fact is that Desktop Development such as ”I” (and I assume “You”) do is anathema to the model of a typical Consulting firm.  I use the tools I use because I want to deliver the best possible solution to my clients for the least money to them and for the most money to me on a net per hour of effort.  Lately that means getting adequate Fixed Price deals and praying for no “gotchas”, but in the end it all comes down to the most money for each unit of work I provide.  If I do not provide my results for a reasonable cost the business will simply not be justifiable on the part of the client and trouble will (or at least repeat business will not) ensue……

In a large technology consulting practice the focus is on finding projects where the most money is available for whatever project and squeezing the maximum out of the client.  Does that sound cynical – sorry …..  That is not because they are bad people, it is because they are first and foremost business people.  Business people with a model based not so much on the joys of clever and efficient, flexible and maintainable  solutions and the assumption that making the client happy will lead to more business and adequate compensation but moreso on providing solutions that satisfy that “professional” veneer that large IT shops demand to satisfy their sense that what they’re paying for is up to their high standards. 

It’d be great to be able to take the businessman’s approach to Office development, but my problem is that IT simply doesn’t consider Excel or Access fitting their model (and they’re right) and so if I am accepted at all I am relegated to low budget, quick and dirty prototyping temporary solutions where budgets are low but at least usually expectations aren’t great either.  As I’ve said before IT has more and more become the “gatekeeper” to technology budget.  The days of end-user client departments paying the contract are pretty much gone.   What we have to do is find a way to sell into this environment with claims (and proof) that we can deliver productive solutions for low cost and usually on a short time and then go ot and get enough of that business to keep the “lights on”.

But if our services are to be offered as part of the offerings of a large consulting firm, the business managers of that firm would be crazy to allow us to go in and draw down their average and threaten their precious “Premium” business – and rightly so.

So in the end I believe that the only way to organize a “firm” that delivers Excel or Access solutions would be to organize it exclusively for that purpose with a business model wrapped around the realities of that type of development.  That means a business driven by productivity not gross billings.  A business that flies against what would be considered “normal” business rules.  The idea has to be to deliver and deliver and deliver as efficiently and as cleanly as possible.  That means people with top skills that understand and appreciate what their technology can and cannot do and who are looking for satisfied repeat customers and referrals.  It needs people who love wat they’re doing and believe in what they’re doing.  But who know that they deserve at least as much as your typical .NET developer or SQL Server DBA, and the business succeeds or fails on that basis – not on the gross profit after paying as little as possible to those that actually do the work. 

I have noticed that many large firms get the one BIG score and then don’t come back for several cycles (unless they’re smart enough to get the client to sign a long-term contract – which always stuns me in its awesome stupidity ….).  That model does not work for us.  Happy loyal clients has to be our stock in trade.

Will this model work?  It will if it’s run like a Law or Accounting firm with working, billing Partners as the sole owners (no VCs thank you).  I believe though that until Microsoft does what they have to do to support this community (their community)  with the necessary “Spin” in THEIR message in the field, this will not be a reality. 

I saw this in the heady Y2K days and it worked, for a while – then the bubble burst.  This has to be a new business model that will stand the test of time and be a parallel force to those chasing the BIG scores.  Otherwise we’re done.  It will be time to give up and become .NET developers and let the customers learn the hard way that we DID provide something they really need… As I always try to think “My name’s Tucker, not Sucker!”  

Dick

p.s. Thoughts ??  Harlan ??

How Does Anyone Develop Anymore?

Is it just me or do others spend more time on a project dealing with IT and D.B.A.s and S.O.W.s and P.O.s and meetings and other time-wasting and project delaying B.S. than actually developing the application anymore?

I am personally having real trouble quoting on projects anymore because of the uncertainty of all the peripheral crap that can’t be predicted (but that should definitely be expected ).

Dick

Away This Week

Sorry to my faithful few that I haven’t posted this week.  It’s because I’m in Redmond learning a whole bunch of things that I can’t tell you about – really.

All I can say is that I am pleased with what I have seen and heard and I think we will all be happy when O2010 ships.  Then all we have to do is get our companies or clients to go to it.

Talk soon.

Dick

OK – Gemini Is Cool – Me Bad

I don’t know if everyone who comes here reads all the comments on my Posts but I thought I’d make a new Post about the info recieved on the previous Post in which I “Dissed” Gemini.

Turns out I was wrong (never !!!) as thanks to a couple of comments from Amir Netz of the Gemini team I have now been appraised of the fact that Gemini is in fact a BI tool as I would define it.   I will be in Redmond next week and have arranged to meet with Amir while there.  So I will have more info to pass on (subject to any NDAs that might be in play ) the week after that.

As a consultant I am excited about the presence of DAX because not only does it sound like the way to true BIness but it also sounds like the kind of thing many (most?) users might need “professional” help with (catch my drift ?)

Here are Amir’s comments once again for you:

Hi Dick,

DAX (Data Analysis Expressions) is the way to express BI logic. DAX is a combination of Excel expressions enriched with BI extensions.

Admitedly, CTP2 shiped with a very partial implementation of DAX so I can understand the sentiment here.

But as CTP3 is looming, I suggest you stay tuned to the added capabilities of DAX. I think you’ll find it pretty powerful for task of expressing BI business logic.

Amir, from the Gemini team.

Well, Gemini is indeed designed as a BI tool. But it is a different kind of BI tool than the run-of-the-mill ones. I think you already realized that we put the emphasis on speed, scale, simplicity and Excel consistency rather than on an exotic feature set.

Still, the core of Gemini it is based on the Analysis Services engine and believe me, we take BI very seriously in this team. Gemini is no toy.

The exact release date of CTP3 is not set yet, but it is not too far… All the features are in and we are now polishing the code to make sure that you have a high quality experience with it.

There are many new goodies in the CTP3 release, but there is no doubt that the full DAX is the biggest of them. A very large portion of the team worked on DAX for CTP3 and the code just got integrated on Friday.

The full DAX documentation will be available for CTP3. We may have some blog entries and demos ahead of time illustrating the power of DAX. But I think we have a few more weeks to go before we are ready to disclose the breadth of the language.

If you are going to the SharePoint conference in LV you’ll have a chance to see DAX in action on stage. We have a full session in which DAX will star and you’ll get to see the CTP3 in action.

Amir (still on the Gemini team)

Thanx Amir..

Dick

 

Maybe I Jumped The Gun On Gemini

I am shocked that I would be a victim of marketting (Who Me (??)) but it looks like I might have been on Microsoft’s new Gemini product.

After my “epistle” on the exciting prospects of the Gemini being promoted by Microsoft (Google/Bing  ”Gemini and Microsoft” and see what you find) I have only been able to confirm that Gemini provides the ability to assemble large data sets from multiple data sources, relate them as any Relational report generator can, and use the results as the source for an Excel Pivot Table.

I have TRIED to get some answers on how this amounts to “BI” or even “OLAP” as many of the documents out there refer to Gemini.  Either they need to do a better job of explaining to people like me WHY this is BI or I am going to conclude that this is simply an MS Query replacement.

Until further notice I retract my excitement about the Gemini product.

Dick

p.s. Special thanx to Sam for saying this against my earlier Gemini Love-Fest:

Submitted on 2009/09/03 at 10:35pm

Dick..
Play with the tool and if you find something that the tool can do that Excel already cant… the that would be interesting…I am still searching…