Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SharePoint _ Move Access 2016 Database To Sharepoint 365

Posted by: halefamily104 Apr 7 2019, 07:08 PM

Hello UA!

After a ton of help from UA members (thanks again!) I have my database finished and ready to move to SharePoint (I am admin of our SharePoint site). I have read several articles on "upsizing" the data to SharePoint but there are SO many opinions on best practices. The database that I have is new and will not get very large for some time now. There are 6 people that will have access. Three will enter data and the other three will only have read access. The recipients are ready to enter data and I just want to know how to do this quickly and effectively. I have split the database and moved the front end and back end to SharePoint as a document. The problem is the link in the backend is pointing to my one drive folder and they do not have access. The article said you can relink the tables by using the link table manager but when I try to do that, it requires me to save a copy before I can make changes. Can I just create a folder in One Drive, load the front end and back end and distribute the front end to the users with the backend housed on my one drive??? If its better in SharePoint, please advise me on how to do it.


Posted by: theDBguy Apr 7 2019, 07:19 PM

Hi. Is your situation where all the database users are not on the same network domain? Otherwise, why were you considering SharePoint? Putting the BE in a Document Library is not the correct way to share or use a database for a multi-user environment. If all users are on the same network, then you can put the BE in a shared network folder. If the users are scattered in different locations, then one thing you could do is convert your Access BE into SharePoint Lists and then connect/link your FE to SharePoint.

Posted by: halefamily104 Apr 7 2019, 07:29 PM

Hi the DBguy! You are correct. I am in Texas and the users will be in California, Maine and New York! I tried my hand at using the upload to sharepoint button in Access but when the tables were created, there was several fields in the tables that I did not add. Some of them were "required" and I couldn't remove them (three hours and three different Office 365 techs). Since the subsite was new, I deleted it and tried again. I uploaded the back end and front end to the subsite but I am unsure how to relink.

Posted by: theDBguy Apr 7 2019, 07:51 PM

Hi. So, it sounds like you do want to use SharePoint Lists. One way to do it is to use the Database Tools tab on the Ribbon and click on the SharePoint button in the Move Data group.

Is this how you did it? This process will create the necessary SharePoint Lists and move your data into SharePoint and then create link tables in your FE. Don't worry if SharePoint creates additional columns in the List. You can take them out because they are needed. The only potential problem is maybe if you have any fields with the same name as what SharePoint needs. I am not sure what will happen in that case.

Posted by: halefamily104 Apr 7 2019, 08:10 PM

That's absolutely how I did it. It created the tables and all was well but it created a field that was labeled "required" and we couldn't delete it for the world. I think the problem was when the tables were created, they were created as Contacts, not lists. I then had to delete the contacts lists and recreate them as lists. Still had issues. How can I ensure the lists are uploaded correctly?

Posted by: theDBguy Apr 7 2019, 08:24 PM

Hi. The wizard should do it for you; but if you want to do it manually, then it’s also possible. lf you like, I can give you a hand with it. We can use something like TeamViewer.

Posted by: GroverParkGeorge Apr 7 2019, 08:25 PM

IMO, you shouldn't invest a lot of effort in removing "superfluous fields" from linked SharePoint lists. Why? Because they don't harm anything in any significant way. Removing them may or may not have adverse impact on the SharePoint lists if any of those columns represent hooks that SharePoint needs. In short, it may be redundant for humans to have those fields, but we simply don't know enough about them in your situation to want to offer advice one way or the other.

You can hide those columns from view in Access if they bother you. And you don't need to include them in queries. So, other than adding to their size, I see no reason to try to figure out which ones can and can't be safely deleted. At least not right away.

You'll find that this configuration (Access FE linked to SharePoint lists) does provide not only the remote connectivity you need, but Linked SharePoint lists (exposed in Access as tables) also can be used off-line and Access will automatically attempt to resynch them when the accdb connects back to SharePoint again later. This can result in some bloat because that cached data is kept in the accdb, but it's a reasonable trade-off, IMO.

Several years ago I wrote some, maybe one or more of them will be useful still.

Posted by: halefamily104 Apr 7 2019, 08:54 PM

Thanks GroverParkGeorge! I would not have been bothered by them except the one field that was "required" would not go away! I will re-upload them to SharePoint and hope for the best! Are there any tips or advice I need to heed? I also read something about the secondary key needing to be set as long integer?

Posted by: halefamily104 Apr 7 2019, 09:18 PM

Just to make sure Im doing this correctly. I have uploaded the tables to SharePoint. Then I add the new SharePoint tables to my db and delete the actual tables, which deletes the relationships. Is this correct? Do I still need to split the db so I can distribute the Front end?

Posted by: theDBguy Apr 7 2019, 09:46 PM

Hi. How exactly did you upload the tables to SP? If you use the button I mentioned earlier, I thought it would automatically delete the local tables and replace them with the new ones from SP? You shouldn’t have to do anything else except maybe link your FE to SP.

Posted by: RobKoelmans Apr 8 2019, 01:49 AM

Hi George,
I went through your three articles. Seriously? The Access Team was obviously instructed to promote usage of SharePoint on behalf of BPOS/Office365 but SharePoint is not a database environment. In 2019, I don't see the point. We have JSON now don't we? And we have Flow to deploy a webservice professionally with fantastic monitoring/troubleshooting. if you look for the video 'Retrieving data through MS-Flow in MS-Access' on you can see how fantastically this works. In 'PowerApps Create and retrieve orders' you can see how you can write from Access with Flow.

You don't need Access Web Apps btw. For some reason AccessMVP all think you're at a dead end if you develop your stored procedures in AWA. But it's fine to build them in SSMS or completely as a script in Flow ExecQuery.
It's reasonably safe because of the huge key in the Flow URL but you can make it professionally safe with SHA256 one time pass tokens with just a couple of lines of code.

Posted by: halefamily104 Apr 8 2019, 10:42 AM

I have moved my tables to sharepoint, relinked them to my front end and no forms are working!

Any help will be greatly appreciated!

Posted by: theDBguy Apr 8 2019, 10:52 AM

Hi. Good morning. For something like this, I think a little hand-holding is useful; so, my previous offer still stands. Cheers!

Posted by: halefamily104 Apr 8 2019, 10:56 AM

Ill take it! How about skype?

Posted by: theDBguy Apr 8 2019, 11:14 AM

Sure, I can use Skype. I'm logged on right now...

Posted by: halefamily104 Apr 8 2019, 11:18 AM

whats your SkypeName?

Posted by: theDBguy Apr 8 2019, 11:26 AM

thedbguy or DB Guy

Posted by: GroverParkGeorge Apr 8 2019, 12:11 PM

That series is now 8 years old, but it addresses the issue of linking SharePoint Lists as tables in Access.

Posted by: RobKoelmans Apr 8 2019, 03:28 PM

Hi George,
I think migrating data from Access BE's or from SQL-Server to SharePoint should be done selectively and only if you need that data to be in SharePoint for some reason. For instance, when you want to refer to that data from other other List- and Document-folders. We tried some migrations in 2010 but there were constant credentials/login issues and response ranged from slow to hopelessly slow. Except for External Lists (Business Connectivity Services), it looked as if the whole table was loaded each time and only then the filtering was applied. Then there was the timebomb of the threshold. We had it at 5000 first and later at 10000. But if you go to 50K or higher on rows you can plan your next migration. It's possible we missed some techniques. Do you know of well-working deployments by this architecture?

Posted by: GroverParkGeorge Apr 9 2019, 07:23 AM

I agree with that assessment, Rob. When I wrote those blog posts, the concept of the "table-like" SP lists was relatively new and we hadn't yet bumped into some of the more difficult problems yet. I was pretty naive, I guess.

I would NEVER recommend the use of the Access Web Database (from 2010) which was based on that approach. On the other hand, in the appropriate niche, I see it as a viable transitional plan until a real solution is implemented, e.g. a SQL Azure BE.

Posted by: RobKoelmans Apr 9 2019, 07:43 AM

Yes, Azure BE is a much better way to go but still not 3-tier. If you have subscribed to Office365, you probably have Flow. The funny thing is, that not only the architecture with Flow is better, it's also way faster if you throw a script at SQL-server and get back the result in JSON in ExecQuery in Flow (not using GetRows a.t.l.). By parsing a session key along with a One Time Pass Token on each round trip you can even make Flow session-aware by creating a record in a session table in SQL-Server and reinstall the session state on each roundtrip. Still, Flow will have one cycle because everything is done in the query script that Flow threw at SQL-server. This may seem complex but is in practice much, much more simple than any other solution for a number of reasons.