Linked Table of MS Access: Sharepoint or OBDC (phpMyAdmin) (1 Viewer)

[DarioDiGregorio]

Hello, this is my first discussion. I hope not to break any rules: I'm Italian and my English isn't the best. I would like to know some notions/information about the best choice for creating linked tables for MS Access that need to work online.

Sometimes I use SharePoint, but there are some issues: it's slow (depending on traffic?) and I've done some tests with ODBC (using the hosting of my personal website): it's fast, but there's a big problem. If there's no internet connection, Access doesn't work.

However, with SharePoint, the database continues to work with a negative ID.

What's the most "professional" solution?

Is there a possibility to work with ODBC tables in offline mode?

 

[GPGeorge]

SharePoint's primary advantage, IMO, is the ability to work offline and resynch data following restoration of the internet connection. In addition to being slower, there are potential limitations in the numbers of records that can be managed in SharePoint lists. I don't think there are any hard limits; however, it's probably not going to support more than 30,000 to 80,000 records in a SharePoint list, and probably closer to the lower estimate. If you have large recordsets, that could become a problem.

With ODBC linked tables (in MySQL or SQL Server, for example), there are other limitations. One of those is, as you noted, that you can only use the tables when the connection is available. There is no offline caching to support resynching, as there is with SharePoint. One of the other primary limitations, which is performance over the internet, is shared with SharePoint to some degree, so that may not be a differentiating factor.

In short, your choice depends on your requirements. I would not use the term "professional" vs "non-professional" in this context. Both are professional solutions, with different advantages and disadvantages.

What is the use case you have and why does the ability to work offline matter so much?

 

[DarioDiGregorio]

First of all, thank you for the information. My question is a general one, not related to any specific case. I often hear discussions about how SharePoint might not be the most suitable choice for a program that needs to be maintained for many years, but I have never found adequate documentation that explains the reasons behind this.

Additionally, in my opinion, the ability to work offline is a huge advantage for those situations where, even if only for a short time, the internet connection is lost.

Not to mention the economic factor, in which SharePoint once again prevails.

 

[GPGeorge]

Spoiler:

First of all, thank you for the information. My question is a general one, not related to any specific case. I often hear discussions about how SharePoint might not be the most suitable choice for a program that needs to be maintained for many years, but I have never found adequate documentation that explains the reasons behind this.

Additionally, in my opinion, the ability to work offline is a huge advantage for those situations where, even if only for a short time, the internet connection is lost.

Not to mention the economic factor, in which SharePoint once again prevails.

How does SharePoint have a cost advantage? As compared to what other possible Back End database?

 

[DarioDiGregorio]

What I think could be the result of misguided experiences. However, typically, if you have a Microsoft 365 subscription, you already have the SharePoint app (often left unused).

On the other hand, it's less common for a user to have a hosting subscription.

Are there solutions cheaper than SharePoint?

 

[GPGeorge]

What I think could be the result of misguided experiences. However, typically, if you have a Microsoft 365 subscription, you already have the SharePoint app (often left unused).

On the other hand, it's less common for a user to have a hosting subscription.

Are there solutions cheaper than SharePoint?

I was just hoping for context, actually. It depends in part on whether you are part of a larger organization, I would think, in which there is a SharePoint admin function to manage it on behalf of employees.

SQL Server Express, which is one alternative, is a free download. It could be installed on-premises for an organization in which that configuration would be appropriate.

SQL Azure can be a inexpensive as ~$5.00 US per month as an entry point.

It seems to me that it's difficult to discuss the relative costs without the context in which the application is deployed.

Certainly for ease of deployment and cost as well as the offline functionality, SharePoint has some advantages. On the other hand, it may be worth an additional small cost to deploy a more robust database engine, like SQL Server Express, or one of the open source databases like Postgres or MySQL. It just makes me nervous to try to pick out one "best" solution for all situations.

 

[Isaac]

I've worked extensively with Access to Sharepoint lists and eventually decided the glitches were way too costly for the 'benefits'. Any list with a People field in the row cannot be updated, and updates would fail very frequently with vague errors of all kinds. I may be roundly thrashed for posting this, but I don't recommend using Access with Sharepoint any more.

Use just about anything else, including sql server express if possible

 

[NauticalGent]

I've worked extensively with Access to Sharepoint lists and eventually decided the glitches were way too costly for the 'benefits'.

Same experience. Even when I used Views, the performance boost was marginal and the offline caching was spotty at best. We trashed it and went back to our previous setup. Since we are using Citrix, it has been good so far, but when it's offline, there is nothing to do except take an early, extended lunch!

 

[Isaac]

It seems like the list of reasons why linked sharepoint tables become non-updateable is long, and includes a big "can't for the life of me tell why" category LOL

 

[DarioDiGregorio]

SQL Server Express works online, but i need an hosting? Is this correct?

 

[Pat Hartman]

What I think could be the result of misguided experiences. However, typically, if you have a Microsoft 365 subscription, you already have the SharePoint app (often left unused).

On the other hand, it's less common for a user to have a hosting subscription.

Are there solutions cheaper than SharePoint?

When you use Sharepoint - EVERY user MUST have a SharePoint subscription. SharePoint only comes with expensive subscriptions.
When you use a RDBMS solution, only the developer needs a subscription. Every other user can simply access the database directly.

Also, how good is the resync from off-line use when you have multiple people updating the same record in a SharePoint app? I wouldn't trust it to figure out what a record should look like.

 

[GPGeorge]

SQL Server Express works online, but i need an hosting? Is this correct?

Again, context. Who is going to use this relational database application? How many people? Where are they located?

If you need the remote hosting solution, then yes, that's an option. But without context, it's hard to be specific.

Actually, @Pat Hartman, it's been a long, long time since I saw it in action, but IIRC, the SharePoint synch process does offer potential matches where there are conflicts so the user can decide who wins the update.

 

[Pat Hartman]

Actually, @Pat Hartman, it's been a long, long time since I saw it in action, but IIRC, the SharePoint synch process does offer potential matches where there are conflicts so the user can decide who wins the update.

I was pretty sure it would but people see the "off line" feature and think it is magic. It isn't. It is viable when you are adding data and userA will never be updating records that userB updates. The whole thing becomes significantly more complex when any user can update any record. Because, in that case, the computer can't decide whose version wins. A human has to look at the changes and accept one or merge them. If two users update different fields, it is possible that you want the result to merge the changes rather than have last in win.

 

[GPGeorge]

I was pretty sure it would but people see the "off line" feature and think it is magic. It isn't. It is viable when you are adding data and userA will never be updating records that userB updates. The whole thing becomes significantly more complex when any user can update any record. Because, in that case, the computer can't decide whose version wins. A human has to look at the changes and accept one or merge them. If two users update different fields, it is possible that you want the result to merge the changes rather than have last in win.

Way better explanation than mine.

 

[Pat Hartman]

Access used to support a replicate feature. When the Access team took over Jet and made it into ACE, they dropped the feature along with the old security model. Both features are nightmares to code and have serious gotchas. I do wish they had kept the security feature but made it cleaner so that people wouldn't mess it up so regularly. But, that was 14 years ago

 

[The_Doc_Man]

I do wish they had kept the security feature

The problem with the Ac2003 security feature WAS the security feature. The object-level security features were poorly written and very porous (security-wise), i.e. easily penetrated. I don't recall if they were originally part of Cirrus (Access's previous name when MS bought it out.)

At the time that the Access object-level security features went away, I was working with the Navy and had access to the government security bulletins and discussions on using it. I don't recall what the specific API module was called (officially) but it had some rude & ugly unofficial names and was listed as a major security weakness in any system that was net-exposed. The Navy recommended against its use for anything classified at any level other than UNCLASSIFIED. Apparently MS finally acknowledged that it was unfixable given the list of flaws because they gutted it after 2003. Or was it 2007? Long enough that I have forgotten some of the details.

Anyway, since Access COULD be used with a more secure back-end, it still had life in it. I was allowed to use it on a secure sub-net but not on our out-facing network.

 

[Pat Hartman]

but made it cleaner so that people wouldn't mess it up so regularly.

I guess you didn't read that part of the sentence. There were certainly flaws with the security implementation but without having access to the code base or the specific hurdles the conversion from Jet to ACE encountered, I wouldn't know how hard the solution would be.

What I do see though is that the MS Access team doesn't actually see Access as a platform for the development of applications. They see Access databases as documents. That is why they do not ever provide tools that would help us to create applications like an integrated way to lock down an app. Instead, we have to work on it piecemeal. Or having the choice of no menus or all menus is pretty silly since they should be able to separate the interface items like printing from the development options that only programmers should have access to.

 

[DarioDiGregorio]

Is the connection with SharePoint slower compared to ODBC?

 

[GPGeorge]

Is the connection with SharePoint slower compared to ODBC?

It depends...

I don't think it's really possible to make definitive statements without significant testing of both alternatives in your environment, using your data and your table designs, and your interface.

My hunch is that ODBC-linked tables are likely to be more performant as the amount of data in the tables grows.

 

[Isaac]

Removing adp's was one of the worst things Microsoft has ever done to Access, unforgivable.