Google Drive (1 Viewer)

[Gismo]

Hi all,

Happy new year and hope all the best for all

Quick question if anyone could please assist

I have a database on google drive
I have a shortcut on my desktop to open the DB, instead, it only opens the location in google drive and not the access DB
Please could you advise?

Also on the DB, how would you link tables from the back end to the front end when saved on Google drive?

 

[arnelgp]

if you open it directly from Google drive, does it Opens or
it also ask for you do download it?

it is advised not to use Google drive or dropbox, etc. as Hosting server.
you can google it with lots of hits.

 

[Gismo]

if you open it directly from Google drive, does it Opens or
it also ask for you do download it?

it is advised not to use Google drive or dropbox, etc. as Hosting server.
you can google it with lots of hits.

Yes it requires me to download which I prefer not if possible

I assume I can download to local pc and have the backend linked but then they would not have the latest version should there be any updates

 

[Minty]

You can't host a backend database on google drive and have it work reliably.
Google Drive doesn't support the SMB protocol that access uses to share a file.

And you appear to be asking about sharing the front end which is an even worse scenario?

 

[Pat Hartman]

So, the answer Gismo, is NO, NO, a thousand times NO. You can use Google Drive or other cloud drive ONLY ONE SINGLE USER AT A TIME. And there is no way to control that so if userA downloads the app and userB downloads the app, each are working on the original version. And when each person closes the Access app THEIR version will overlay whatever version is currently there.

Do NOT do this. You cannot reliably control it. You need to use Citrix or RDP. As a stopgap, you could convert the BE to SharePoint but that comes with other issues and will only be satisfactory if you tables are limited to a few thousand rows.

 

[The_Doc_Man]

I would chime in here but they've already sung my song...

A typical cloud-based drive does not support the protocols needed for Access to work from a cloud. If you cannot do true Windows file-sharing from your local PC to the cloud then you can't use Access. The catch is that cloud protocols require you to move the WHOLE FILE at once or not at all. But Access and many other file utilities based on Office will require partial file access.

 

[Gismo]

Has anyone ever used the product from Synology - NAS?
Apparently you just connect it to the internet and it operates exactly as a server via VPN

 

[Minty]

I have a Synology NAS unit. I use it for backups and to replicate Network storage conditions for clients with (and this is the important bit) LOCAL network drives, e.g. internal network shares, not via the cloud.

The SMB protocol works over your local network or a VPN connection, it won't work on a HTTP style Web connection, which is what you would get accessing it over the interwebs.

 

[Gismo]

I have a Synology NAS unit. I use it for backups and to replicate Network storage conditions for clients with (and this is the important bit) LOCAL network drives, e.g. internal network shares, not via the cloud.

The SMB protocol works over your local network or a VPN connection, it won't work on a HTTP style Web connection, which is what you would get accessing it over the interwebs.

Would you advise using this unit to host the access DB or am I not understanding your reply?

 

[Minty]

You can but only for local use, e.g within your LAN environment.
VPN access can work but it's generally unreliable.
Sharing via the internet won't work.

 

[Gismo]

You can but only for local use, e.g within your LAN environment.
VPN access can work but it's generally unreliable.
Sharing via the internet won't work.

I was hoping this could be my solution for a once off purchase and no monthly hosting subscriptions.

 

[Minty]

I'm afraid if this is a commercially used application you should just take on the chin that there will be costs.
We host a number of SQL azure databases and the hosting costs are not huge. ~ £150 a year depending on the power of processing required.

You can run your own SQL Server Express (Free) backend, and provide VPN based RDP access to it, but there is a cost for the RDP seats.
Virtually nothing that is of enterprise/commercial grade and actually works will be free.

 

[mor10]

We offer cloud storage of the back end on a MySQL server, where each user has a local copy of the front end. Many queries had to be updated to deal with the MySQL ODBC that sometimes make a relatively fast MSAccess query take several minutes over the ODBC connection.

 

[Isaac]

Virtually nothing that is of enterprise/commercial grade and actually works will be free.

Agree.
Perhaps one shining exception is simply SQL Server Express - but then again (as you have explained), a suitable hosting server to put it on & allow connections to, needs $

So there are interesting components that can be free, but not the "whole arrangements".

Perhaps the reason I like SSexpress so much is that I have a client who does a lot of data management - but it's not multi user, and RDP'ing into a virtual desktop is perfectly acceptable. I've used SQL Server Express for all his data needs and never come remotely near the size limit.

Honestly I think that in the near future, technology will be improved & developed to solve for the infamously poor connection problems when trying to use Access+VPN+Wifi. Because we in the Access world can tell people "it's unreliable" all day long, but it doesn't change the reality - that more and more and more people ARE working from home, on vpn, and almost everyone on Wifi.

If all we do is tell people they can't work from home on Wifi using VPN to a network, (when that's precisely what everyone is doing, especially post-covid), then Access WILL go to oblivion, because people are not going to drill holes through their walls and string ethernet cords like Christmas lights just because we told them "you need to for my access database".
But I suspect better solutions will continue to develop and make it possible.

 

[CJ_London]

It is important to separate Access the RAD tool for front ends and Access the backend database (ACE). As I've become more adept I've come to think of ACE as being up there with lookup fields in tables, attachment fields, navigation forms et al - looks great initially and works in 'simple' situations but as you become more adept and require more complex solutions you come to realise it's limitations.

Bit like learning to ski. First thing you are taught is the snowplough. Once you have mastered that the next thing you learn is how not to snowplough. You can always fall back on it if you feel you are in too deep - but performance (i.e. speed) goes down the pan. And so much in Access templates and' training manuals' don't show the efficient way of doing things, just demonstrate something shiny.

Lost connections can be identified and using things like begintrans/committrans can help to minimise the damage if the app was halfway through posting a transaction when the connection was lost.

Slow connections are always a problem - this is where web based apps win because a) they will be designed to bring minimal data across and b) the page is usually on the same server as the back end. And what happens if a connection is lost? you get the 404 error. Put access on the same footing (i.e. installed on terminal server or citrix) and use the same methodology of minimum data and performance remains high and lost connections are unlikely to cause an issue.

I think the issue is that Access comes 'free' with Office (most versions) so the mindset is they want the same capabilities as a website, but for free. What is forgotten is it costs to have a website.

So you have Access+VPN+wifi. To address VPN, minimise the data required to be brought across. Base forms on one record using criteria, not a whole table which is subsequently filtered. Be aware of how much data is brought across for subforms, combos and listboxes. Also perhaps change your VPN provider - it may be they don't have the bandwidth you need. Consider moving the BE to SSE and make sure as much data processing is done there and not in Access. For wifi check the connection still exists before starting a transaction and then use begin/committrans to make sure it goes all the way. Have routines to manage what happens if a connection is lost.

If as a result of COVID the company policy is to support WFH then they need to provide the appropriate infrastructure for that to work successfully. That may mean they need a redesign of existing apps to work efficiently in a new environment. No point blaming a car which is designed to run on the road when it doesn't work so well on a railway track.

 

[Pat Hartman]

@Gismo If you have physical computers in the office connected to the LAN, you can use RDP to connect to one of them and work as if you are on the LAN. It doesn't require a server. If the Office PC has a Windows Pro operating system, the RDP license is already included. If not, I think they are ~ $100 each and they are perpetual so they are not a subscription. That is probably the cheapest solution you will find and it actually works very well. You will need to have someone working in the office in case a PC needs to be rebooted or turned back on after a power outage. I've used this method to connect to several clients. I don't remember if the remote PC needs a license also but I don't think it does. I think you can set the RDP session up to allow for local printing and file transfer also.

 

[pekajo]

Ok,
Coming at a totally different angle. If you only want data from the database to be accessed from the internet ie reports etc. you may want to look at this youtube I found.
What this does is make a Google drive look like Hosting so is accessed via the internet. On your computer it will just be another drive like normal.
So on the Access database export the report to the Google drive then other people can pick it up from there via the internet.

Onedrive/Google drive Hosting.

Hi, I have been playing around with MS Access during lockdown (Victoria Australia) and have just discovered that you can set your Onedrive and Google drive to also be hosting. What this then give you is from Access you can directly export what you like to your Local drive and then use a...

www.access-programmers.co.uk

If you are not sure of what I am saying ask a web developer to explain the difference between Google drive and Hosting.
Peter

 

[The_Doc_Man]

@pekajo - The problem with the video (Onedrive/Google) is that it still doesn't solve the Access problem. It all comes down to a simple question.

Does the disk holding your back-end data file support the Server Message Block protocol across the network to the server or workstation on which your front-end app file is running?

If the answer is NO then the only DB data that you can safely share would be reports externally generated by that DB locally (perhaps with DoCmd.OutputTo but there are other ways to do this). Then, in a second step, upload those now-separate files to the cloud-based drive after-the-fact. There would be NO live data sharing. That kind of file distribution would work even with Access.

By the way, the last ten or more years before I retires, my employment was as a contractor with the U.S. Navy Enterprise Data Center - New Orleans office. We WERE the U.S. Navy's Hosting center for most of the states in the USA Southeastern area. We had a lot of network-attached storage, so we WERE the Navy's cloud. I don't need to ask a web developer anything about those terms.

By the way, saying that you have a "hosting" site doesn't guarantee that Access will work across a long-haul network either. Depending on the site's security policy, if SMB is blocked then you don't run Access to that site. The equation is simple: No SMB? No native Access back-end.

 

[pekajo]

You are absolutely correct and no way would I have a BE on OneDrive . This idea is more about web design than Access.
Most websites need up-to-date data and as for me the data comes from Access. Until I learnt this trick I had to create the report/excel and then have an upload to my website now I just export the report/excel etc from Access onto Onedrive and it's all done, people can then see it from my website.
So in short there is no Access either FE or BE on the OneDrive only the output and as I have said this is more of a 'trick' than a process.

Maybe I can add my last project. An Access database holding all client data including purchases etc which is updated daily. From Access they need all states individual reports daily and weekly spreadsheets sorted by product. All in all about 15 reports/spreadsheets. So each state could see the daily data I had to export and upload all these reports onto our hosting site.
Now I have one button that exports all the required reports/spreadsheets to my OneDrive folder (www.Export) now all see them from the website as required. I could email the said reports but there are a number of people who do not want them daily but need access (not MS Access) to them hence on a website.

 

[theDBguy]

You are absolutely correct and no way would I have a BE on OneDrive . This idea is more about web design than Access.
Most websites need up-to-date data and as for me the data comes from Access. Until I learnt this trick I had to create the report/excel and then have an upload to my website now I just export the report/excel etc from Access onto Onedrive and it's all done, people can then see it from my website.
So in short there is no Access either FE or BE on the OneDrive only the output and as I have said this is more of a 'trick' than a process.

Maybe I can add my last project. An Access database holding all client data including purchases etc which is updated daily. From Access they need all states individual reports daily and weekly spreadsheets sorted by product. All in all about 15 reports/spreadsheets. So each state could see the daily data I had to export and upload all these reports onto our hosting site.
Now I have one button that exports all the required reports/spreadsheets to my OneDrive folder (www.Export) now all see them from the website as required. I could email the said reports but there are a number of people who do not want them daily but need access (not MS Access) to them hence on a website.

Hi. Just a quick clarification request, does your website have HTML pages used to navigate to those individual reports? By the way, did you say those reports are exported as Excel spreadsheets?