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
> Moving Data To Sharepoint, SharePoint Foundation 2010    
 
   
Brepea
post Oct 18 2017, 02:03 AM
Post#1



Posts: 515
Joined: 11-January 09
From: UK


Hi all

I am trying to move data from an existing BE to Sharepoint. I've created a blank web database and attempted to import tables into it (with relationships checkbox unchecked) - i'm getting all sorts of errors, but this one particular stands out mostly as follows: AccWeb105010 - "Lookups require an associated relationship to be compatible with the Web".

I'm not sure what they mean as i can't see anything wrong with the relationship from table level nor the relationship window. The table-level screen shots are attached. The relationship window simply has the 1-many relationship with enable referential integrity checked and the cascading update related fields also checked.

Can anyone help me work out why the import fails? I have also follows Albert Kallal's video step-by-step (https://www.youtube.com/watch?v=3wdjYIby_b0&t=482s)

Attached File  SP_Import_Web_Error.png ( 546.7K )Number of downloads: 3
Go to the top of the page
 
Brepea
post Oct 18 2017, 02:25 AM
Post#2



Posts: 515
Joined: 11-January 09
From: UK


Interestingly - if i import the said tables with the "relationships" checked - it imports ok...
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 03:20 AM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Not to an Access Web Database I hope.

You're just trying to move the data to SharePoint lists to be used as tables linked to your accdb, right?

The answer to this problem is that you must, unfortunately, use the Lookup Field mechanism to tell SharePoint which relationships to create, not standard Relationships and RI. This is the only situation in which I can see using the otherwise senseless Lookup Fields in tables.

--------------------
Go to the top of the page
 
Brepea
post Oct 18 2017, 03:52 AM
Post#4



Posts: 515
Joined: 11-January 09
From: UK


Hi George

Yes - my last attempt to try something else. The RDP would work great, but there is something like 6 months lead time to this solution. In the interim - i'm having to come up with alternative results...this is all i can think of right now.

So yes - trying to get my data to SharePoint lists which the FE will link to. Albert's video takes one through it - and i've managed to get quite a few tables from normal non-web .accdb to a web database (blank one before pushing up to SP)...but i keep getting the same issue with the AccWeb105010 for my main tables...I have created these fields in the normal .accdb as one would via the Lookup in table design - then it asks me which table/query to lookup - then i do the usual link here. In relationship window i create the cascade update related field and RI...

Am I missing something?
Go to the top of the page
 
Brepea
post Oct 18 2017, 04:23 AM
Post#5



Posts: 515
Joined: 11-January 09
From: UK


Ok - so i see i need to create new blank columns for all my lookups from non-web .accdb. Copy ID values that were in old lookup column - and paste these to the new column field (number - long integer as data type).

Then from new web database - i need to recreate relationship from datasheet view (lookup)....
Go to the top of the page
 
Minty
post Oct 18 2017, 06:41 AM
Post#6



Posts: 70
Joined: 5-July 16



As George alluded to I wouldn't invest the time - even short term to developing an Access Web App, they are being deprecated, and removed from MS's platform.
https://redmondmag.com/articles/2017/04/04/...int-online.aspx
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 06:53 AM
Post#7


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


I wrote some blog articles on this a long time ago when I was still enthusiastic about it.

My understanding here is that you are NOT trying to create an Access Web Database, only moving the tables to SharePoint to use as a remote back end. Right?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 07:00 AM
Post#8


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, there's enough overlap in terminology to be slightly confusing.

In 2010, Microsoft debuted the Access Web Database, which featured SharePoint Lists as tables, and html "forms" that ran in a browser. Ben Clothier and I jumped on that bandwagon and wrote the only book ever devoted to the subject (to my knowledge). Others did mention it, of course.

Microsoft deprecated them.

In 2013, Microsoft launched the Access Web App, which featured Azure SQL databases as the backend and true html5/javascript forms that run in a browswer. Ben Clothier and I jumped on that bandwagon and wrote part of book devoted to the subject.

Microsoft deprecated them, at least as far as Office 365 is concerned.

It is important to note that on-premises SharePoint sites will still be able to support AWAs for the next 5 to 10 years. One UA member is planning to offer hosting for them on his organization's SharePoint Server in the Netherlands. They are, therefore, not dead.

On the other hand, I may never write another book on Access. I love Access too much.

--------------------
Go to the top of the page
 
Brepea
post Oct 18 2017, 07:16 AM
Post#9



Posts: 515
Joined: 11-January 09
From: UK


Yes - moving data to sharepoint lists. Presumably this is still ok (i.e. no deprecated)?
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 07:37 AM
Post#10


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


So far as I know, it's a good alternative "for the foreseeable future". In those cases where you need remote access to smaller amounts of data, it's a good choice. And, given the ability to re-synchronize lists with locally cached tables, they support disconnected remote work, which pretty much no other alternative can without a lot of complex coding.

--------------------
Go to the top of the page
 
Brepea
post Oct 18 2017, 10:21 AM
Post#11



Posts: 515
Joined: 11-January 09
From: UK


Is there a logic to exporting/moving the data (once PK and FK all done properly to prep for web transfer)? I have been trying by (1) Ribbon / External Data / More / SharePoint List & also via (2) right-clicking a table then clicking Export / SharePoint List (presumably the same thing, yet it yields different results).

I tried first option - and when I did it did everything and told me there was one issue = one of my tables was read only (which it definitely isn't). I did option 2 on that specific table - and it worked fine. Problem is whenever you do them one-by-one if there is a related table it creates a copy of that tables as a list (which is not helpful)....
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 10:38 AM
Post#12


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Well, it really does depend somewhat on the specific table(s).

I would definitely prep and move the tables in a group rather than one at a time. The read only table might have lost its primary key.

Follow Albert's video and some of the descriptions in my blog articles (there should be at least three on the topic).

The good thing is that you can redo it a few times, if necessary to get the results you need.

--------------------
Go to the top of the page
 
Minty
post Oct 18 2017, 10:55 AM
Post#13



Posts: 70
Joined: 5-July 16



@George - You do appear to have the Grim Reaper touch for Access "Web Accessories" smile.gif

I'd start a Power App book now, save me trying to learn it wink.gif
Go to the top of the page
 
GroverParkGeorge
post Oct 18 2017, 11:04 AM
Post#14


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Hm. That would require actually learning more than the mere basics about PowerApps.....

--------------------
Go to the top of the page
 
Brepea
post Oct 18 2017, 12:38 PM
Post#15



Posts: 515
Joined: 11-January 09
From: UK


I just can't seem to work out why it stops at my one table (no other tables after that are carried through to SP (they are - but have no data in them). The said table carries through fine if i right-click on it and export table to sharepoint - but not when i do it all in one go. Any ideas why it does this?
Go to the top of the page
 
AlbertKallal
post Oct 18 2017, 02:08 PM
Post#16


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Simply re-build the relationship using the wizard. There are a "few" settings that are often wrong with relationships - and SharePoint don't like them.

I have a video here that explains how to re-make the relationships so they work and will upload.

https://www.youtube.com/watch?v=3wdjYIby_b0...7E956A1537FE1C5

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
Brepea
post Oct 19 2017, 12:37 AM
Post#17



Posts: 515
Joined: 11-January 09
From: UK


Hi Albert - yes - a very useful video and is what I have used to get everything else done...does anyone know what would be considered illegal characters for sharepoint...? I find it strange that when i right-click the table it exports to SP lists (along with all the other tables associated to it) - so why then doesn't it do it on bulk...? The problem with doing this one-by-one is if i do said table on it's own - it exports fine - but i have other main tables that don't get carried over with that export - so when i export those tables - because there are lookups to tables already exported to SP when carrying out manual export - it creates another table_1 with suffix "1" - not sure how to delete that one and redo the lookup - but if possible - what a pain to have to do that to all tables....
Go to the top of the page
 
Brepea
post Oct 19 2017, 05:26 AM
Post#18



Posts: 515
Joined: 11-January 09
From: UK


I've basically renamed the problem table t_xStaff - to get this to appear at the end of the import process - and it moves all data across except for data in the table. Now i am recreating the table from Sharepoint directly - then copy and paste from Access (using link tbales to newly create able from sharepoint direct - to the problem table).
Go to the top of the page
 
Brepea
post Oct 19 2017, 04:43 PM
Post#19



Posts: 515
Joined: 11-January 09
From: UK


Albert -presumably if you have more than 1 lookup /relationship field pre-move to sharepoint; for e.g. in a staff table you may StaffID; TeamID & StaffLevelID - you'd create the relationships via "Lookup / Relationship" option for each as well - right?
Go to the top of the page
 
Brepea
post Oct 19 2017, 07:29 PM
Post#20



Posts: 515
Joined: 11-January 09
From: UK


No - i just can't get this to work... I managed to get all tables in SP - but some tables have the correct PK name - but the values are SP's values from 1-n number - and it should be starting with 259 or whatever my original ID values were. I can delete the PK field and past the _Old values in - i.e. my auto-increment on the old field ID will not be honoured. Not sure what i'm doing wrong here... i've watched Albert's video and George's blog...
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 03:20 AM