Front End/Back End and Google Drive

[bcarter17]

Hello all,

Access 2007 user.

I have a setup question for a front end/back end program we created. Currently, we are using a stand alone shared hard drive for the back end each workstation has a copy of the front end. To make it more available offsite, we would like to move the back end to our Google Drive. We have tested and this does work. But there is a drawback....



At our work location (rock quarry), internet access can become unavailable at times. If we are loading trucks and need to make weight tickets, this would bring us to a stop if the internet went down. Not a good situation.

My question is, has anyone ever developed a setup where if the connection to the backend went down, the front end could "store" records and then push them through later? We would need to have the front end remember the last ticket number created so it could continue with the next number in the holding table. It feels like with internet reliability still being an issue that someone may have built a process like this and if so, how did you set it up? How does it recognize the internet is down and to use the front end storage until it could be switched back?

Any and all help appreciated!!!

[Welshgasman]

AFAIAA Google drive and OneDrive are not compatible with Access as BE locations?

[Micron]

You might solve the ticket number problem by prefacing it with a character(s) that uniquely identify the station/user/fe or whatever you want to call it. Then it won't matter if the number portion duplicates when you finally get to upload the data to the be. I agree - Access with One Drive is a non starter for a shared database. Your other option would be to use a remote access application, such as Citrix. However, that won't help if areas do not have a wired (LAN) connection that connects to the server where the back end resides. For example, at home, I could use Citrix to log into my work network and remotely use a shared db. However, I was wired to my router, which was wired to the internet, which was wired to the LAN network that had the back end on it. So no WIFI, because WIFI and Access is not a good mix either.

[mike60smart]

This is what Copilot says:-
Using Google Drive to host the BackEnd of a Microsoft Access database is technically possible, but it comes with significant limitations and risks. Here's a breakdown to help you decide whether it's suitable for your needs:


✅ What Can Work
If you're using Access in a single-user environment, or if you're simply backing up your BackEnd file to Google Drive, then:
• You can store the BackEnd file in your Google Drive folder.
• Access will treat it like any other local file.
• You can automate backups to Google Drive using VBA or third-party sync tools.


⚠️ What Doesn't Work Well
For multi-user environments or live shared access, Google Drive is not designed to handle the simultaneous file locking and record-level concurrency that Access requires.
Problems you’ll likely encounter:
• File corruption due to simultaneous access.
• Slow performance from cloud sync delays.
• Record locking issues, especially when multiple users try to edit data.
• No true real-time syncing—Google Drive syncs files, not live database connections.


��️ Better Alternatives for Shared Access
If you're looking to share your BackEnd across multiple users, consider these options:



�� Pro Tip for Your Setup
Since you're already automating timestamped backups, you could:
• Use Google Drive as a backup destination, not a live BackEnd.
• Schedule periodic exports or copies of the BackEnd to your Drive folder.
• Use VBA to trigger the backup after key operations or at intervals.
Would you like help setting up a VBA routine to back up your BackEnd to Google Drive automatically?

[mike60smart]

This is what Google Says:-
No, using Google Drive as the backend for an Access database is not recommended and can lead to data corruption. While you can store the Access database file on Google Drive, it's not designed to handle the way Access interacts with data, especially with multiple users accessing it simultaneously.
Here's why:
File-based nature:
Google Drive copies the entire file when changes are made, which is inefficient for Access's frequent small updates.
Potential for corruption:
Multiple users accessing and modifying the database concurrently via Google Drive can lead to conflicts and data corruption.
Network limitations:
Access performs best on a local network with high bandwidth. Internet connections, even with services like Google Drive, can be too slow and unreliable.
Not designed for this purpose:
Access databases are not built to work with file-sharing services like Google Drive.
Best practices for sharing Access databases:
Split the database:
Separate the data (backend) from the user interface (frontend).
Store the backend on a network share or SQL Server:
Use a dedicated server or service designed for database storage and access.
Deploy the frontend to individual users:
Users should have their own copies of the frontend, which connects to the backend.
Consider alternatives for collaboration:
For larger teams, consider using SharePoint lists or SQL Server for data storage and collaboration.
Backup your database regularly:
Regardless of where you store your backend, make sure you have a backup strategy in place.
In short, while you can technically place an Access database file on Google Drive, it's strongly advised against due to the potential for data corruption and performance issues.

[ArviLaanemets]

In case at least some users need to work with database from diffe4rent location (at home, at some public network, or from another LAN), the foolproof solution will be:

Your BE database is situated in some shared network resource in some LAN, and all possible users have access rights to this resource;
There must be a Terminal Server installed in this LAN, with user profiles present either for all users, or at least for every user who may need external access for database;
MS Access must be available for every user who may start it from TS profile;
Every user which may use the database remotely must have the copy of it's FE saved on some network resource in this LAN, or in his/her TS profile. For users working exclusively in same LAN, and never starting database from TS, , the copy may be stored in his/her computers user profile, or on some accessible network resource - it will be as you want to set it up.

NB! Your SysAdmins must set up a secure way for remote access for allowed users to log in into TS securely!

With this setup, any data exchanges in your database are happening only in this LAN. Between remote computers and Access instances revoked whenever user works with database, only keystrokes, mouse clicks, screen images, etc. are exchanged.