Dataverse or Sharepoint list for Access

[Rexter]

I am developing an Access DB and looking at using either SharePoint list or Dataverse tables as the data table sources. I was told that no remote data source is suitable for Access. I find this somewhat confusing since Access has an online connector to both SharePoint and Dataverse. 

Any advice on this? 

[#2]

@Rexter 

First, either whoever told you that about remote data sources was totally uninformed, or perhaps you misunderstood what was said. Access works well with a number of remote data sources, although good design is critical to doing so successfully. In the narrow sense that a higher level of professional development is needed when working with remote data sources, one might say that it is harder.

Don't use SharePoint unless you have a very small application. A small number of tables and fewer than 8 or 10 thousand records in tables. Some larger tables are possible, but performance is less than ideal.

Dataverse is relatively new for Access. You'll find, I think, that it is more challenging for that reason. However, it is probably more robust than SharePoint.

If it were me, though, I'd look at SQL Azure or another remotely hosted SQL Server for the back end. 

Given those considerations, you need to perform your own due diligence with each.

[#3]

Hi,

Thanks for the clarification as I was told the wrong information by an Access user. I am very familiar with SharePoint lists and have a basic understanding of Dataverse. I found the GUI and workflow for the latter very clunky. My organization is a non-profit so there is no budget for the resources to support an SQL backend. The database I am building is for a small team - about 30 or less users. I don't think I will have more than 10 tables and less than 3,000 records, so SharePoint lists should do the trick.

Thanks for your response.

[#4]

SQL Server Express is free, if you have a way to install it. A cheap Windows hosting site that would support a SQL Server database could be as little as $5 -- $8 a month US. A SQL Azure database would be in that same price range.

[#5]

Hi George,
As it turns out, a SharePoint list will not work as a data source for Access is one is attempting to create forms from the SharePoint tables. The SP list randomly duplicates data when using an Access form for the front end. For example, I have a CLientName table with one John Smith. Creating an Access form from the ClientName table randomly duplicates John Smith several times. I haven't tried Dataverse but, from what I read, DV is still not fully developed, so I have my doubt's if it will serve as a remote data source. SharePoint probably worked differently when you used it as a data source for Access. It definitely does not work now.

[#6]

"The SP list randomly duplicates data when using an Access form for the front end. For example, I have a CLientName table with one John Smith. Creating an Access form from the ClientName table randomly duplicates John Smith several times."
While I am not a fan of SharePoint lists anyway, and said as much in a previous response, this sounds unlikely to me.

" Creating an Access form from the ClientName table randomly duplicates John Smith several times"

Creating a form can't duplicate records in a table. Something else is going on, and since we can't see the application in context, we couldn't guess what that is.

Perhaps, though, you could share some screenshots to illustrate the problem.

[#7]

Hi George,

I am moving on from SharePoint list as an Access data source because of another issues, I cannot rename Sharepoint ID columns which is really not optimal for relational database development. I will look at Dataverse, but I am hesitant to use a platform still in beta development.

[#8]

I fully agree that SharePoint lists are not optimal for a relational database application. I remain curious about the specific problem you encountered.

With regard to Dataverse, I have to sort of agree and disagree. The disagreement part is considering it "beta". I think it's past that point although the whole environment is still very new and not well understood by many Access developers. If you have a choice, it is not the preferred option for Access developers yet. There is one positive to note about Dataverse, and that it the ability to support offline synching. In a situation where some part of data entry must be done via a mobile device, such as an inventory in a warehouse, the ability to continue working without a wireless connection can be very useful.
However, absent that "mobile hybrid" aspect, there isn't yet enough known about Dataverse, in my opinion, to make it a choice over Azure SQL or a hosted SQL Server database.

I guess I mostly want to avoid blanket dismissal of most options because there are always specific situations where that unique set of features makes one of those options really attractive.

[#9]

I connected Access to an existing SharePoint list which seemed to introduce the duplication errors. I deleted all the duplicate records and that seems to be working. Although that may have done the trick, I am not confident it won't happen again, hence my hesitation to use SP lists or Dataverse, I don't know if these MS cloud data sources are reliable or not for Access. Although, I am not expert in SQL server, I will see if my organization will consider purchasing an SQl cloud service. Any suggestions on that?

Thanks