Can I deploy this MS Access app via Sharepoint fully or partially?? 1

[BoulderRidge]

--- Posting same thread on MS Access: Other and MS Sharepoint forums ---
I have an MS Access app that I need to deploy to multiple users who don’t share a single network drive. Their best common access point is via Sharepoint. What I’ve read to date suggests that publishing the Access app fully onto Sharepoint will severely limit the functionality I can use in the app—i.e. no VBA code under my forms.

What other options might work for deployment? Could I publish only shared tables to Sharepoint, link them back to my MDB containing forms, and distribute this as an MDE file to my users? Or is there a way from a Sharepoint site to either let them run the MS Access “front end” in a multi-user mode or download an updated front end MDEs when changes are made?

I can use MS Access 2003 or 2007 and Sharepoint WSS3 or 2007, no higher on either count. I cannot assume all users will have MS Access installed locally. I have experience with Access development but am a newbie with Sharepoint. Any help is greatly appreciated!



-- BoulderRidge B-)

 

[MajP]

You can treat Sharepoint as your backend just like any other backend, and do what you were asking. It works OK, and I think I am pushing the limits of what it can do, but it is not really a relational database so there are limits to what you can do.
You cannot enforce referential integrity so ensure you are doing this on your forms. I recommend rolling your own primary keys and not using autonumbers. I do not think they will convert over well. Do as much data validation at the form level. Although MS does not advertise limits to the lists, I think once you get over 3K records in a list things get weird. I do not think Sharepoint is ready for very large lists.

But bottom line is you can build your split database and get it working then upload the tables to sharepoint where they will become lists. Then relink to the sharepoint site and give it a try.
With these limitations in mind it has been working suprisingly well. All my users have a robust front end linked to a sharepoint be. We have about 30 uses with some VPNing in.

The biggest issue is finding information. Neither the Sharepoint books or the Access books provide good information for doing this.

 

[BoulderRidge]

Thanks much MajP. A few clarifications:
1. What versions of Access and Sharepoint are you using?
2. Are you using VBA code in your front end?
3. Any suggestion for "rolling my own primary keys" since I've currently got autonumbers in every table? Or is this OK since I don't need to cascade updates or deletes?

Agreed finding info is tough, my perusal at the bookstore had me ready to abandon the whole idea last night, so really appreciate your post!


-- BoulderRidge B-)

 

[MajP]

We are on 2007 for both. I hear 2010 is better. The front end is no different than any other front end, full of forms, code, and queries, reports. In fact we were using the same front end we were using on a standard backend until migrating to SP. Once you link your tables you will do everything exactly the same.

I have some natural key for my PKs like program name. However there is plenty of code in the FAQs for the Access Tables forum on making your own auto increment.
However, I think the issue was that I did have autonumbers and had to save those in another field, because I had to create a new autonumber field in SP. Then I could relink the foreing keys based on the new autonumber field and the saved old fiedl. If you are not starting without data then it should not be a problem. So you do have autonumbers in lists, but you just have to force "referential integrity" on your own.

 

[BoulderRidge]

Ok, progress. Converted to 2007, moved tables to my SHarepoint site and linked them back in. I have an issue with one autonumber that is used when creating new records, but you predicted that

How would the Sharepoint user permissions need to be set up so that users cannot see the contents of the lists from SHarepoint, but can still read and edit them from within the Access application? (Hope this is not my dealbreaker issue...) I need the table contents to be such that each user can only see their own record, not everyone else's. This is done via the application by detecting and filtering the form on their username and works beautifully. But if they could open the table from Sharepoint they could see everything!


-- BoulderRidge B-)

 

[MajP]

I do not touch the Sharepoint Side, and our experts are not in for me to ask. I just do the front end portion once the lists are converted and uploaded. But our lists are completely hidden from the users so they cannot see it on sharepoint. So it can be done just do not know how. You may want to post on the Sharepoint forum since that is a Sharepoint specific question.

 

[BoulderRidge]

I have worked thru the Autonumber issues and all lists are going to Sharepoint without error. I removed the Unique attribute from all my indexes per reading that SP will only allow one unique index, it's own ID field. But now (surprise) the app is quite a bit slower. Do you have any suggestions for performance?

thanks!


-- BoulderRidge B-)