Sharepoint help!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Sharepoint help!

Post by NWGal »

I just got a Sharepoint access through an Office 365 subscription. Now I'm on a brand new learning curve as well as a timeline. I need to figure out the best way to utilize my web access to share my database and I need to get it sorted very, very quickly. After a ton of reading and digging, I'm looking at spliting the db and moving the tables to Sharepoint lists, that's the backend. I want to keep the front end as I have it with my forms, vba, reports ect. Has anyone done this, is it a viable solution? I simply don't have time to wade through learning all the ins and outs of building forms and such that would work for me as well as what I have.
My thought is, since I already have the wheel and it works great, is this method going to make it possible for my co-workers to simply access my frontend on their copy of Access 2013, enter and edit data, and have it all stored online.
Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sharepoint help!

Post by HansV »

I'm afraid I can't help you with this - I don't work with Office 365 and SharePoint at all. I hope that someone who has hands-on experience with them can help.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Sharepoint help!

Post by Rudi »

I'm is the same boat as Hans.
I've also never used 365 or familiar with its integration with SharePoint.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

I've not used it, but I've seen several demos of the process, and heard about a number of potential pitfalls with it. The biggest challenge in storing tables in Sharepoint lists is that if your tables are large - say 100K records or more, performance seems to get very sluggish. Another issue to consider is that the front-end database needs to be in the .accdb file format. Finally, there are issues using VBA. I believe the real concept is that you push your entire application to the Web so that any person who gets the proper credentials can access it and with the proper permissions, can also enter and update data. I'll need to do some further reading to confirm that, but wanted to share with you what I've seen and heard.

For certain, you will want to split your database if it isn't already. There are numerous advantages to doing that. Are your co-workers located in different offices, so that you aren't on the same network? If not, it would be much easier to simply setup a "server" PC that contains the backend, and then distribute your front-end to each workstation. If they are in many different locations and not connected to the same network, the problem gets more challengeing, but there are other approaches. One involves using Terminal Services - I see that used in some cases, but it requires some hardware and software infrastructure at a central location. The other approach is sometimes called the "hybrid" location - in it, you put the data in the cloud on SQL Azure, and connect to it either using the Office 365 version of Access, or with a local copy of Access 2013. I'll update this after I've done some further research.
Wendell
You can't see the view if you don't climb the mountain!

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

I've done a bit of research - and it seems there are widely differing views on how well Access and Sharepoint co-exist. The link Publish a split database to Sharepoint... is a recent example in the Microsoft Community Office forum. It does indicate that you don't have to push the entire database to Sharepoint, but if you want to serve co-workers at distant locations, they somehow have to have a means of getting to the data. (BTW, the two MVPs in that thread are highly respected members of the Access community.) You might also want to look at some of the information Luke Chung at FMS has posted - see Microsoft Access and Cloud Computing with SQL Azure Databases for example, and some of the links there. That is the hybrid approach I mentioned in my previous post. The bottom line for me is that you don't want to plunge ahead without understanding the bumps in the road ahead. Post some further details about your network and the location of your co-workers to give us a feel as to what would work best.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Ahh, I was surely hoping for a more simple answer. To give a bit more info, we are a small non profit serving several counties with offices in two locations. We don't have any IT or tech personelle, only me, a mostly self taught and not very techie, program coordinator.
Everyone who needs to share the db has Office Pro 2013 installed on their desk top and will be a user on our 365 site.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

Based on that situation, I would first try to run your database in the cloud (Office 365) completely - though I've not personally tried that. If that get's you acceptable performance, then that would be my choice. I will do a bit of investigation and contact a couple of people I know and see what their take is as well.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Wendell wrote:Based on that situation, I would first try to run your database in the cloud (Office 365) completely - though I've not personally tried that. If that get's you acceptable performance, then that would be my choice. I will do a bit of investigation and contact a couple of people I know and see what their take is as well.
Thanks, I appreciate all help.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

Well, it isn't as simple as I had hoped - simply putting your database in the cloud, whether with Office 365 or OneDrive/SkyDrive isn't the answer. When you do that, the database is actually downloaded to your local workstation and run there, and then synchronized back up when you finish. So two people working on it at the same time would create a real mess. I think the best answer in your situation is to put your back-end database (the tables) in SQL Azure, and have anyone who needs to run the database have a local copy of the front-end database (queries, forms, report, macros and modules) on their workstation - it could be downloaded from Office 365. That's the approach talked about in the Luke Chung article I referenced in my second post. That's sort of an overview tutorial, so you may want to get more information - I would suggest "Access 2013 Inside Out" by Jeff Conrad - the electronic edition has quite a bit of information about working with SQL Azure, and he's a member of the Access development team.

I should also note that there is another solution you might want to consider, and that is putting the back-end in SQL Server on a local server, and putting a replica of that database on another system at the second location. Older versions of Access supported replication, but the current versions do not, so SQL Server would be required. Replication has been the principal way that users in distant locations used the same database until the "cloud" services became available, but it is a complex subject, and can be challenging to keep synchronized if you have unreliable Internet links. We've been using it since 2007 in one instance, and have managed to keep it working, but it is currently down at the moment because of an apparent Internet connection problem at one of the servers.

Feel free to pursue either option in this thread or a new one. This is a subject of considerable interest to many developers, and I personally would be interested in how you fare.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Wendell wrote:Well, it isn't as simple as I had hoped - simply putting your database in the cloud, whether with Office 365 or OneDrive/SkyDrive isn't the answer. When you do that, the database is actually downloaded to your local workstation and run there, and then synchronized back up when you finish. So two people working on it at the same time would create a real mess. I think the best answer in your situation is to put your back-end database (the tables) in SQL Azure, and have anyone who needs to run the database have a local copy of the front-end database (queries, forms, report, macros and modules) on their workstation - it could be downloaded from Office 365. That's the approach talked about in the Luke Chung article I referenced in my second post. That's sort of an overview tutorial, so you may want to get more information - I would suggest "Access 2013 Inside Out" by Jeff Conrad - the electronic edition has quite a bit of information about working with SQL Azure, and he's a member of the Access development team.

I should also note that there is another solution you might want to consider, and that is putting the back-end in SQL Server on a local server, and putting a replica of that database on another system at the second location. Older versions of Access supported replication, but the current versions do not, so SQL Server would be required. Replication has been the principal way that users in distant locations used the same database until the "cloud" services became available, but it is a complex subject, and can be challenging to keep synchronized if you have unreliable Internet links. We've been using it since 2007 in one instance, and have managed to keep it working, but it is currently down at the moment because of an apparent Internet connection problem at one of the servers.

Feel free to pursue either option in this thread or a new one. This is a subject of considerable interest to many developers, and I personally would be interested in how you fare.
I really appreciate the feedback. So far it seems I have taken the first solution in that I have "moved" the tables to my sharepoint site, and when I add or change a record on my desktop version, it's reflecting in the sharepoint list (HOORAY). If I'm reading things correctly, doesn't 365 have SQL Azure as it's "guts", so that without realizing it, I am actually doing your first suggestion? Am I on the right track?
If I am, the next piece is after I set up my desktop end users with the "front end", how do I link them with the "backend"?

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Ok, also, I just realized that back in my desktop, the linked tables (they are a different color now) have data deleted. What is up with that. The missing data is from junction tables. Yikes, that won't work. Argghh...good thing I made a backup first.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

I believe it is correct that the Sharepoint lists do get stored in SQL Azure, but that's not the same as actually working with SQL Server tables directly. When you like directly to an Azure (SQL Server) database you are actually creating a connection directly to the tables in SQL Server rather than going through SharePoint. In order to work with a local Access front-end connected to Sharepoint, as I understand it, the list gets downloaded to your local database, updated and then synched up. In theory, things are supposed to be synchronized and several people can be working in local copies of Access. The complication arises when one person edits a record, and another person edits the same record, or deletes it for example. I believe in such cases you have to do a manual conflict resolution to sort out what the data in a record should look like.

In the direct connection to Azure, you basically create an ODBC connection (or a ADO connection using a connection string) that goes to a specific IP address for the Azure server, and then you link the tables much as you would with a local SQL Server database. If you aren't all that comfortable using a SQL Server backend, then continue down the Sharepoint path and see how things go. A couple of questions - was your database split into a Front-End and Back-End before you uploaded to Sharepoint? And if so, did you upload just the data side of things?
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Well, I'm not sure how I found this, but somehow I landed on a blog site where a guy actually goes step by step for creating a hybrid! Maybe it was something linked to on here, but here it is again if anyone else is in my boat and can use it.
http://www.devhut.net/2014/01/13/how-to ... omment-595" onclick="window.open(this.href);return false;

I will admit that I am stuck at the creating a dsn file step, but up to that point everything looks like it is working fine. I will keep you all updated.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

Excellent! I had not seen that post, but that should help a great deal. And I can assist with the File DSN creation. The one thing you have to be aware of, is that when you put a database in the cloud, you want to be sure to not return large recordsets with queries, whether they are stand-alone, or behind forms or reports as the record source. You are typically OK as long as you only are returning a few hundred at most. But if you have complex queries that Access cannot optimize for ODBC, then you may end up returning one or more entire tables for Access to do the joins and run the query. Those can be woefully slow. BTW, that is true even if the SQL Server database is on your local LAN - putting it in the cloud will make it that much slower. We've seen a case where if the query is run in a local Access back-end, it will run in less than a second. Move the tables behind that query to Access, and it may take ten seconds or more. Optimize it so the query gets run in SQL Server, and it takes milliseconds.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Wendell wrote:Excellent! I had not seen that post, but that should help a great deal. And I can assist with the File DSN creation. The one thing you have to be aware of, is that when you put a database in the cloud, you want to be sure to not return large recordsets with queries, whether they are stand-alone, or behind forms or reports as the record source. You are typically OK as long as you only are returning a few hundred at most. But if you have complex queries that Access cannot optimize for ODBC, then you may end up returning one or more entire tables for Access to do the joins and run the query. Those can be woefully slow. BTW, that is true even if the SQL Server database is on your local LAN - putting it in the cloud will make it that much slower. We've seen a case where if the query is run in a local Access back-end, it will run in less than a second. Move the tables behind that query to Access, and it may take ten seconds or more. Optimize it so the query gets run in SQL Server, and it takes milliseconds.
Thanks for the warning. I seriously don't forsee us working with large recordsets, it really is a smallish database and if we ever do grow to working with large numbers...well I hope by then they hire someone who truly knows what they are doing - LOL.
I could use help with the file dsn creation. I am completely lost.
FYI, I am getting ready to close up for the day and head home in about 15 minutes and won't be able to get back to work on this until tomorrow.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

If you haven't sorted out the DSN challenges, check your messages at the top of the forum under control panel. I may be able to help.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Sharepoint help!

Post by NWGal »

Update! After a long, frustrating journey which included MS techsupport (useless), I finally found the answer. I split the db, and linked to a 365 web app using ODBC and now I have succeeded in being able to connect my fe to the sharepoint list (web app) from anywhere I put it. :clapping:
For those interested in this hybrid method I recommend this blog post, http://www.devhut.net/2014/01/13/how-to ... omment-595" onclick="window.open(this.href);return false; but with the added step that once you have your linked tables, you MUST delete your local tables and rename the linked tables with the former local table names.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sharepoint help!

Post by HansV »

Thanks for sharing the solution!
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Sharepoint help!

Post by Rudi »

Thanks for sharing...
This is a useful piece of info as I was chatting with someone earlier in the week who was interested in experimenting with a web orientated database.

Cheers
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Sharepoint help!

Post by Wendell »

NWGal wrote:Update! After a long, frustrating journey which included MS techsupport (useless), I finally found the answer. I split the db, and linked to a 365 web app using ODBC and now I have succeeded in being able to connect my fe to the sharepoint list (web app) from anywhere I put it. :clapping:
For those interested in this hybrid method I recommend this blog post, http://www.devhut.net/2014/01/13/how-to ... omment-595" onclick="window.open(this.href);return false; but with the added step that once you have your linked tables, you MUST delete your local tables and rename the linked tables with the former local table names.
Glad you finally figured out the issue - I was about to post to see how far you had gotten. And I did know that you needed to either delete or rename your local tables and give your linked tables the same name as the local tables originally had. I'm curious if you are seeing any performance issues with the tables now residing in the cloud?
Wendell
You can't see the view if you don't climb the mountain!