UNC & Cloud BackEnd

[FuzMic]

Hi friends

I am a small guy stretching the dollar to help the small guys thus i still use msAccess 03 which is fast and simple, essentially i can do almost everything (low traffic) inspite of the caveat about vulnerabilty. Having said this I get to what i am thinking about to day.

My approach is to have split backend in a pc, say "Srv" with a shared folder say" Data" where the database is stored. This data is then accessible from numerous pcs in a wired LAN using UNC code ie \\Srv\Data. I have no problem with this at all.

Now i want to venture to keep the data in a free cloud eg Mega or Google Drive or OneDrive or MediaFire. The question how to i name the cloud folder using UNC convention (without mapping); how to share the folder I presume that each PC must have access to the cloud folder thus overcoming sharing requirement. Any views to help me along. Thanks.

 

[Pat Hartman]

Not a chance. Cloud drives operate very differently from those on a LAN and they CANNOT support shared access to an Access database. PERIOD. The way the sharing works for Access is quite different from what is needed for Word or Excel. There are lots of threads here on the topic with pretty detailed explanations of why this technique does not work.

There is no cheap way of putting Access on the web but there are simple ways. Remote Desktop and Citrix both work very well can be implemented with either no changes to the app or perhaps some small changes. If you have desktops in the office, you can set up RD to allow people to remote into their office PC and work as though they were on your LAN. Professional versions of office come with the seat licenses you need to do this. Otherwise they are ~ $100 each. Another more costly implementation is to run a server version of RD. This instantiates a "desktop" for each user on the server. I'm not sure how much the license is for this but it is probably more plus you need an actual server. Citrix, if you host it your self is a little more sophisticated than RD but the concept is the same. You instantiate "desktops" on the server.

You can use a VPN but if the BE is Jet or ACE, the application will be excruciatingly slow. You would need to convert the BE to SQL Server and possibly change a lot of the application to use good client/server techniques if it doesn't already and it still won't be as fast as you'd like but it will be usable.

Any other method requires changes to the application and more software. If your tables are no more than 5,000 rows, you might be able to use SharePoint to host the BE and connect the Access FE to the SharePoint lists. Again, you need a server and SharePoint licenses. There are third-party services to host SP but I don't know what they charge per seat.

There are also third party options for RD and Citrix but they are fairly pricy for a small business and run ~ $40-$50 per seat per month but they do the maintenance and setup so you don't need technicians to do it for you.

 

[gemma-the-husky]

@FuzMic . I tend to use A2003 as well. There's not much I really need in later versions, although the RTF text box controls would be pretty, and pretty useful.

The issue to do with the cloud is nothing to do the the Access version though. It's just the nature of access. Each user has to be able to change parts of the backend database, and that just isn't the model with shared cloud files. That's based on synchronizing shares, and each user has a mirrored copy of the cloud on their own PC.

So you actually edit/work on the PC copy, and then the whole file updates to the cloud, for the next user. Rather than concurrent users, it's serial single users.

@Pat Hartman has provided some solutions. You can host a SQL Server back end in the cloud, but not Access.

 

[The_Doc_Man]

@FuzMic - Pat has given you some good alternatives. I will belabor one point. The reason that "Access" and "Cloud" don't appear in the same sentence very often (other than in a negative way) is that Access requires, but Clouds do not support, the SMB protocol that is the heart and soul of Windows-based file and printer sharing. SMB, or Server Message Block, allows an app to look at a part of a file over a LAN. Cloud systems only give you whole files. If you want to update the file, SMB lets you be selective. Cloud systems are "all or nothing at all" for their updates. If you think about circuitry, Cloud sharing is SERIAL access. SMB sharing is PARALLEL access. Nowhere near the same.

 

[oleronesoftwares]

Now i want to venture to keep the data in a free cloud eg Mega or Google Drive or OneDrive or MediaFire

it might be possible, but considering the age of access 2003, i wouldn't suggest doing this, it's likely if you achieve it, you might be forced to move to a more recent access version to maximise using the cloud storage

 

[Gasman]

it might be possible, but considering the age of access 2003, i wouldn't suggest doing this, it's likely if you achieve it, you might be forced to move to a more recent access version to maximise using the cloud storage

I think you will find it matters not a jot what version of Access you have, but the way that the cloud services work?
I have never tried, but if I was going to, I would certainly take the advice given here (time and time again) not to do it, if you need to share.?
If it is just one user, then it might work?, but you may as well have it local and faster in that case surely?, unless access from anywhere is required?

 

[Pat Hartman]

@oleronesoftwares - Apparently you haven't read any of the threads from people who ignored the advice or just did it without coming here first. Doc has pointed out very clearly the difference in how the cloud drives work and why that prevents you from sharing an Access BE or even monolithic db on one of them. Did you not understand that? He can probably post a link to one of the other threads where he goes into much more detail. As Doc said, it has nothing to do with the version of Access since they all handle sharing essentially the same way with a lock file - .ldb or .laccdb that opens in the same folder as the BE. Since the lock file would open on the local drive, NO ONE ELSE COULD EVER SEE IT so no other open copy of the db could ever be aware of any other open copy of the db. When you close the database the cloud version is OVERWRITTEN so any changes you make to the database REPLACE the entire cloud version clobbering the version that your coworker just updated. Bye-Bye coworker's data.

You might be able to work out some convoluted method of ensuring that only a SINGLE user has the db open at any time. But, none that we've come up with actually solve the problem since mistakes happen and the methods all rely on people to not make mistakes. I wouldn't put my company's data in that jeopardy.

 

[oleronesoftwares]

Apparently you haven't read any of the threads from people who ignored the advice or just did it without coming here first. Doc has pointed out very clearly the difference in how the cloud drives work and why that prevents you from sharing an Access BE or even monolithic db on one of them. Did you not understand that? He can probably post a link to one of the other threads where he goes into much more detail. As Doc said, it has nothing to do with the version of Access since they all handle sharing essentially the same way with a lock file - .ldb or .laccdb that opens in the same folder as the BE. Since the lock file would open on the local drive, NO ONE ELSE COULD EVER SEE IT so no other open copy of the db could ever be aware of any other open copy of the db. When you close the database the cloud version is OVERWRITTEN so any changes you make to the database REPLACE the entire cloud version clobbering the version that your coworker just updated. Bye-Bye coworker's data.

i have shared in another post earlier this month, how i was able to use ms access with cloud storage has back end.

 

[oleronesoftwares]

I think you will find it matters not a jot what version of Access you have, but the way that the cloud services work?
I have never tried, but if I was going to, I would certainly take the advice given here (time and time again) not to do it, if you need to share.?
If it is just one user, then it might work?, but you may as well have it local and faster in that case surely?, unless access from anywhere is required?

i have shared in another post earlier this month, how i was able to use ms access with cloud storage has back end.

 

[Gasman]

But were you sharing that with others?

 

[oleronesoftwares]

But were you sharing that with others?

yes, a small team of 8 users

 

[Pat Hartman]

And they haven't yet clobbered each other yet. You are very lucky. Please post a link so we can see this technique.

 

[oleronesoftwares]

And they haven't yet clobbered each other yet. You are very lucky

it isn't about luck.

I designed the form as if am working on a .net application.

Hence all forms are unbound.

By default a select query is used to populate the forms.

The insert query or update query is triggered by a command button

 

[Pat Hartman]

Why do you think that solves the problem? The way cloud drives work, is when you open the file, a COPY is loaded to your local PC. Updates happen on your local COPY. when you close the app, your COPY is put back into the cloud folder and overlays whatever was there before. So if at 1 PM you open the file, it gets downloaded. At 1:01 someone else opens the file. They get the same copy of the file that you got. You do your updates with your unbound forms. When the application finishes and the .accdb closes, your COPY is put back on the server. Then 10 minutes later, the user who started with the same original data that you did puts back his COPY. It overlays your COPY and all your data is lost. Your changes are overlaid by his or revert to their previous values if the second user didn't change those records.

 

[oleronesoftwares]

With MDB, ACCDB Viewer and Reader this does not happen "a COPY is loaded to your local PC"

 

[The_Doc_Man]

Ah, but wait! There is a loophole here. With ACCDB VIEWER AND READER, do any of those 8 users ever UPDATE anything? If not, then the problem becomes moot. If there are no updates, there is no clobbering.

 

[oleronesoftwares]

hi @The_Doc_Man , yes they can update, the name accdb viewer and reader confuses people, all that is needed is rights to be granted them from google drive.

Apart from accdb viewer there are also other ways/apps that allow for sharing access applications via google drive

one of such other apps is https://fileproinfo.com/file-editor-online/accdb#

there are several other tools.

We have to look at things from microsoft's perspective, though they have pushed .Net more into the market, they know that ms access revolutionised DBMS over the years, and the future for now is cloud computing, so there is no way ms access will not be editable in year 2021 on the cloud

 

[FuzMic]

Thank you very much comrades .. lots to chew. Appreciate Peace2u

 

[The_Doc_Man]

hi @The_Doc_Man , yes they can update, the name accdb viewer and reader confuses people, all that is needed is rights to be granted them from google drive.

Apart from accdb viewer there are also other ways/apps that allow for sharing access applications via google drive

one of such other apps is https://fileproinfo.com/file-editor-online/accdb#

there are several other tools.

We have to look at things from microsoft's perspective, though they have pushed .Net more into the market, they know that ms access revolutionised DBMS over the years, and the future for now is cloud computing, so there is no way ms access will not be editable in year 2021 on the cloud

Thank you for that fileproinfo link. It would appear that they are "cheating" by placing the file in a "sandbox" and using HTTPS as a way to talk to the cloud server. I would more easily believe that HTTPS is served by the Cloud than SMB. The sandbox web page apparently does something locally. Is it acting as though you were in an RDP session, except that it is using HTTPS to do what it does? Does that method allow for simultaneous users updating the DB? And in so doing, does it appear that they are using the basic Access GUI (or an app created through the GUI?)

 

[Sun_Force]

hi @The_Doc_Man , yes they can update, the name accdb viewer and reader confuses people, all that is needed is rights to be granted them from google drive.

Apart from accdb viewer there are also other ways/apps that allow for sharing access applications via google drive

one of such other apps is https://fileproinfo.com/file-editor-online/accdb#

there are several other tools.

We have to look at things from microsoft's perspective, though they have pushed .Net more into the market, they know that ms access revolutionised DBMS over the years, and the future for now is cloud computing, so there is no way ms access will not be editable in year 2021 on the cloud

@oleronesoftwares as far as I see, this site is a on-line editor for Access. Those who don't have Microsoft Access installed locally, can upload, edit and download their databases.
How did you shared a database between 8 users? were all the users able to view/update the same database?


thank you.