UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Move Access 2016 Database To Sharepoint 365, SharePoint 2013    
 
   
halefamily104
post Apr 7 2019, 07:08 PM
Post#1



Posts: 510
Joined: 7-November 10



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.

Thanks

Go to the top of the page
 
theDBguy
post Apr 7 2019, 07:19 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
halefamily104
post Apr 7 2019, 07:29 PM
Post#3



Posts: 510
Joined: 7-November 10



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.
Go to the top of the page
 
theDBguy
post Apr 7 2019, 07:51 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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.
Attached File  sharepoint.png ( 18.28K )Number of downloads: 2

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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
halefamily104
post Apr 7 2019, 08:10 PM
Post#5



Posts: 510
Joined: 7-November 10



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?
Go to the top of the page
 
theDBguy
post Apr 7 2019, 08:24 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 7 2019, 08:25 PM
Post#7


UA Admin
Posts: 35,891
Joined: 20-June 02
From: Newcastle, WA


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 blog posts on this general topic, maybe one or more of them will be useful still.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
halefamily104
post Apr 7 2019, 08:54 PM
Post#8



Posts: 510
Joined: 7-November 10



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?
Go to the top of the page
 
halefamily104
post Apr 7 2019, 09:18 PM
Post#9



Posts: 510
Joined: 7-November 10



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?
Go to the top of the page
 
theDBguy
post Apr 7 2019, 09:46 PM
Post#10


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RobKoelmans
post Apr 8 2019, 01:49 AM
Post#11



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


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 MetaMicro.nl 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.
Rob
Go to the top of the page
 
halefamily104
post Apr 8 2019, 10:42 AM
Post#12



Posts: 510
Joined: 7-November 10



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

Any help will be greatly appreciated!
Go to the top of the page
 
theDBguy
post Apr 8 2019, 10:52 AM
Post#13


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
halefamily104
post Apr 8 2019, 10:56 AM
Post#14



Posts: 510
Joined: 7-November 10



Ill take it! How about skype?
Go to the top of the page
 
theDBguy
post Apr 8 2019, 11:14 AM
Post#15


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
halefamily104
post Apr 8 2019, 11:18 AM
Post#16



Posts: 510
Joined: 7-November 10



whats your SkypeName?
Go to the top of the page
 
theDBguy
post Apr 8 2019, 11:26 AM
Post#17


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


thedbguy or DB Guy

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 8 2019, 12:11 PM
Post#18


UA Admin
Posts: 35,891
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobKoelmans
post Apr 8 2019, 03:28 PM
Post#19



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


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?
Rob
Go to the top of the page
 
GroverParkGeorge
post Apr 9 2019, 07:23 AM
Post#20


UA Admin
Posts: 35,891
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 02:50 PM