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.
Up to this point, I've discussed building traditional Access client-side databases and run through some of the new tools and techniques that are available. This represents a very important part of the work done in Access 2007, but it is only one part. In addition, we've done a lot of work to make it easy to build client-server databases against Windows SharePoint Services. Doing this allows users who don't have the skills or privileges to be SQL Server DBAs to still get the manageability and stability benefits of storing data on the server, while retaining the ease of use of Access. This is a significant long-term bet for Access, and the level of server functionality can be expected to continue to grow in the future. I'll be describing Access 2007's SharePoint functionality over several posts, and will start with an overview of the server itself.
SharePoint is becoming increasingly well known, but is new to many Access developers. At the top level, it is simply a set of services that runs on top of Windows Server. It is shipped on the Office schedule, but it is included with Windows Server (i.e. if you have server, you can freely download the SharePoint bits). SharePoint provides the server cornerstone to the Office System, and SharePoint functionality appears throughout the Office client apps. SharePoint is designed to be a very horizontal technology, applicable to all Office users, and not limited to large organizations. We'd expect that eventually anyone with a file server, will eventually move to SharePoint, and those without servers will be able to take advantage of services like Office Live. There is an enterprise-focused SharePoint server, called SharePoint Portal Server, that runs on top of Windows SharePoint Services (WSS) and provides additional functionality to large organizations. Access 2007 runs on both, but with a few exceptions I'll call out, all of the functionality described below works on WSS and is included in the base version of Windows Server.
Windows SharePoint Services is described more fully here, but for our purposes, we can think of WSS as being composed of two parts: a platform, and a server application built on that platform. The platform contains things like an authorization model, a set of collaboration services, and flat file database (or "list") functionality this platform is highly programmable both directly and through web services, and in 2007 will have a dedicated design tool called SharePoint Designer. The application then builds UI on top of that platform to enable a collaboration app. This app is designed to enable team collaboration out of the box, and contains lists for things like a calendar, tasks, issues, and documents. User interact with SharePoint directly through the browser for adding or editing list items, uploading documents, or even modifying the site itself.
Here's a quick walkthrough of the parts of SharePoint that are most relevant to Access (there's a lot of other stuff we won't go through here, so if you're interested in SharePoint in general, please refer to the links above). Here's a picture of a demo SharePoint site:
(Click image to enlarge)
Clicking on the Announcements title, takes you to the default view on the Announcements lists, called the "All Items" view. If the all items view doesn't meet their needs, users can build their own custom views in the browser, choosing columns, column order, setting filters, and so on. The All Items view for our demo Announcements list looks like this:
In addition to the default view, each list has a single-item view and a single-item edit form. The single item view looks like this:
And the single item form looks like this:
You'll notice above that SharePoint has support for rich text, complete with in-browser tools for formatting, as well as the ability to copy / paste from other apps.
Office 2007 users can also use the Access Datasheet to edit list items in the browser through a grid, which allows quicker editing for multiple items. The grid looks like this:
Creating a new list is also done through the browser, and the user can select from a variety of built-in list types or create a custom list. The custom list creation UI looks like this:
Adding columns is done after the shell of the list is created, through the "List Settings" UI, where you can choose data types, defaults, and so on:
Each of the lists created shows up on the "List of Lists", which prevents orphaned content:
In addition to the very basic list functionality described above, SharePoint provides a number of other features that help users. You can turn on version history for list items, allowing you to see who made each change and when, and roll back those changes if desired. In addition, deleted items land in a "Recycle Bin" and can be easily undeleted. Here's what the recycle bin looks like:
The SharePoint team collaboration app and SharePoint platform provide basic in-browser database functionality - there's a place to store data and simple forms and reports. Of course it lacks many of the things that make Access so powerful (relationships, remote data, logic) but it adds some things that are also useful to database authors (server manageability, recycle bin, versioning). In the next posts, we'll explore moving Access database to the SharePoint server and discuss building apps that take advantage of the best of the rich-client and the rich-server.
The "wish list" of Access developers who make application with this product, since 1992: - A native TreeView Control instead ActiveX or API
- ComboBoxes and ListBoxes more powerful (column resizing, icons, more than 65536 records, conditional formatting...)
- SubForms on Continuous Forms - Better image support (OK with Access 2007)
- Interactive Reports previews like Crystal Rep. (OK with Access 2007) - To be able to create, by code, new controls at RUNTIME and not only at DESIGN time.
- More than 758 controls in Forms/Reports (895 in Access >= XP)
- More than 255 columns in JET Tables and Queries.
- a ROBUST Runtime package without the need to buy the sagekey scripts for good deployment. Why, in this years, do you have lost time on "mistaken ways" (ADP, DAP, RIBBONS, etc.)? Bye
I like the extra functionality with Sharepoint. Anything that broadens the Access canvas is a good thing, in my opinion. What I don't like is the removal of developer-level features such as user-level security and replication. Both of these were gems and any weakness should have been improved rather than removed.
There is a discussion on user-level security going on at www.utteraccess.com/.../showflat.php which may be of use to you. Alan Cossey
allenbrowne.com/ser-46.html I hope this bug don't affect Access 2007...
Sorry for the slow response - I just looked back for more comments and realized that my last comment didn't make it to the site. I'd like to give some insight about how we create Access that will shed light on the questions above. One of the key characteristics of Office is that it has a LOT of users, more than 400 million worldwide. As you can imagine, those users are a very diverse bunch - in fact the sample is so large, that the average Office user isn't much different from the average person. Access doesn't have quite the breadth of useage of Word or Excel, but its user base is for most purposes the same. The best way to think of it is, its "everybody". This broad usage means that when we're designing product, we need to build it for everybody. That doesn't mean that each feature has to be useful for everyone in the world, but that the product as a whole needs to have something for the core user groups. Something for everyone, something for developers, something for IT organizations, and so on. As it turns out, we invest more for developers than the numbers might indicate we should. We do this because the development community is very important to us, and possibly because we're developers ourselves. We can't do everything developers want, but in the list of current above, you'll see that 1/3 of the issues have been addressed. We're very aware of similar lists on places like UtterAccess and have actually nailed a large number of the things developers want. Not nearly all of them, but we've made good progress and will continue to do so going forward. Sometimes the "features" we add actually take functionality away as in the case of the user level security. This was done because IT organizations are justifiably very worried about security, and have the ability to block the use of Access by the majority of our customers. The feature could not be made sufficiently secure (I know there's debate about this online, but I promise you we understand our own code pretty well). Nobody likes this (even the IT folks) as the feature was clearly useful. Our solution to issues like this is to figure out another way to provide the functionality, which we will work to do. We don't have anything for Access 12 and we feel very bad about that, but we understand how important the scenario is and will keep driving going forward. We understand the frustration you feel when we don't do everything you'd like, but do stuff that isn't important to you. The reason is generally that we're doing the work to meet the needs of a different part of the Access user community. The only other reason is that we've goofed and built something that nobody wants. And trust me, we hate that much worse than you do. Thanks, Erik
I've some Access applications with a "main" Menu Bar AND a ToolBar with the more common options. How I make to obtain the same one (or similar), with Access 2007? Thanks
Erik Rucker: "One of the key characteristics of Office is that it has a LOT of users, more than 400 million worldwide. As you can imagine, those users are a very diverse bunch - in fact the sample is so large, that the average Office user isn't much different from the average person" Me: Ok: MS Office suite is a lot used in the world ... but DB-developers like us know very well final customers needs. We know that customers, in general, don't use Access seriously: they don't know what is a relational DB, and they prefer to create large tables in Excel. Microsoft Access, since 1994, offers: - Internal relational db-engine
- ODBC connectivity to SQL Server engines
- Rich Forms and Reports
- ActiveX support (with some problems)
- Access/Visual Basic code and win API support
- easy Menus and toolbars Obviously this is a product for DB developers that can ALSO be used from (good) final users, and not "vice-versa"...
There aren't real differences in the Target (and power) between Access and visual Fox-Pro. On the other hand, this blog is attended only from developers, Erik. Bye
Well it is now clear that Access is finished for developers. The breaking of the guaranteed access rule is a fundamental error, and just to add interoperability with SharePoint. We shall obviously have a look at the next version but Access 2007 is really not a serious developer platform at all.
Isaac - If you've replaced all of the Access UI in 2003 (or before) your menus & toolbars will come through just as they have. If you've added to the existing menus / toolbars, they'll show up on the add-ins column. In either case, you'll be able to rebuild them in the new UI using the new ribbon controls as I showed a few posts ago. Craig - Access will still ship a runtime, just as it has for years, and you'll be able to deploy your solutions to users who don't have Access 2007 (or any version) just as you could before. The only thing you can't do is create VB programs that don't use Access for users without 2007 because the new engine isn't part of the OS. The existing engine is part of the OS and you can still program against it using VB if you'd like. In that case, you're not using Access at all. Zen - I couldn't agree more that developers do a better job than other users of building databases, I was simply explaining that many others build databases as well and that we can't ignore those users.
Erik: In either case, you'll be able to rebuild them in the new UI using the new ribbon controls as I showed a few posts ago. Thanks for the answer Erik. In your post on "how to customize Ribbons" it isn't clear if it's possibile to make a Ribbon that contains an "imitation" of Menu-Bar with custom icons, and below buttons Toolbar style. Bye
Erik thanks for your reply, however I was in no doubt that a Runtime would be available. What I was concerned with is that the tables created when an MVF is employed are not exposed to SQL or the Relationships Window. Code can be used but that is not set based like SQL. I would tend to beat a programmer with a pointy stick if he used code when SQL could have done a better faster job. Obviously all professional developers (who understand relational database theory and design) would avoid using the backward step of the MVF. Until this is properly implemented, which I believe being planned for the next release, it should be avoided as this breaks the guaranteed access rule. BTW Codd's 2nd rule is called the guaranteed access rule "each and every datum (atomic value) in a relational database is guaranteed to be accessible by resorting to a combination of table name, primary key value and column name". That is no longer possible with an MVF in an ACE database and SQL stops working the way it is supposed to.
Erik I always thought that referential integrity was key to database design. Can you give us a clear explanation of the ramifications for referential integrity if the database is moved to Sharepoint. The following quote from the Microsoft Access 2007 helpfiles is alarming. "SharePoint sites do not support referential integrity"
Does this mean that data entered into Sharepoint lists will compromise the referential integrity of the database?
Foollowing that thread may be you could help me in an issue with WSSv3 and Microsoft Access 2003. Basically what I need to do is just to see an access form in a WSSv3 site (just in a similar way we can see Infopath forms in WSSv3), I have been reviewing the different options for doing it directly in the design view of WSS or by means of Sharepoint Designer....after some time I haven´t been ablee to do it...the Best regards My e-mail is firstname.lastname@example.org
Juan Carlos - sorry, there isn't really any way to do that in Access 2007. You'll need to create a browser form using SharePoint Designer to be able to edit directly in the browser. If your users will have Access 2007, you can put an Access database on the site and users can link to that, then use the client-side form. Accessible - yes, RI is an important tool for database design and it is not directly supported on SharePoint v3. (You can impliment using managed code event handlers, but this is hard and well outside the scope of Access usage.) There are, however, a great many valuable database solutions that don't require RI. If your solution does require RI, SQL Server is a better back-end than SharePoint at this point. Craig - ah, it wasn't clear you were talking about multi-value fields. It is certainly not required that anyone use them, so by all means go ahead and build the data structures explicitly if you want. As to the accessibility of the data, the MV fields are of course accessible as a collection from the result set. Thanks, Erik
Early in the comments there was a back and forth about who uses Access, with Eric handing off 400 millions of users and zen saying it's just us devs using Access 'seriously'. Eric was careful to say 40 millon Office users, not 40 million Access users. I only point this out because it obvious that Eric's marching orders re Access 2007 have to do with Access-enabling more of the 400 million Office users. That's a pretty ok goal I'd say. But as a developer, I am very concerned that Access is being diverted from it's strange role as the most broken, most neglected, and yet still most efficient, data smart development tool out there. It's helping to wean me from my career-long Microsoft develoment tool orientation. The 'diversion' is happening because even though MS Access is a massively popular dev tool, it's a part of Office, the audience for which is much larger than the dev group...so we don't get the TLC that a standalone db dev tool would have had. Instead we get an incredible intial offering, then ten years of stagnation, then...whatever the new Access is going to be.