Database Design (1 Viewer)

upeenarce

New member
Local time
Yesterday, 20:48
Joined
Dec 10, 2012
Messages
8
Hi All,

first off, i don't really post here, but have frequented the forum a number of times, so thanks for the help thus far.

I have question about database design.

Currently i have set up that is an excel sheet using multiple macros to essentially replicate some of what a database would do. this sheet is used by myself and 7 other people (all located in different countries). The current sheet is stored on a shared google drive (team drive). This presents multiple problems, with versions / users overwriting each other etc etc. The 7 users all have a similar job to input data, which we then use to run various reports (the excel macros) on.

My suggestion is that we move to a split database. The backend hosted on the team google drive, with each of them using their own local copy of the front end. My question is can i utilize 'Microsoft Access 2016 Runtime' for their front end copies (they dont have a copy of access, which is the reason we went with excel originally) and will their be any potential problems having the backed on a shared google drive.

Any and all thoughts / suggestions welcome.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:48
Joined
Jul 9, 2003
Messages
13,221
No, you can't link to an MS Access database on Google Drive.
 

upeenarce

New member
Local time
Yesterday, 20:48
Joined
Dec 10, 2012
Messages
8
The google drive in question is a team drive we access through filestream so its treated like a network drive (drive letter J: on all computers), would that still not work?

Also all users have access to an internal drive access via a vpn, could that work, if the google drive option is not a option?

Failing both of those, can you suggest a different set up?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Feb 19, 2002
Messages
32,221
It makes absolutely NO difference what kind of cloud drive you want to use. The answer is NO. You cannot share an Access BE this way. If you want to share an Access database among remote users, the best solutions are Remote Desktop and Citrix.

If you share the Access BE with a cloud drive, the last user to save will overwrite the previous users data. Doc has written on this extensively and explained WHY a cloud drive will not work for multiple users. Thinking you can control the sharing with timing, will be the equivalent of playing Russian Roulette with all chambers loaded instead of just one. It is not a question of IF data loss will occur, it is a matter of when.

A VPN could work but Access is extremely slow over a VPN. If you are going to share an Access database over VBN, you will almost certainly need to convert the BE to SQL Server to get any performance at all.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:48
Joined
Feb 28, 2001
Messages
20,224
The biggest issue with Cloud systems is that they are primarily whole-file sharing systems. To be useful to your purpose, they have to (a) be connected to every user via an incredibly stable wide-area network and (b) fully support SOME level of Server Message Block (SMB) protocol and (c) support Windows file locking.

(a) is unlikely because "incredibly stable" and "wide area network" are words that only appear in the same sentence as incompatibilities. A "stable WAN" is an oxymoron. Oh, granted that the WAN will drop and very quickly come back up. But that drop is FATAL to file transfer protocols that don't have "restart/resume" built in - which is to say, most of the transfer methods.

(b) is necessary because the drive has to fully (emphasize FULLY) support Windows File Sharing protocols which are based on SMB. But most cloud systems do EXACTLY what you have observed - user A will OVERWRITE what user B has done. And that would be true for just about ANY of the Microsoft Office utilities because they ALL use Windows File Sharing protocols.

(c) is necessary because the way you DON'T overwrite someone else's work is to have Windows support file locking fully - but because of (a) above, you cannot (do not DARE) implement file locking across a slow, unstable network. If you actually did that, your server admin would hate you forever for it, because you would CONSTANTLY be asking that admin to please unlock the shared file for you after each network drop. And given that you said your users are widely distributed among different countries, the long-distance calls and calls coming from multiple different time-zones would make your server admin very unhappy. Not to mention that because of the distributed nature of your users, file locking would have to be coordinated over the WAN. From a network viewpoint, this is one that would make an IT network manager say "NO" no matter how vital the files were to the business. (HINT: The basis for the "NO" would be risk assessment issues.)

Pat Hartman's suggestions for CITRIX or another RDP solution might be your best solution. Your other alternative would be to get away from Office altogether and find a web-based solution. As it sits here, "Office" and "WAN" just don't mix very nicely if at all.
 

upeenarce

New member
Local time
Yesterday, 20:48
Joined
Dec 10, 2012
Messages
8
Thanks for the advice. its great to know this before starting out down this route only to find it doesn't work later on. Ill rule out a split database and look for alternatives

@Pat Hartman, you mention converting the back end to SQL. I am just starting to get into SQL, and have a mock up SQL database i tinker with whilst learning. Could the access runtime be used as a front end with the SQL server as the back end. I'm probably showing my little understanding here, but I'm trying to stick to technologies that i have some knowledge of and am comfortable learning on the fly. As there are so few users of the database it doesn't warrant a lot of investment (even if we rolled this out further, it could only ever get to 50 users at its highest point).

@The_Doc_Man we have just moved away form remote desktops for our remote users, and i don't believe our IT department will be wanting it back anytime soon. You mention fully web based solutions, can you offer any suggestions, i could look into. The issue im having is that i have little to no experience with web based solutions and when reading through the various options, they seem either too simple and wont work for my needs or require some complex knowledge of coding languages, which is way above me
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:48
Joined
Feb 28, 2001
Messages
20,224
i don't believe our IT department will be wanting it back anytime soon
If your supervisor says people have to be able to do remote desktop connections then he will talk to their supervisor and handle that. This is not your battle to fight. The question will always be "how badly does the company as a whole need it?" The reluctant IT guy, at the end of the day, has to NOT block the company from solving its problems with an eye towards security AND economics.

Unfortunately, I don't personally know of any web-based RAD tools that are equivalent to Access. However, the good news is that if you have an SQL Server that enables ODBC connections, then both Access front-ends and web browsers can deal with the same DB. That, at least, is possible. The trick is that you will likely have to develop specialized web code (specialized to your business needs, i.e. a custom web page or two or six). So there is the cost & pain of having to "roll your own" code because you won't be able to buy it off the shelf.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Feb 19, 2002
Messages
32,221
Technically Access can connect over a WAN to a remote database. However, Access is not optimized to work over a WAN so doing this while possible, will be like watching paint dry. I have heard encouraging reports of Access connected to Azure might be usable but not in my experience. I could get the connection to happen but the response was so slow, you'd spend your whole day going for coffee as the screen filled with data. The people who reported success actually hosted their own Azure cloud so I'm guessing that I was experiencing a configuration problem since I was working with the lowest level third-party offering available and the tech people didn't have a clue how Access worked or what might be the problem.

This is an Access forum. While some experts might have some experience developing web apps, in general, we use Access and Access is not a web app at all. What I have seen from projects I've worked on or friends have reported to me, Web development at its best will be about 10 times the cost of development using Access and that is assuming that you are an expert web developer.

Why did your IT remove the RD capability? Did they give any thought to how they would support existing Access applications or just discard you because Access isn't important? Replicating your app as a web app is simply a matter of time and money. How much is your employer willing to throw at it when they already discarded the cheapest solution?

All of my clients who need remote sharing of an Access application use either Citrix or RD, both of which they host themselves. There is another RD option which several of my clients set up for me to allow me to work remotely and that is the option that doesn't run a RD server but runs RD on some PC in the physical office. So when I connect from home, I actually connect to the same PC I would log into if I were actually in the office. If you have office hardware, this is by far the cheapest solution. The downside is you need someone on site if you need to power on the PC and reboot it.
 

Users who are viewing this thread

Top Bottom