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.
Richard is not someone you would call an avid Access developer. He recently wrote about his experience building an Access application on SharePoint. I have reposted his article here with his permission.
So what I have I been doing of late? The blog has been a bit quiet. Well I have been having a good look at Access Services in SharePoint 2010. This has been an interesting experience, as I am not historically what you might call an avid Access developer.
Like most .NET developers, I had looked at Access as more of a file format than an application, something from the past. Something that I might use for a small data store, maybe in a web application hosted on a cheaper ISP that does not provide ‘a real’ SQL DB, or where an XML data files don’t seem right, often because I just can’t be bothered to work out the XPATH. When using Access as a data format it seems easier to get at my data using basic hand crafted SQL commands or maybe at most via a OLEDB Data Adaptor/DataSet. All very old, old school. Thinking about Access in this way just seems an easy way out, playing it safe with the knowledge I have. I don’t for a second propose that this is a good idea. You should not be looking at using any technology just because it is there and you already know it. There are obvious downsides, using Access in this manner meant that from the ADO.NET developer side I could not:
But equally, by treating Access as just a data format I was not able to make use of it as the Rapid Development tool it is. I was too hung up in the unpleasant idea of an MDB sitting of a server being poor at locking and saturating the network with unwanted traffic. I was not even considering Access as a front end to a MS-SQL solution, and it is not as if that is new technology, it has been around for ages. I was just sitting happily with my prejudices.
I don’t think this position is that rare for .NET developers these days. Access seems just looked down upon as something old in the Office pack that is best ignored, and no good would come of using it in a business environment.
So enters Office 2010 and SharePoint 2010 Access Services. For me this changes the game. For those who don’t know this technology, you can create an Access database locally on your PC then publish it to SharePoint. Tables become SharePoint lists, macros become workflows and forms become forms. Access becomes a RAD tool to create data driven SharePoint sites.
So how has this new technology been working for me? Well I can’t say I have grown to love the Access client, but I think that is mostly down to that fact that I am still not thinking right for it. Access is all about data binding. You don’t have to think about what form fields need to be copied to which DB columns, and the wizards make a really good attempt to design forms for you based on the relationship of the tables in your DB. This just all seem unnatural to me. I think this is because I am usually working with design patterns to reduce the linkage between forms and data to a minimum (e.g. the MVC pattern), and so consider this good practice; automated data binding seems wrong. So in Access, I want to build things from first principles, but this is just not sensible. Better to let the tool get you close and then you add the polish, and put away any thoughts of implementing design patterns as you would in a language such as C# or VB.NET.
I think this is the key to the degree of irritation I feel with the product, if you have got used to architecting from the ground up, especially in a Test Driven Development style, you have to turn everything on its head. It feels like you are cheating, and not doing the job properly.
But wait! Look at the benefits. A while ago I was involved in a project to provide a resource management data-driven web site that was hosted within SharePoint. It contained the usual things--data entry forms, links to SQL and reports. It took a couple of weeks to build. I think I could write the same system in Access with SharePoint 2010 in an afternoon, and would be happy to have a client’s business analyst sit next to me while I did it, in a pair programming style, to design the forms, report layouts and columns as I went along. For the smaller scale data-driven site, Access Services is a great tool, but obviously it is not perfect. I do keep hitting points where I think ‘if I were in C# I could just do that,’ but then I remember, ‘yes, but it would have taken me three days to get there, not an hour’. Most projects don’t need that last 10-20% you can only reach on .NET custom code, and the client with be far happier with 80% done quickly and flexibly rather than 95% done later. Also we have to factor in my relative lack of experience with Access as a RAD tool, reducing the productivity that could potentially be achieved by a more experienced Access developer.
Actually the bulk of the time I have spent has been on looking at how you can extend Access Services to reach that last 20% of functionality, and it not that hard. The key to remember is that the Access Services are just built on standard SharePoint objects. Ok, there is a new service running to render the pages, but underneath there are just SharePoint lists and workflow, and where these exist there are events that you can programmatically handle. I have found that by trapping events such as ItemAdd() for the Access-created SharePoint lists, there is no real limit to what you can achieve via the SharePoint Object Model. And this development process is made even easier by the new Visual Studio 2010 templates for SharePoint features. If nothing else the fact that all the templates create a WSP for deployment makes for far more robust feature development.
There is one major difference between a standard SharePoint site and one created by Access, and it is that SharePoint Designer cannot open the Access site. I thought this would be an issue when I first heard about the limitation, but it turned out not to be. Anything you might have wanted to do in SharePoint Designer you can do quicker and easier in Access for this type of data-driven site. Ok the range of things you can do is more limited, but again you get that 80% you need with much less pain.
So how has my experience with Access 2010 been? Exasperating, frustrating but undeniably productive. I am not sure it is the right product for an ISV style company who want to roll out a single solution to many client sites (but it could be used for this if needed via the SharePoint site template gallery); but for a smaller data driven site (with or without custom extensions) written within an IT department it is a very strong contender. Taking Access in many ways back to its roots.
So if you need small data-driven sites I would suggest you put aside your prejudices and have a look at the beta program for Office/SharePoint 2010. I think you will be surprised.
Updated 10/22/2009 with minor grammar edits.
Thanks for posting this Clint. Great to hear .net folks call Access RAD tool. Question: Why not allow Sharepoint Designer to open Access Web Services Sites? Sure the site might break, but so what. Wouldn't there be more advanced customizations and extensions available such as XSL parameters in data view web parts. I assume the SP lists that are created from Access can still be used to build custom pages using SPD and DVWPs, so maybe it's okay. Just thought it would be nice to leave the door open to devs advancing what can be done on a Access Web Site 'that last 20%'.
We do not need Sharepoint. All of our Customers from 2 up to 100 employees don't have Sharepoint. And they wan't have in the future. Where are the improvements for those who have no Shatepoint? There is almost noting since 1997. It's a shame.
Clint said: "..great question about price. Microsoft does provide a hosted version of SharePoint today called SharePoint Online. ...." I would prefer data in our place. Will be there a Lite version of MS Sharepoint 2010 that you can install yourself without permission from IT department? My database is being used by 8-10 PC in a FE/BE setup. Sharepoint 2010 Lite would be better for implementation even for small groups. It would be like Filemaker Server v10... Joao Santos
Horst: There are some interesting developments for purely client customers as well in Access 2010. Let me know if you'd like more info on any of these. 1. Improved Data and UI Macros
2. Data Services (connect to enterprise-style web services)
3. Web Service Expressions
4. Template parts
5. And more...
Horst Currently your customers don't have Sharepoint. But the future? Will they decide to give their customers access to their customer specific data? I can see a auto repair outfit/dealershipo allowing their customers to periodically enter in the miles/kilometers/hours and suggesting routine service items. "Time to consider changing your timing chain." Access 2010 might make this capability very simple.
Hi Toni, Hi Ric because our customers are usualy small companies (2 - 15 employees) they also have no infrastructure for web-services and the will probably have no sharepoint at all.
On the other side I speak about Access-Application with about 30 tables 100 Queries and a lot of forms and reports.
I do not need macros. All the logic I implement is with in vba. The Applications i speek from are a bit more complex then tracking kilometers.
I have writen a view COM-Objects in C# (visible and non visible) but they are difficult to deploy.
Im also an acceptable C# programmer (Winforms, Asp.net) and have writen some complex DotNet Applications for a big company. I love C#. By the way in this company the normal way to devlop software is in JEE. (Yes IBM Websphere and no Data Services) But I love Access too. And I think it's a pity that it seems that Access becomes only a sharepoint frontend and nothing more. Perhaps you can consider this in the future.
There are certanly a lot of usefull things to implement. Apart from Sharepoint.
Excuse my bad English. Grettings to all
@ Horst: ".... I think it's a pity that it seems that Access becomes only a sharepoint frontend and nothing more." I agree that this is a valid point. While many apps will be written to work with/in SharePoint/Access Services, MS needs to also keep in mind that just as many, if not more will continue to be little standalone apps that won't need or benefit from collaboration with SharePoint. J.
".... I think it's a pity that it seems that Access becomes only a sharepoint frontend and nothing more." I strongly desagree !
You steel may create mdb (with security) Accdb (local or SharePoint) and ADP (SQLServer) files ! It's not "only a sharePoint frontEnd"
It's the most usefull RAD and Database Tool, client, or wathever you want :) I love Access, and the 2010's release seems to be the best ever !
because our customers are usualy small companies (2 - 15 employees) they also have no infrastructure for web-services You mean that these companies have never heard of the internet? You mean none of them have a web site? That is very unusual. I would say that even the smallest of my clients (2 employees) still has a good web site. Of course none of them host their own web servers as that too expensive to setup. I don’t really see this SharePoint issue as any different here. SharePoint is just another web stack like Lamp that you write and deploy software to. Most smaller companies don’t purchase a server and setup their own web site. The same goes for SharePoint and hosting. SharePoint is really not any more expensive then most web hosting plans anyway. In fact office live small business been free SharePoint for about two years now. So, you don’t think it great that now you can start web development for customers using access? You been able to use ms-access with office live for about two years now. As for data macros? Well the Oracle database has table triggers and stored procedures. Microsoft sql server has table triggers and stored procedures. Now, our lovable JET (now called ACE) data engine has table triggers and stored procedures. This a game changing technology and will even change how you write and design software. You can move code logic out of forms and into stored table procedures. If you really done some c# work, then you should have a big smile on your face as now access developers for the 1st time get to experience this wonderful ability to separate UI and code from each other. After spending a few days playing with access 2010 I am now wondering how it been all these years we lived without table procedures. They change how you think and change how you design software. You become a BETTER developer when you do this. This means that long time access desktop developers for the first time now have table level triggers and stored procedures (even for desktop only applications). Remember those triggers and stored procedures run if you modify a table using VBA and a DAO recordsets. This means that code can be moved from 10 forms strewn all over the place into table logic. This great table logic feature is what every other major database platform offers to developers and now this set of keys has just been handed to ms-access. I think that receiving table triggers and stored procedures is one of the BEST NEW features for longtime developers. Is a way cool feature and will make you a better developer.
@ Albert Kallal Does the introducion of table triggers mean we can also use lookup fields in tables again ? I had always thought that lookup fields in tables were frowned upon by the developer community as degrading performance ? In fact I just inherited a big Access db and one of the first things I did was to go through all the tables removing any lookup fields....? Looking forward to hearing more about Access 2010 ! J.
>table triggers mean we can also use lookup fields in tables again ? Great question. The new triggers and lookups are very much separate issues. Triggers really does not change this lookup issue. The lookup thing was never really a performance kind of thing. It really comes down to that more experienced developers encountered some problems when trying to modify existing applications with those look up columns. If you’re building a little application and you’re editing a lot of data in tables, then you will find look up columns are extremely handy. A lot of access application developers have the view that you never want your users to directly see and edit data from the table view. So for forms etc to lookup other values you could always use the combo box wizard to quickly create a combo box which does exactly does the same thing as the look up field does in a table view. The problem with lookup columns is sometimes you get unexpected results from a lookup field. That lookup is often storing an ID but displaying a text description value from another table. In the report writer if you sort and group on the lookup field you actually get sorting and grouping by the ID value and not the expected description value. So the next thing you know is a person is now asking how to sort this data by description. The answer and solution in this case is to usually build a traditional query that joins the two tables together and then you can sort on the description. A lot of purist database developers would thus state that the user should have built a query in the first place, and not used to look up field to avoid this sort of data problem (pun intended!) in the first place. So to some real database purists those lookup fields are frowned upon and are seen as training wheels. It’s not really a performance issue, there’s a few areas of design that they can trip you up a bit, but on the other hand if they’re working well for you, then more power to the people! And that’s "more power" is what access is always been about.
Hello Yes the triggers are usefull.
But do they also work with linked ODBC tables? Hi Maxence. I do not say that Access is only a frontend to sharepoint. It's a standalone Application since Access 2.0.
That's abaout 15 years. But where are the real approvements for standalone Application since then?
Tell me what are the real big improvements in the last 15 years. Please do not say the Ribbon. In my case I don't realy need it.
XML? I can do XML even with the Scripting Host.
No great improvement. I need a Treeview, a Grid and other gui-components and not gui-components out of the box.
I need a better integration in DotNet.
@ Albert Well I would just prefer to be consistent in that I build my data validation in either my forms or my tables....Up til now I've always done this with code in my forms although the caveat with this is that all forms have to been consistent with this validation. Doing validation at the table level would centralize this. I like this idea. If you're telling me that other than the sorting issues you mentioned there are no real performance hits to lookup fields, then I will begin to reassess the manner in which I build my data validation....
>But do they also work with linked ODBC tables? Yes they do! Is not this cool! This is true JET (well ok we call it ACE now) engine level triggers. If you write a windows script and don’t even launch ms-access and modify the data, then the data triggers will run for you! So open up the database outside of ms-access using ODBC and those table triggers and table procedures run. As I said, a fantastic feature for long time developers. They will even work without ms-access installed. >XML? I can do XML even with the Scripting Host. It nice to see some xml import and export features added to access. This started in access 2003, and 2007 is even better. What is a person supposed to do when they are sent an xml file of data? Here you are complaining about lack of features and now you are suggesting that someone write scripts to import xml into ms-access? To have to write scripts and code to import a simple silly xml file into ms-access is not fun! I think it is GREAT that export and import of xml data (and including support for Schema and xsl presentations) is now part of ms-access. We had this from 2003 onwards. Access is a great Swiss army knife of a data tool and it would be unfair to tell me that xml support in access is not a great feature. XML is a great and welcome feature in a data centric tool like ms-access. >a Grid and other gui-components and not gui-components out of the box. Hum, what kind of grid are you looking for? We have a great new picture control that allows pictures to be displayed in a continues forms now (2007 feature). That means we don’t have to resort to 3rd party controls. We now finally can have cool pictures displayed in a grid (continues form). So, things like a graphic of the status of a project displayed as a “open” or closed icon is now easy. You can now show a graphic image of the country flag beside a country column. Or how about pictures of a part in a ebay type of screen? All easy now!
@Horst Hi !
Of course, if you have no interest for the improvements, there is not! :)
I need a Treeview>> If you can manage xml with the Scripting host, you may have a treeview through a form in Visual Studio, and use Ado.Net to connect to the datas... I use treeview since... pfiou ! I was so young :D
Ok, you're right, it's not "in the box", but, it's not to hard to push the "activex control" button I think. Same thing for the grid. And as Albert Kallal said, there are many ways to have grids. Funny grids... Regarding DotNet, I heard about VSTA (not VSTO !!!), but I could not have use it anyway... :s To my opinion, the best improvement would be that, finally, Access can create Setup.exe which will install stand alone applications. That would be cool! But, we are maybe on the way, with the publication of Web Access application on SharePoint Servers ! Maybe v15 ??? (hope) Other Wishes ?
- Cross join in queries
- Stored Procedures in standalone applications
- Completion in SQL
- Better Database documentation
- ... Yes, I have also many wishes :) Greetings