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.
Tips
How-to
News
Videos
Stories
In the last post, I described how you can publish an Access database to SharePoint. This time, we'll look at taking that database back offline, making data changes while disconnected, and re-synching. I'll also briefly discuss updating the thin client version of the application using SharePoint Designer.
When the database is published to SharePoint, it moves the data into WSS lists and the database front-end into a document library as described in the last post. Opening the app is as simple as going to the document library and double-clicking:
After double-clicking on the app, you'll be prompted to open read-only or for edit, and since we're making design changes, we'll choose edit.
This causes a local copy to be saved to your machine, so the SaveAs dialog is brought up and you can simply choose a location for the database.
(Click image to enlarge)
The database remembers where it came from in SharePoint, so it is easy to publish changes back to the server by clicking the "Publish to SharePoint Site" button.
Taking the application offline, is available with one click from the External Data tab.
Since we've already saved a local copy, we're not prompted to do that again. When I click "Work Offline" the data from the web is brought down to the local Access file and cached in local tables. The links to the SharePoint tables are cut, but of course are remembered for resynch.
Now you're free to update the local data while offline, and the application behaves much like it did online.
While the application is offline, of course other users may update data on SharePoint. They might do this through the browser or through the SharePoint web UI. Here they're using the Access Grid control:
When you return to the network and want to resynchronize changes, it is again one click:
Now however, there are some data conflicts, so you're presented with conflict resolution UI.
After resolving any data conflicts, the application is back online, and all changes are made directly against the linked tables.
SharePoint Designer is a new product that makes it easy to design SharePoint web applications. You can use it to build a thin client that provides a similar experience to the Access thick client UI. The first step is to open the SharePoint site in SPD, then either start a new page or open one that you'd like to customize. In this case we'll customize the All Items view of the Issues list:
Then we'll convert the view from a SharePoint List View to a "Data View", which allows extensive customization through XSLT. This is done from the right-click menu in SPD:
Now the data view can be edited directly, and updating the columns and formatting is much like a grid view in Access:
SPD has a data source task pane that is quite similar to the Add Existing Field taskpane in Access:
SharePoint Designer provides a rich set of tools for working with both the data and its presentation. It is based on web technologies, so writing code is quite different, but the general concept and much of the UI is very similar. For more information on SharePoint Designer, please see Alex Malek's blog and the Office SPD page.
In the next post, I'll discuss building workflows in SharePoint Designer and using those in Access. After that, we'll move back to more Access client app developer focused topics.
Comments: (59) Collapse
I've got extensive and long-standing experience with Jet replication and am interested in the capabilities of A2K7 and Sharepoint to offer some of the features of Jet replication. But how can anyone seriously present that conflict resolver graphic as a serious attempt to allow an end user to fix it? Why in the world is the long integer value for 9/27/2006 given for the date instead of a date-formatted value? How could a user know what that date value means? Has anyone given this any serious testing? That is, is this a serious effort or not?
Rich, The SharePoint offline *lists* are in no way similar to JET *database* replication. Among other things I have made lots of money in building and supporting Access applications over the last 10 years or so. I know Access inside and out. I also have done extensive work with SharePoint v1 & v2. I’ve been studying Access 2007. Basically, I know what I’m talking about. The SharePoint to Access offline lists are a pathetic joke! How M$ can even call a SharePoint *list* a *database* table is beyond me – really, it must be driven by some weird marketing thing around SharePoint. It is an absolute kluge. This offline SharePoint list thing is for a few items and a few users max. Anything beyond that and it will completely fall apart and be unmanageable. Windows 95 briefcase replication had more engineering by far than this offline list abomination. JET replication was engineered for database replication. JET replication worked, it was programmable, manageable, robust, useful, had a graphical manager, worked with many topologies, did partials, had scheduling, had logging, worked over the Internet; in short it was engineered for real databases and real applications. Now, since M$ is clearly putting Access out to pasture, they don’t care, M$ ripped JET replication out of Access07 because they don’t want to support it any more. The writing is on the wall. There in no future in Access. VB/VBA is dead, dead, dead. M$ is going to string Access along in maintenance mode only. Ripping out things that are too complicated to support and playing with the rest. M$ obviously is not advancing Access. Access is fine for existing applications and will continue for a long time in this role. But as a real development platform, it’s dead. No new applications should be built with it. Access 2007 makes this very clear. A SharePoint “list”, or pseudo table has only a single non-controllable incrementing ID – usually not shown. There are no other unique constraints. It can not be called a database table. “is this a serious effort or not?” No, SharePoint offline lists are not a serious tool for real database applications or any serious application, for that matter. Mike
Whoops, I ment to address that to David W. Fenton, sorry David.
Well said Mike. I agree completely, but with much sadness. My favorite devlopment tool, may you RIP.
StepUp, I am very sad too. It really really is a shame that M$ is not putting any real effort in advancing Access, THE single most successful development platform for creating database applications. I am well experience in building ASP.net v1/2 database applications. I know the limitations and clunkiness of ASP websites and the richness that is a Windows app. I know the effort it takes to build a VB6 and now a Winforms application. Access trumps them with richness and simplicity of development, thus low cost. But, I can not honestly recommend Access any more. I have committed to C# and the VS changing menagerie, but Ruby on Rails is taking my attention, it is very cool. Ruby on Rails is open source and therefore is not at the whim of the arrogant bobos at M$. M$, please spin Access off to an independent company! Let it live! Mike
Is it not the case that Access 2007 will continue to support regular Jet in MDB files, thus enabling the continued use of traditional Jet replication? In other words, if you open a Jet 4 MDB in A2K7, will you have the Replication item on the Tools menu (or whatever corresponds to it in the ribbon UI)?
David, Yes, if you open an MDB, or create an 03 MDB file you will have replication in Access 2007. But, for me at least, it is not usual to work with miss-matched versions of programs and data files unless I am migrating between versions, especially with customer’s production data. It’s hard to know if MDB replication is supported in Access 2007 either. M$ is not supporting replication in Access 2007 native data files (accdb). JET Replication support ends:
Access 2003 1/09 (main stream), 1/14 (extended support)
Access 2000 1/04 (main stream), 7/09 (extended support) Access 2003 has two good more years, then it is toast, as they say. As a side note, it is interesting that M$ is tenderly stepping into longer file name extensions. Why not just use Access2007db as the file extension? “accdb” is still cryptic. Jeez, virtually every other OS uses intelligent file names. When are ancient DOS v1.0 drive letters going away? Not in Vista... Mike
I am concerned for the sanity of the people at M$! I have been doing tests with SharePoint and Access 12… I created a table called “Table1” with two columns: TestNumber(autonumber) and TestName(text). I did the complete publish to SharePoint v2, including moving a copy of the Datebase1.accdb file to the SharePoint “Shared Documents” doc lib. The “Moved to ShrePoint Site Issues” has some good news for me: TestNumber(autonumber, PK) Field values will not be auto-generated; SharePoint only supports AutoNumber of their ID field. TestNane(text with unique index) SharePoint does not support unique indexes on any column other than ID; Unique index will not be enforced. Hmmm. Yup, adding duplicates data to the SP list works; adding duplicates to the Access “table” works – never heard about Codd’s work at M$ I guess... Ok, maybe someone somewhere will find a use for this. Not me. Ok, Ok, Ok, lets try creating another table “tblUser” with UserNumber(auotnumber, pk) , UserName(text, unique), UserType, UserCode, UserStatus, UserNotes. And try the “Move to SharePoint” thing again. Oh excellent, Access 2007 and SharePoint DUPLICATES my Table1 into Table1_1 and moves my tblUser to SharePoint. And more of the same, Access07 throws ways the unique index, etc. Hmmm. SharePoint now has Table1, Table1_1 and tlbUser. (By the way I am leaving out a fun thing that Access 2007 does to the now linked pseudo Access tables, it adds an _ID(float), Attachments(ntext), Modified(datetime), Created(datetime), [Created By](datetime) and [Modified By](datetime) and then creates a link to the SharePoint UserInfo table where ALL users of the SharePoint site are listed.) Anyway, lets create another table, and try that Move to SharePoint. Yup, now we have Table1, Table1_1, Table_2, tblUser, tblUser_1, and my new pseudo table tblTest. Presumably there is no easy way for M$ to make this “moving” thing to work cleanly so, users will just need to delete a lot of SharePoint lists. Oh, wasn’t that deleted list the one you used FrontPage (now called SP designer) to customize for a hour or so? No problem, you will just need to make all of those XLT SPD customizations again! And again! And Again! Ha ha ha, I hear screaming coming from the next office... Mike
MikeC#: M$, please spin Access off to an independent company! Let it live! Me: Which is your Access "wish list"? Bye
MikeC# wrote:
> Yes, if you open an MDB, or > create an 03 MDB file you will
> have replication in Access > 2007. Hold on. In A2K2 and A2K3, your default MDB file format is A2K format. Is that going to change in A2K7? Can A2K7 create a new MDB file? Can it create it in A2K format? Can it create it in A2K2 or A2K3 format? > But, for me at least, it is not > usual to work with > miss-matched versions of > programs and data files > unless I am migrating > between versions, especially > with customer’s production > data. Does that mean you only use A2K2 format in A2K2 and A2K3 format in A2K3? Me, well, I use A2K format in all of them, because the later versions add nothing at all that I need for my clients' apps, and so I get cross-version compatibility. I would expect A2K7's replication support to be similar -- if you create an MDB, it will allow replication, because MDB is a JET format and replication is a JET technology. If you use the new Access file format, you're no longer using pure Jet, so replication is not going to be offered. I don't have a problem with that. Jet replication has never worked reliably for anything but pure Jet data in the first place (i.e., tables and queries; that is, no forms/reports/etc. should ever be replicated), so it's not going to be an issue to be limited to what only classic Jet can do, since that's all Jet replication ever offered in reality. > It’s hard to know if MDB > replication is supported in > Access 2007 either. Well, surely someone with the beta could test it and find out, no? > M$ is not supporting > replication in Access 2007 > native data files (accdb). Yes, of course. And I don't care, as long as replication in Jet MDBs is still supported.
As David notes, Jet replication continues to work for Access 2007 just as it always did in mdb's. Further, 2007 reads / writes 2003 (actually 2000 - 2003) file format just fine without updating it. So if you'd like to use replication, simply use a 2000-2003 mdb and you're cooking.
David, Access 2007 Beta2TR can create these files: 2000 MDB format
2002-2003 MDB format
2007 ACCDB format (default, but changeable)
Access Project ADP format (binary differences from Access 2003 ADP files...) Other combinations work fine, but because of simple compatibly, functional and update reasons, for production use I recommend that: Access 2000 MDB/E files work best with Access 2000.
Access 02-03 MDB/E files work best with Access 2003.
Access 2007 ACCDB/E files work only with Access 2007.
Access ADP/E files and SQL Server 2000 work best with Access 2003
Access ADP/E files and SQL Server 2005 work best with Access 2007 Note:
Access 2003 was the final version that Microsoft was committed to MDB files.
Access 2003 was the final version that Microsoft was committed to ADP files.
Because of the above, it is questionable in using Access 2007 with anything other than an ACCDB file format, final judgement pending... I’ve been testing Access 2007 Beta2TR a few different ways. On thing that is apparent is that Access 2007 is not working well with charts (I’m having hangs/crashes when this code is run). Another is when a bullet proof Access 2003 24/7 production application ADP file is run under Access 2007 the top window title bar gets lost, and flickers in and out of existence when forms are opened (the whole screen jumps up and down with two status bars). The application has one custom menu and one custom tool bar – something to with the hidden ribbon? Access Projects (ADP) information is virtually non-existent in the help file. Anyway, more on this testing later. I get no response from M$ smiles, maybe I will just post the bug list here… Makes me want to give up completely and say that Access 2007 is not even good for existing apps… Zen, give me a little time, I will post a coherent wish list. Mike
There in no future in Access. Microsoft is going to string Access along in maintenance mode only. Ripping out things that are too complicated to support and playing with the rest.
I am going to use FileMaker instead. SQL Express/VB.NET does not offer good alternative to my works. Microsoft is too big and arrogant and thinks Access has no value.
It's really sad, but I have to agree. Many of the most important Access developer features are being subjected to a slow death. Many of the features that really distinguished Access from its competiton are now deprecated. A whole generation of apps that relied on those features (e.g. replication, security) will now have to be rewritten at great expense in some other tool. The Access team in charge of developing the feature list for this new version should all be cited for gross incompetence. I mean, who cares about Ribbons and SharePoint??? What kind of market research did they do? Wasn't it obvious from all the public blog and list postings what features we needed??? Did ANYBODY ask for SharePoint??? Instead they give us a whole load of useless half-baked "features." What an incredible waste of effort!!! What an incredibly stupid decision!!! These new useless additions are responsible for sucking up development of much more important features (e.g. security, replication, ADP enhancements, VB IDE improvements, .NET integration...) Ribbons be gone. SharePoint be gone. Give us a real development tool. This is not a proud moment for the Access team. Of all the Office apps, Access is the only one that is less useful in the new version. If I were you guys, I would hold off on this version until you can get it right.
> Access 2003 was the final
> version that Microsoft was
> committed to MDB files.
> > Access 2003 was the final
> committed to ADP files.
> > Because of the above, it is
> questionable in using Access
> 2007 with anything other
> than an ACCDB file format,
> final judgement pending... After the ADO/DAO debacle, I'm skeptical of any such "final" versions of whatever new technology Microsoft is promoting for Access. I realized right away that ADO made no sense for my A2K apps, as I wasn't developing for anything but Jet back ends. And then classic ADO gets abandoned entirely a few years later. If you need replication, then you will have to use an MDB in one of the earlier versions, as the new file format offers nothing approaching replication (Sharepoint is clearly not going to be able replace replication except for the most trivial data schemas). I still do about half my development in A97, and none of my development in anything newer than A2K, because my clients have to support desktops with A2K, A2K2 and A2K3 installed. This works out really well for the clients as they no longer have to worry what post-A2K version of Access is installed, since all of them work with the A2K apps they have. I just don't any pressure to move to the new data format because I really don't need any of the things it offers. It may be that, as with ADO, all the official documentation for a time will be written in terms of the the new database format, but, well, I got along with the ADO support files, so I can probably get along with documentation for the new database format.
Comments: (loading) Collapse