Microsoft Access Team Blog

The example quoted shows that referential integrity and (some?) validation rules do not get included in the Sharepoint version of the database. Do you have any info on how to overcome these problems, e.g. is there any way of setting up referential integrity and validation rules in Sharepoint? Validation rules could conceivably be handled in the Access front end itself, but losing referential integrity sounds serious.

Many thanks.

I thought that it was already mentioned that referential integrity was not supported for Sharepoint.

In any case, at this point I have given up on Access 2007. First of all, I'm quite discouraged that there hasn't been much of a response of late by developers on this blog. Maybe that is because they have given up as well. As I looked back at some of the early posts on the blog, there were a lot of comments from developers, especially about the ULS issue. But I guess it became clear that Microsoft was not going to budge on the issue, nor apparently are they going to budge on any of the many concerns expressed by developers on this forum. The only reason I would use this product is if a customer standardized on Office 2007, or was using Sharepoint. I certainly will NOT be recommending to my customers that they upgrade to Access 2007.

I find it interesting that the project managers have stated that developers were an important part of the early feedback received on the product. I'd like to know who these "developers" were, because it has been my distinct impression that there have been large doses of complaints and concerns (in addition to some well deserved compliments) from developers in this forum.

What continues to baffle me about this whole situation is that lack of flexibility in the new version. I wonder why OPTIONS are not afforded. The OPTION to use menus and toolbars instead of ribbons, the OPTION to use ULS (along with, of course, beefing up password encryption) or trust center, the OPTION to still use replication, the OPTION to have better ADP support.

I have to add, that even as I use other Office products with the Ribbons, I am liking them less and less. I think Microsoft is going to be in for a big surprise when the product is released and they see what the reaction to it is (most especially with no GUI tools to configure the ribbons and how they behave, which I must say I am completely astounded by).

I think its both and exciting and sad time for Access, and sad especially for Access developers. Just read Mike Groh's Access Advisor article on Access 2007. You can see he is trying to be encouraging and enthusiastic, but his disappointment comes through pretty strongly.

I certainly join him and the other developers here in that sentiment.

Hi !

I like the new design of the forms but nothing else has been done for devleopers. I'm going further on switching to C# using a good code generation tool.

Thomas

Although I don't share all of StepUP's sentiments, I am surprised that significant features of any database system have been removed from Access. Of course, I'm mostly referring to user level security. I have found that the Access 2000-2003 MDB format provides ULS in Access 2007, but it sort of negates the purpose of upgrading to 2007 if you have to stick with the older format just to get ULS in applications.

It'd seem there'd be some way to provide a reasonable level of security in the ACCDB format -- after all, if the ACCDE format makes objects read-only, why can't the same mechanism be selectively applied to ACCDB

My bottom line is that (today) I see much more in 2007 that's good than what's bad. The ACCDE format provides most of what I'd use ULS for, in any case, and I get most of the benefits of the new 2007 features.

In those cases where ULS is a requirement, we'll have to use the MDB format, or provide an ACCDE linked to a password-protected back-end. These work-arounds still won't have the flexibility of ULS, but, for most situations, I think they'll do.

SharePoint integration is very, very good. It's very cool to seamlessly access remote data in an Access form or report. Other than, perhaps, a tiny delay while the data is pulled from SharePoint, the user isn't even aware the data resides in Timbuktu. SharePoint integration is a terrific addition to Access 2007.

OTOH, I'm not sure how many Access apps really need integration with Timbuktu data --- after all, it's my understanding that the majority of Access apps are still single-user, or workgroup databases working off of a local file server.

The good news in SharePoint integration is that, at the very least, the possibility of tremendous upsizing and data sharing.

Erik: BTW, you guys really need to get the word out about using SQL Sever 2005 Express as an alternate database engine for Access. I am absolutely thrilled with Express --- the new developer studio is so easy to use, a caveman could do it ("I'll have the roast duck with mango salsa"). And, Access 2007's integration with SQL Server Express (or SQL Server Standard/Enterprise) is, quite frankly, dang good.

Supporting StepUP's complaints about ULS, utilizing SQL Sever (in any form) is more than a corporate/workgroup developer should have to consider just to add data security to desktop databases. IMO, as a database system, Access should natively provide full protection for its data and design.

However, from the perspective of performance and "upsizeability", in addition to really strong security, SQL Server (Express, Standard, or Enterprise) is the way to go. And, once you've bit the bullet and installed SS, the majority of issues with the loss of ULS simply disappears. Users couldn't care less what database engine is servicing their applications -- they just want to see their data. Using some form of SQL Server provides the opportunity to share "Access" data with web sites, .NET, SharePoint, Oracle --- you name it. And, Access developers continue working in a familiar, comfortble, and productive environment. That's a good story, and you really need to get it out there.

That's my 3 cents for the day! Thanks for reading!

Mike and StepUp,
I too have been really concerned about security in the new file format. Just giving up on ULS was, to me, an incomprehensible decision. However, if you have not had a look at it yet, please do look at a thread on UtterAccess, i.e. http://www.utteraccess.com/forums/showflat.php?Cat=&Board=81&Number=1242310&fpart=. A couple of us have been working on trying to do something with database passwords and we think we have got somewhere. For example, we believe we have managed to stop users from getting at data directly in tables and queries as well as restricting users to only opening forms and reports as decided by the developer. This method we have called vPPC (Virtual Password Protected Connection). So far we have left the method of determining which user can open which form or report to the designer as different systems may need different methods of allowing this, e.g. one may require setting up users in a table in Access while another may run off a user's ID in Active Directory or whatever.
Our main problem is that there only appears to be one other person who has had a real look at what we have done despite over 2000 views of the thread! We may be barking up the wrong tree, but it would be really, really good if some other people were to have a proper look at what we have done and give us their verdict. A security system checked out by a grand total of 3 people is unlikely to have found all its problems.

Since the thread referred to above is rather long, you might want to start at the end and work backwards.

Oh, and on the subject of ribbons in Access 2007, I have found them fairly unintuitive so far. That may just be me though.

Alan,

I have been following the thread on Utteraccess about your security architecture and it looks VERY interesting from what I can understand of it so far.

I'll admit that I haven't really kicked the tires with it as of yet...I've been working on a .Net project with an October deadline so time is tight right now. I guess another factor is every time I bring up Access 2007 and see what I consider to be a cumbersome and unattractive GUI (I can't even create a classic Windows style form and of course I can't create a standard menu) I find it hard to get motivated to work with the program.

Anyway I'm glad to see some people are working on a solution. But as you have pointed out, the fact that so few people seem interested makes me think that many developers have already abandoned Access 2007.

Mike, I have tinkered with SQL Express (just in .Net, haven't tried it yet in A2007) and I agree, its pretty cool stuff. It DOES have a pretty heavy memory footprint though, and maybe that is not such a big deal these days, but I still work with some customers who have older machines and I wonder about performance in that kind of situation. I know you are an advocate of unbound forms (I personally love bound forms...its what makes Access RAD), but have you tried using SE with bound forms?

When uploading tables or changing or adding data will we be able to use a secure connection i.e: https?

Will we be able to loop through linked SharePoint tables (lists) using VBA from the client?

I installed the office 2007 professional plus beta2... and it's very much interesting and having more featured suits to the requirments and needs of today and matching to current working enviorment.

In the Ms Access 2007, I did not find the workgroup manager (former version call Workgroup administrator) where you can joing to secured workgroup (mdw).

Aprreciate if you can help me on this.

Regards
Saman0515@msn.com

Ten thousand Access developers could stand in front of the Microsoft Access Group Product Manager and explain that ribbons won’t work with their applications, and two minutes later he will have forgotten.

The M$ Access Group Product Manager does not primarily work to make Access better for customers.  He works to make Access better for the internal memes of M$.  Of course M$ can’t see this and will state that they are doing what’s best for the customer and thus profits.

The internal memes of M$ have dictated ribbons, and to allow users the option to remove them and use toolbars/menus, would expose the fallacy that ribbons are THE way and not just one way.  Another way to say this is: “The Emperor, M$ has spoken.”

So, Access07 requires a screen bigger than 1024x768 because of the ribbons.
99% of all Access03-97 applications are written for 1024x768 screens or less.
Therefore 99% of all Access03-97 can not be upgraded to Access07 unless the users minimun screen size is changed to 1280x1024 or someting bigger.

Why is it that the memes at M$ want a huge chunk of screen space reserved for the Microsoft “feature/ad tabs” – I mean ribbons?  Because of Office v13.  M$ memes see this as a link – a way to join the web based Office 13 pieces to the PC installed pieces, and a nice place for ads.

Access is dying because it is not reincarnated dotnet and thus perceived to be old and second class.  M$ people aren’t concerned with old and second class stuff.  A spit of minimal changes with focus on future Live$ pie in the sky, should keep the money flowing from the idiot second-class Access developers.

It’s very clear - M$ is muddling around with Access and not looking to make it better.  Linking to SharePoint “lists” or pseudo tables is the most important thing in Access v12 and not core functionality like SQL services because Access v13 will run with this web model further, creating links to the M$ Live$ database service.  Multi-valued fields, rich text and attachments were included because they are part of SharePoint, nothing more.

So what did the developers get in Access07?  A working mouse wheel in the VB editor.

Even the added calendar popup date control was not included for developers, since it can not be fixed to stay in place (as any developer would instantly see the need for).  Right now users need two clicks to use it – first click on the date field then the calendar button appears, then a click on the button.  So much for the new “non-hidden” easier to use user interface bullsh*t.

It would be best if M$ spun off Access to a separate company.  A separate company would at least care for Access and listen to customers about what improvements are important.  But since M$ has inward focused managers, this won’t happen.  It’s the beginning of the end for Access.

I suggest you look at Ruby on Rails as an alternative to the M$ Access muddle.

5 d) Create a query to show the name of the company whose price changed most during the month of July and whose headquarters are in New York. (Hint: Use the difference between the opening price in the first week of July and the closing price in the last week of July, if July is not included in your data, please use another month.)

I have trouble creating this query, could you please help me! In SQL view!! Thank you very much!!

Your blog site requires a logon for photos!

Can we modify the "Office File Menu" - the big round button?

If not, this will completely stop us from EVER going to Access 2007.

August 1 post: SharePoint

September 1 post: SharePoint

October ? post: SharePoint

This no longer feels like an Access blog but marketing for SharePoint. Can you get to other topics for applications that already exist that will need to be transitioned to 2007 when clients/corporations move to Office 2007 instead of talking about stuff that does not exist and frankly, I don't see you getting much support for? Detail of runtime / distribution packaging would be nice. How about changes to DAO? What are the new object model properties, functions, methods. Is there anything being introduced at the application level events ... how about application_object_open events? What's up with security and I don't mean ULS, more like what do I need to do in a corporate environment to make Access runtime apps open and run code? How come I have to open a query before I can switch it to design mode? How come if I open a query in design then a form in design that if I then close the form straight away I get the home ribbon instead of the query ribbon?

Mike,  you are so right.  Actually I now understand that MS wants to use its ribbon/button scheme to force all developers off Access and onto Visual Basic Express 2005 (touted for hobbyists!).  Only in VB can you still easily control the menu structure and toolbar design.  There is no choice, no option, MS in its infinite wisdom has determined what Access applications must look like, even if such requirements contradict its previously published guidelines for VB user interface design.  It's OK to say the emporer has no clothes.  Question for you: if they spent so much time and money in research in developing this new interface, why is that SAP, who spends mega-millions with its design guild/simplication group to make extremely complex applications easy to work with -- why is it that SAP has rejected this approach.  All I can say is that if you use Navision or Great Plains you should be trembling that MS will port this brilliant new approach to those application products.  If they do, I predict users will abandon them in droves, and SAP offers a "safe passage" program especially for such situations.  

As I've said before, this is the New "Coke" -- it seemed like such a good idea to its creators, but never screw with things that your customers love; they'll make you pay for it.  Gee, I wonder what the relaunch of Classic Coke set back our favorite Atlanta beverage firm?

Do you know how data will be stored in SharePoint (SP) if you use SP as an Access data store?  

In SP there is only ONE table that your data will be stored in.  That’s right, if your Access program has 5 tables (or a hundred tables) stored in SharePoint, then data from the 5 (or 100) tables is intermingled into ONE table.  The data table in SP is called UserData.

UserData is predefined by M$ to have 201 columns: 64 nvarchar(255), 16 int, 32 float, 16 datatime, 16 bit, 1 guid, 32 ntext, and 8 sql_variant (plus 16 non-user SP internal use columns).

So if you define a table in Access that has one integer column, and one varchar(10) column and store it in SP, the table really has 201 columns (but in this case only two columns will be used for your data).

There a few house keeping tables that SP uses, one is called Lists.  Lists is where your column names are stored.  So there is a map between your column names and the predefined SP columns names of UserData.  Every time your data is read the map also needs to be read so that SP can send the data to Access with the correct column names.  

The real columns names of UserData are (you guessed it): nvarchar1, nvarchar2 – nvarcahr64, int1 – int16, float1- float32, datatime1 – datatime16, bit1 – bit16, guid1, ntext1 – ntext32, sql_variant1 – sql_variant8.

The rows of your table will be intermixed with rows from all other tables and all SP “lists”.  I’m not making this up!

Wow, all I can say is WOW WHAT A CLUGE!  It is boggling to even try to think of the performance and interaction problems that can arise from such an outright wacky scheme.

If you want to use SP with Access, there should be a big bold warning:

WARNING, STORING ACCESS DATA IN SHAREPOINT WACKY, IF YOU REALLY WANT TO DO THIS, FIRST GO TO THE PHYSIATRIST TO CONFIRM THAT YOU ARE CRAZY.  THEN IF YOU ARE CERTIFIED CRAZY, ITS OK, YOU CAN MAKE IT WORK, JUST BE SURE THAT YOU DON’T STORE MORE THAN A FEW ROWS OF SIMPLE DATA AND FOR BEST PERFORMANCE DON’T ALLOW THE SHAREPOINT SERVER TO BE USED FOR ANYTHING OTHER THAN YOUR JUMBLED UP PSEUDO TABLES.

Note, if you have virtual arrays of octal-hyper 100Ghz processors with 100Gs of memory (like the M$ Access team) you may find that storing Access data in the SharePoint pseudo tables may actually work during testing.  Use real data on real systems at your own risk.

Using SharePoint for Access data storage will be as useful as Microsoft Bob.

assuming Mike has the details correct ... I will only say that I considered this kind of thing for User Defined Fields in an Access app once and rejected it. To think that the power of WSS has been degraded to this sort of nonsense is sad.

Stevbe, SharePoint gets worse!

When you store data in SharePoint there is no primary key other than the artificial numeric ID, and there is no unique constraints (Unique indexes) for the natural key.  Thus there no way to prevent duplicate data!

Further, in SharePoint v1 and v2 (and likely v3) when you use the website to create a "list" item (enter data into a table), users can multi-click the Save button creating duplicates.

Now we all know how consistent M$ has been with the single-click / double-click UI idiotism. So, users are always real clear as to when to single-click and when to double-click - not.

Really, its quite shocking that M$ pushes SharePoint as an "Enterprise" solution when a well known and simple to correct web application problem like users multi-clicking on a Save button has never been corrected.

Microsoft calls WSS an enterprise product.  What crap - if users can simply and accidentally create many duplicates of an item, then in this respect WSS is like a HTMLv1 Joe website designer product, not ready for prime time.

Sorry I've been absent for a bit (both for posts and comment replies).  There has been quite a discussion here about SharePoint, Access's future as a development tool, and the ribbon.  Since we've already been through these issues all separately in the comments, the newsgroups, and on other forums, I won't restate all the arguments.  Suffice it to say that we believe we've made changes to the app that make Access a better tool for all users and revitalize the product by expanding the number of users who can use it.  I understand that not everyone buys our reasoning, and that's fine.  We believe the tool is still better for all users - even if the arguments about "MS should have feature A and they built feature B" are true, in our research, the breadth of new tools make developers more productive and end users more successful, full stop.  If you don't agree, you have my appologies and can rest in the knowledge that we're planning another big release and are looking at a tremendous amount of innovative work for that one as well.

As far as the content of the posts, I'm still following the outline I laid out at the beginning of the year.  That means that there are a couple more SharePoint posts to come, then we'll move on to other things.  My plan is to cover upsize to SQL or Access backend, Access macros and disabled VBA, the developer extensions, and upgradig to Access 2007.  If there are other topics you'd like to see, please post or send me mail, and I'll try to get to them.

  Thanks,

      Erik

Mike: WSS is not the same thing as SharePoint, that would be like saying that Jet and Access are the same thing. WSS is just a data store, nothing more, nothing less, I was programming with it back before it was RTM'd with Exchange 2000. Yes, Noticed that the "Lists" feature is never referred to as Tables and in no way has been stated as being relational. I am with you on the idea that both those idea are implied because they are talking about the capability of using it as a data store for Access and it would have been nice to have the details up front. I am always trying to keep in mind that like Eric posted, they have made up their minds on the functionality being introduced or removed. Period. I also try to keep in mind that  this is still in beta so in some cases they have not had time or even the final features completely set so may be unable to provide details.

Eric: Sorry if I sounded confrontational, I really do appreciate the information you have been providing us even if I don't always agree with the decisions that have been made.

I hope this experiment in continual development blogs does not die because it seems like all you get is flack and the onerous spam posts " great site ... meet me at the poker table". Which leads to an off topic question ... can you folks block post backs that have the words "poker", "alice", or "zoomer" in them? that would trim the auto-email by about 50%.

Thanks again,

Steve

I think for my current employment, 2007 will not affect me much other than working out the detail of the ribbon which don't seem to really be that difficult, just a bit more time consuming.

"WSS is not the same thing as SharePoint, that would be like saying that Jet and Access are the same thing. WSS is just a data store, nothing more, nothing less, I was programming with it back before it was RTM'd with Exchange 2000. "

Hmmm.

WSS = Windows SharePoint Services = SharePoint v2/v3

STS = SharePoint Team Services = SharePoint v1

"WSS is just a data store"  I suppose that Access 07 is not a database application anymore also...

Eric,

there are exactly ZERO discussions about how to modify the "Office File Menu" - the big round button. This has NOT be covered in this blog - or Jensen's - or anywhere.

In Access 2007 can the items on this menu be removed/added/changed through VB?

For example, can we remove the "Access Settings", New, Open, Save As, etc.

I would really like an answer to this!

Erik,

I answered my question.  Yes, you can modify the "office button" FULLY.  This is good news for the HelpDesk…

Now if I can just get an event to fire when a ribbon menu TAB is clicked.  This will at least allow existing 1024x768 forms to work if the ribbon is set to auto hide - not ideal, a clunky/distracting menu flash actually, but it removes an impediment to Access 07 use by hundreds of users with 1024x768 screens.

An alternative is to have the main application objects (that were accessible via a nice slim custom tool bar) accessible via large garish buttons on a ribbon or clumped up buttons or something ribbon-fishy.

Mike

Mike: "WSS is just a data store"

Yes ... Web Storage System was introduced with Exhange 2000 not with SharePoint (which was code named Tahoe). Take a look at an Exchange 2000 server, you will see a mapped drive "M". This is a virtual drive that you can use to read/write files into WSS directly from Windows Explorer. SharePoint is just another FE into WSS. You can read/write to WSS without using SharePoint at all by using file system code, ExIFS and even with ADO.

<I suppose that Access 07 is not a database application anymore also.> it is a database management system, it does not store the data in and of itself, that is why we have the option to use jet, newjet, excel, text, sharepoint etc. as data sources.

I am still experimenting with ribbon options myself and also dislike the *playschool* button size but have not come up with a good use of the extra space if I do use small buttons.

Steve

I finally got to updating to the nearly half a gig "2007 Microsoft Office system Beta 2 Technical Refresh".

I could not find any info as to what was fixed or changed for Access in this release. Does anyone have a clue on this?

One thing that I noticed was NOT fixed was the "auto repeat" capability on the navigation buttons (i.e. the ability to quickly scroll through records when the forward or back buttons are pressed). I reported this months ago.

I was hoping that there would be several new "color schemes", or a way to make forms have a classic Windows style. Will this ever be possible?

Stevbe, Ok, now I understand your confusion!

The acronym “WSS” was also used for the “Web Storage System”, an Exchange 2000 and SharePoint Portal Server 2001 (SPS) storage tech that now is defunct.  Exchange now calls it “Exchange Storage”.  There is no “M:” drive in Exchange 2003.

Windows SharePoint Services, SharePoint Portal Server 2003, the new Office SharePoint Server (MOSS) do not use the old Exchange Web Storage System.  The future of Web Storage System in Exchange is murky.

Clear as M$ mud?

To say this another way, Windows SharePoint Services (WSS) that M$ linked Access 07 to has nothing to do with the old Web Storage System. WSS uses SQL Server or MSDE/Express as a storage platform in a funky SharePoint kind of way (basically everything is stored in three tables).

You did make me think, although.  From the stand point of Access, it makes no sense to story anything on SharePoint.

But from the Standpoint of SharePoint, it’s good to have something as powerful as Access to handle data massaging.  So for example if a SharePoint “list” (that is SharePoint lingo for a pseudo table), contains a survey or URLs, contacts, etc, you can at least get at this data and manipulate it allot easer in Access.  This is one case where it makes sense to link Access to SharePoint.

Mike

OMG ... thanks for correcting me ... it has been a while (obviously) since I worked with Original WSS  ... never touched the new Coke :-)

Why use the same TLA ... with only 19683 (3^3?) available they need to start conserving resources ... or is this kind of like the confusin being sown by mashing WinFX and .NET 2.0 together and calling it .Net 3.0?

I don't ever plan on storing anything in SharePoint directly, hey ... I read the details, it really is good at indexing but you are are wasting time and space by not just pointing it at your file servers. OK ... enough rant ... I have not got B2TR on my PC yet but am planning on it this weekend for more ribbon experiments.

Steve

In the last post, I described how you can publish an Access database to SharePoint. This time, we'll

Access 2003 already had some WSS 2.0 integration capabilities: it could link, import and export lists, edit data, create reports based on WSS data, etc.

Now Access + WSS integration is said to be a "brand new feature" and, even more, the main reason to upgrade do A2007.

It's a nice feature to have and I consider Access an excellent front-end to WSS data, but I don't believe I'll see or work with Access + WSS apps because 99% of the Access relies on desktop databases.

I want to see what was done for developers: improvements in object models? Tools for code-generation?

Mouse wheel in VBE is fine, but it should be on since Access 2k or XP.

Report and form design capabilities are one of the main benefits for developers.

PingBack from http://www.virtualassist.net/blog/2007/01/23/whats-new-in-microsoft-access-2007-publish-to-sharepoint/

What is it going to take to get microsoft to add an auto scheduling function to MS Access.  

Please try to look at it from the users side.  To schedule reports in MS Access would be a revolutionary tool.  Please try to help us.

PingBack from http://workerthread.wordpress.com/2007/05/28/ken-getz-at-the-access-user-group/