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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conversion Of Awa Data To Sharepoint Lists Fails, Access 2013 Web App    
 
   
alancossey
post May 23 2017, 05:09 AM
Post#1



Posts: 545
Joined: 26-September 05
From: Norfolk, UK


I’m trying to work out whether one of my AWAs can be converted to SharePoint lists with a “normal” Access front end, but the export routine called from within the AWA is not working properly. I currently get the following errors reported:

Number of issues experienced to Export the App 'Funerals': [5]
Not Supported Creating SharePoint Fields on Lists Unable to support Calculated Column [TotalFee] of the Access Web App Table [tblEventTypes] to export to SharePoint List.
Not Supported Creating SharePoint Fields on Lists Unable to support Calculated Column [FullNameCalc] of the Access Web App Table [tblPersons] to export to SharePoint List.
Not Supported Transfer Access Web App Table Lookup to SharePoint List Lookup The lookup of Column [PersonID] of the Access Web App Table [tblEvents] is not handled since the lookup source data is not transferrable to SharePoint List.
Not Supported Transfer Access Web App Table Lookup to SharePoint List Lookup The lookup of Column [PersonID] of the Access Web App Table [tblPersonsNOKCross] is not handled since the lookup source data is not transferrable to SharePoint List.
Not Supported Transfer Access Web App Table Lookup to SharePoint List Lookup The lookup of Column [PersonID] of the Access Web App Table [tblLookupTreasurerReport] is not handled since the lookup source data is not transferrable to SharePoint List.

The Calculated Column problems are easy to resolve, but I’m stuck on the lookup column problems. The column being looked up is the field PersonID in the main table, tblPersons. Lots of other lookup fields convert with no problem. This is the only one which fails.

Anyone got any idea what might be the problem, please?

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
RobKoelmans
post May 23 2017, 05:18 AM
Post#2



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


Hi Alan,
Before you solve these issues, try something that does work from within AWA to SharePoint. The response is so bad, that you're likely not going to use it anyway. I doubt it'll be any better from Access Desktop, probably worse if it's SharePoint Online. We're hosting AWA's from this month in case you're interested.

If you do need a connection with SharePoint and msAccess Desktop, you'd have to create a SharePoint list using BCS (Business Connectivity Service) in SharePoint designer 2013. A SQL-table also approachable as a SharePoint list. That works actually quite nice. The filtering in SharePoint is parsed in the Where-clause in BCS, so your table may be in the millions without performance issues. You can make more lists that are related in the SQL-tables.
Rob
Go to the top of the page
 
GroverParkGeorge
post May 23 2017, 06:32 AM
Post#3


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


I experienced a similar problem when I first tried to export the AWA tables to SharePoint using their new function to do that.

I reported it to the Access team and provided them a copy of the app package. They did release a couple of fixes to the process. Coincidentally, about the same time as the release of the second, I did a major clean up of my O365 site, deleting a number of unused AWAs. At that point, the export finally completed successfully.

I would not use that approach anyway for my own AWAs. I will migrate the data to Azure instead.

Except for the potential ability to work with SharePoint lists while off-line, there's no real advantage to trying to use SP lists, IMO.

Rob. Actually, you can link an access to SharePoint lists directly. No need to go through BCS for that at all. On the other hand, as you say, going through BCS might offer another, more robust, path, but not one familiar to those people who have experienced AWAs only through Office 365.

I am pleased to hear you're going to offer AWA hosting. How is that going?

--------------------
Go to the top of the page
 
RobKoelmans
post May 23 2017, 07:27 AM
Post#4



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


Hi George,
SharePoint Designer works for SharePoint OnLine as well. I expect BCS to also work within SP-designer on O365. We have medical survey answering lists in SharePoint that are not with BCS and then you get really into trouble (because SP loads the list in memory completely to give you a filtered result set. BCS is limited functionally as List but performs and scales fine.

We have installed and tested five simple applications for a Dutch developer. Performance is much better than on O365 but he's not in a hurry because his existing AWA's run on O365 till October still, I think. As soon as he has us develop a PowerApp connected to his AWA's for one or more of his customers, we'll start to go live for one of his customers I expect. I found licensing information that says, you may freely have form based authenticating users in SharePoint since SP2013, so our point of view to Microsoft is, that we don't have to pay for AWA end-users. Besides, in practice all end users of our customers will pay for an Office365 license as well, given the fact there is no OnPremises hosting of PowerApps and Flow. So it will be gateway hybrid from our O365 to our Premises. An issue still is, a user will pay double if has an Office365 license to himself as well, but I understood from Ingram Micro that there's a discussion going on about that. If it's an external user without PowerApps, we plan to pay only SPLA (datacenter per core, SharePoint FE and SQL-server, no SharePoint CAL's).
Rob
Go to the top of the page
 
GroverParkGeorge
post May 23 2017, 07:36 AM
Post#5


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


Excellent news about hosting.

I guess I am not excited about BCS as an alternative primarily because many, if not most, of the people who have tried AWAs come from the MS Access world. Sharepoint is not top of their lists for development tools. Using SP to support AWAs, in fact, has often been cited as an impediment, not an advantage. That's nothing to do with the technology itself. it's just a matter of asking us to step further away from a comfort zone.

--------------------
Go to the top of the page
 
RobKoelmans
post May 23 2017, 07:57 AM
Post#6



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


Yes, I understand that. Creating a BCS in SharePoint Designer is not exactly partytime. One very convenient thing in AWA was, that you had sort of row based security by the CreatedBy-attribute. It's the only way you can prevent a malicious user from stepping between the AWA browser session and the Access Web Service (and just retrieve any record in any table in the whole of the AWA database). The value is limited because of this issue per se. That's why we use data replication between AWA-databases (and replicate only what users are allowed to see in the destined database). Provides for better scalability too.
Rob
Go to the top of the page
 
alancossey
post May 23 2017, 09:00 AM
Post#7



Posts: 545
Joined: 26-September 05
From: Norfolk, UK


Hiya Rob,
I understand about the probable slowness of the app if I use SharePoint lists, but it is a small app and if I can get it working offline that would be an improvement on what we have now.

As for getting other AWAs exported, none have worked yet. I have given details of my first one going wrong. A second one on the same tenant gives me

Not Supported Creating SharePoint Fields on Lists Unable to support Calculated Column [KeyHolderFullName] of the Access Web App Table [tblKeyHolders] to export to SharePoint List.
Not Supported Transfer Access Web App Table Lookup to SharePoint List Lookup The lookup of Column [KeyHolderID] of the Access Web App Table [tblKeys] is not handled since the lookup source data is not transferrable to SharePoint List.

i.e. it is very similar to the first failure (again I can sort out the Calculated Column OK).

On a second tenant I just get, "Export to SharePoint Lists - Sorry, something went wrong. Please try again later." I did try again later and got the same thing.

0/3 so far. Not impressed. I'll probably open a Service Request.

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
GroverParkGeorge
post May 23 2017, 09:05 AM
Post#8


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


Not to flog a dead horse, but my reaction to the repeated failures was to shrug and move to another, what I consider to be better, approach anyway.

Still another option I wouldn't normally recommend would be to convert your tables to local tables in an accdb, then export those tables back into SharePoint lists. Long way around the block, I guess. And, if I recall correctly, I found that converting them to local tables caused the Lookup fields for RI to be lost, so there'd be an additional amount of work in getting that all back together either in Access or SP after the lists are there.

All in all, I'm lazy. I took the path of least resistance. frown.gif

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:28 PM