Publish to SharePoint

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. www.utteraccess.com/.../showflat.php. 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.