Placing my customers individual front ends in Sharepoint Synced Folder (1 Viewer)

[medihub_guy]

Good day everyone,

I'm new to this forum.

I wanted to talk about my current set up and your opinions.

So I have a split database: access front end, sql back end

I synced my sharepoint folder on each of my customers computer.

I encrypted the folder so they dont have access to it.

I then created individual folders for each client.

From there, I created shortcuts on their desktops so they can access the front end.

With this design, I can update the individual front ends through the sharepoint synced folder.

Has anyone tried this before? What are your thoughts?

 

[The_Doc_Man]

As long as everyone's FE remains as a local/private copy and the BE is an SQL engine that is on a LAN rather than a WAN, you should be OK. Set up so that Sharepoint doesn't try to update the FE from auser's local copy. (Because if it did, you would update the Sharepoint user copies with bloat as part of the picture.)

 

[medihub_guy]

Hi there Doc_Man.

Thank you so much for your feedback!

So the thing is, the customer doesnt really have access to the local file as it will be running from the synced sharepoint folder. They will be able to access it via desktop shortcut on their respective desktops.

With the folder encrypted and they have no access to the original accde file, I shouldnt have anything too wacky happening.

Are you aware of anyone taking this approach? i.e. using a synced sharepoint folder as a network for their front end database application?

 

[theDBguy]

Hi @medihub_guy. Welcome to AWF!

Are you saying each user's shortcut to the FE point to the same physical copy of your ACCDE file?

 

[medihub_guy]

Hi @medihub_guy. Welcome to AWF!

Are you saying each user's shortcut to the FE point to the same physical copy of your ACCDE file?

Hi DBguy!

Thank you for the warm welcome!

No. They will all have their own respective copy of the accde file in separate subfolders in the synced sharepoint folder.

Now I have 10 customers, each one of them has a separate subfolder in the synced sharepoint folder with their respective accde file.

This allows me to update their accde files individually.

 

[theDBguy]

Hi DBguy!

Thank you for the warm welcome!

No. They will all have their own respective copy of the accde file in separate subfolders in the synced sharepoint folder.

Now I have 10 customers, each one of them has a separate subfolder in the synced sharepoint folder with their respective accde file.

This allows me to update their accde files individually.

Hi. Thanks for the clarification but does this mean all your paid users are sharing the same BE?

 

[medihub_guy]

Hi. Thanks for the clarification but does this mean all your paid users are sharing the same BE?

Yes they do! Financial info and other sensitive data to that customer is hidden through setting the specific customer number in the query criteria.
This design is was chosen as it provides the foundation for future interoperability between the pharmacies as that is what is being encouraged by healthcare authorities.

 

[theDBguy]

Yes they do! Financial info and other sensitive data to that customer is hidden through setting the specific customer number in the query criteria.
This design is was chosen as it provides the foundation for future interoperability between the pharmacies as that is what is being encouraged by healthcare authorities.

Interesting... Thanks!

 

[The_Doc_Man]

It is possible that this is OK,... but it has one minor drawback. I'll mention it but then back away since it is MINOR in scope.

As long as the files remain single-user/private (even if not local) you are OK on file locking collisions. But if you are using the FE remotely, the file locking rule is that the file's host is the lock manager - which means you are using remote locking. The issue is that network-based locking is anywhere from 10 to 100 times slower than local file locking. If you have a slow (or worse, overloaded) network, your app will be more sluggish than it would be if you could directly download to the user's local computer. Like I said, it is a minor issue, but you should be aware that your configuration is giving you a persistent network speed hit.

If you are doing this for security reasons and have an ACCDE file in this mix, the question I would ask is "which file needs to be secured better?" I would think that securing the data would be better. Securing the code using an ACCDE file is already pretty good since you can't see the clear-text code in that case. I wonder if there is a slight overkill going on that makes your life harder than it needs to be.

Don't forget that Access is still on your end-user's computer and IT has to do all sorts of file operations - plus you've added folder encryption which means "encryption by software" which adds to the load that would rob your system of performance. I'm offering comments here, but don't take them to mean "you way will never work." I'm not saying that. I'm saying "your way might be working harder than necessary."

 

[medihub_guy]

It is possible that this is OK,... but it has one minor drawback. I'll mention it but then back away since it is MINOR in scope.

As long as the files remain single-user/private (even if not local) you are OK on file locking collisions. But if you are using the FE remotely, the file locking rule is that the file's host is the lock manager - which means you are using remote locking. The issue is that network-based locking is anywhere from 10 to 100 times slower than local file locking. If you have a slow (or worse, overloaded) network, your app will be more sluggish than it would be if you could directly download to the user's local computer. Like I said, it is a minor issue, but you should be aware that your configuration is giving you a persistent network speed hit.

If you are doing this for security reasons and have an ACCDE file in this mix, the question I would ask is "which file needs to be secured better?" I would think that securing the data would be better. Securing the code using an ACCDE file is already pretty good since you can't see the clear-text code in that case. I wonder if there is a slight overkill going on that makes your life harder than it needs to be.

Don't forget that Access is still on your end-user's computer and IT has to do all sorts of file operations - plus you've added folder encryption which means "encryption by software" which adds to the load that would rob your system of performance. I'm offering comments here, but don't take them to mean "you way will never work." I'm not saying that. I'm saying "your way might be working harder than necessary."

I did not think of that. Thank you so much for bringing this issue to light. Maybe I can do a control based experiment with this to see just how much performance I'm losing.
As mentioned, the main purpose of keeping the accde file in the synced folder was not entirely about security. It was more of an easy and dare I say "clever" way to be able to update the customers copy without having them involved by downloading the new updated copy and deleting the old one. I know there are 3rd party solutions here; but all but one (app Launcher for access) does not give you the option of updating over the internet as opposed to a local network.

 

[The_Doc_Man]

updating over the internet

Whoa! If you are running Access on the user's local system on a front-end that is on a general internet OR a WAN setup, you SERIOUSLY risk corruption. Yes, the back-end that is on an SQL engine might survive internet connectivity, but the FE will be subject to network drops that occur FAR more often on an internet connection than on an intranet (LAN) connection. Because Access uses the Windows Server Message Block protocol, you risk having issues with network stability. Now, it is POSSIBLE that you have a fairly stable internet connection for most of your users, but you need to consider that internet connections can be multi-hop and every hop is another potential connection-breaker. So if you have some really distant users, you might find that their usage isn't even close to optimum.

You have THREE locations involved in this configuration if I am reading it correctly. The BE is on some SQL machine. The FE is on a user's private folder on a SharePoint server. But the user launches a copy of MSACCESS.EXE from his/her local machine. The Access-to-FE link is at risk in this configuration. I am not as concerned with the FE-to-BE link in this case. I'm not saying it would NEVER work or that it would ALWAYS be reliable. I'm guessing that FE stability is usually pretty good... until the day it isn't.

The configuration that USUALLY is recommended for this case is that you have a properly licensed copy of MSACCESS.EXE on the SharePoint server and opened via Remote Desktop Protocol. If you can RDP to the SharePoint server then you can run Access so that it is local to the FE file, and the BE on the SQL server should be robust enough. This is not a monetarily cheap solution as you would need a properly licensed multi-user copy of Access and some kind of RDP package, such as but not limited to CITRIX.

You do what you have to do with the resources available, so go on using this method... but if the FE starts to inexplicably get flaky on you, then remember my comments.

EDIT: If I misunderstood the configuration, I apologize for alarming you. But if I am correct, then my warning stands.

 

[medihub_guy]

From what I understand about synced sharepoint folders, they allow users to continue working on files in its folder even if the internet is down. Therefore, internet connectivity shouldnt be an issue. Furthermore, only one user should be editing the unique front end accde file anyway. Therefore, there should not be any clashes with data. Also, when I said update over the internet, I meant periodic updates to the design of the front end for me to push to the users. With the synced sharepoint folder, all I have to do is schedule down time with the client then drag and drop the new front end file into their respective folder. This literally takes 5 seconds to do.

@The_Doc_Man I want to ensure I understand your concerns as I can tell you understand access/database design a whole lot better than I do.

This link kinda sums up what took place with my application: https://sharepointmaven.com/how-onedrive-sync-works/

 

[medihub_guy]

I consulted with our robot overlords (chatgpt).

@The_Doc_Man it seems as your concerns are valid. But I'm still not too convinced of a major issue here.

Here is our conversation:
https://chat.openai.com/share/6e6a5ebd-0049-40dd-a530-2944e0cdd685

 

[medihub_guy]

I'm now at the point of considering cost between the synced sharepoint folder and remote desktop services.. then compare the risk and benefits.

 

[theDBguy]

From what I understand about synced sharepoint folders, they allow users to continue working on files in its folder even if the internet is down. Therefore, internet connectivity shouldnt be an issue. Furthermore, only one user should be editing the unique front end accde file anyway. Therefore, there should not be any clashes with data. Also, when I said update over the internet, I meant periodic updates to the design of the front end for me to push to the users. With the synced sharepoint folder, all I have to do is schedule down time with the client then drag and drop the new front end file into their respective folder. This literally takes 5 seconds to do.

@The_Doc_Man I want to ensure I understand your concerns as I can tell you understand access/database design a whole lot better than I do.

This link kinda sums up what took place with my application: https://sharepointmaven.com/how-onedrive-sync-works/

Hi. Thank you for posting this link. I have been wondering since your first post what a synced SharePoint folder was. Now I understand it's actually OneDrive. I grew up with those two as separate things, but I guess they're more integrated now. Cheers!

 

[The_Doc_Man]

From what I understand about synced sharepoint folders, they allow users to continue working on files in its folder even if the internet is down.

Yes, but then you move attention to a local-to-WAN back-end. Since the BE is SQL-based, you are LESS likely to have issues, but there are issues in the way you write queries that use an active SQL engine across a network. Our member Pat Hartman has written many articles on dealing with queries across an ODBC connection including ways to minimize impact. Her expertise in this matter is considerably greater than mine.

When the SharePoint is "up" then it sounds like your connection is reasonably localized. But when it is down, having the FE local means you need to have taken steps that minimize your traffic i.e. let the BE active engine do the heavy lifting.

I'll toss in one more comment, and again, with the configuration you describe it is possible that this won't be an issue - but if I don't mention it, I would be a poor advisor. OneDrive is NOT compatible with Access. It uses whole-file protocols to copy data from point A to point B (and back again.) Access uses ONLY the Server Message Block protocol (SMB) to handle parts of files. As long as you can use the local copy and just have OneDrive sync it occasionally, you are OK. However, if you EVER have to run Access directly on the remote FE copy, you have a big problem. It won't work correctly. When you were talking SharePoint, that is known to work with Access in a limited way. But OneDrive is very different.

Just as a sanity check, how far apart is the SharePoint server from the SQL server? Is that at least a short, local hop? Because the FE-to-BE link is the critical link in performance and reliability.

 

[KitaYama]

I consulted with our robot overlords (chatgpt).

@The_Doc_Man it seems as your concerns are valid.

It really seems so weird (to me) that someone must check with a robot to be sure that the comments of an expert with more than 30 years of experience is valid.

 

[medihub_guy]

It really seems so weird (to me) that someone must check with a robot to be sure that the comments of an expert with more than 30 years of experience is valid.

@KitaYama it was a joke and I'm certain @The_Doc_Man didnt take it as an insult. If he did that was not my intention. Him and I were having dialogue of which I thanked him for his insight on the matter. No need to come here and make things awkward. Please dont twist my words into something it is not. And technically speaking, artificial intelligence is smarter than any human being on the face of this earth. I'm sure even @The_Doc_Man appreciates that too.

 

[medihub_guy]

It really seems so weird (to me) that someone must check with a robot to be sure that the comments of an expert with more than 30 years of experience is valid.

refer to my previous comment:

@The_Doc_Man I want to ensure I understand your concerns as I can tell you understand access/database design a whole lot better than I do.

 

[KitaYama]

I'm certain @The_Doc_Man didnt take it as an insult.

It's not a matter of being an insult or not. And I don't think it was.
I meant how people believe an AI is better than a human mind. Your last comment shows that I was correct.
"And technically speaking, artificial intelligence is smarter than any human being on the face of this earth."

No need to come here and make things awkward.

It's an open forum and I think everybody is free to express what he thinks. I didn't make anything awkward. I only said what I thought.