Database Design (1 Viewer)

upeenarce

New member
Local time
Today, 00:33
Joined
Dec 10, 2012
Messages
9
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, 07:33
Joined
Jul 9, 2003
Messages
16,245
No, you can't link to an MS Access database on Google Drive.
 

upeenarce

New member
Local time
Today, 00:33
Joined
Dec 10, 2012
Messages
9
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
Today, 03:33
Joined
Feb 19, 2002
Messages
42,981
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
Today, 02:33
Joined
Feb 28, 2001
Messages
27,001
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
Today, 00:33
Joined
Dec 10, 2012
Messages
9
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
Today, 02:33
Joined
Feb 28, 2001
Messages
27,001
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
Today, 03:33
Joined
Feb 19, 2002
Messages
42,981
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.
 

upeenarce

New member
Local time
Today, 00:33
Joined
Dec 10, 2012
Messages
9
Hi,

Thanks again for the advice. So after re reading some of the above and some further research i, thinking i need to go down the route of using an SQL server.

@The_Doc_Man you mention creating multiple specialized web code? i assume if i dont want any form of website, this wont be needed? My thought here being i can create all tables on MYSQL server and then have access handle everything else. I would then aim to distribute the database using access runtime to the users.

Following this route, is there anything i need to be aware of? Will the basic design premise work as i imagine?
 

Minty

AWF VIP
Local time
Today, 07:33
Joined
Jul 26, 2013
Messages
10,355
We use Azure as a back-end to Access front ends frequently and with a good level of success.
As long as a majority of the users are local (i.e. the same country/ geographic region) as the Azure location it works very effectively with a suitably designed front end.

With a self-hosted SQL server then realistically as Pat has suggested a hybrid Access RDP / web page solution is by far the most reliable and effective, you can add in the web pages for those parts of the DB that require it, e.g external customer-facing reports etc.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:33
Joined
Feb 19, 2002
Messages
42,981
My thought here being i can create all tables on MYSQL server and then have access handle everything else. I would then aim to distribute the database using access runtime to the users.
You might want to read the part of my earlier response about watching paint dry. Simply swapping a Jet/ACE BE for ODBC isn't going to solve your problem and if the Access app is not properly designed for ODBC, it would actually be slower even on the same LAN. Access does not work well over a WAN. You might be able to get by using a VPN IF you convert the BE to ODBC but a straight link across the internet is not going to solve anything.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:33
Joined
Feb 28, 2001
Messages
27,001
Hi,

Thanks again for the advice. So after re reading some of the above and some further research i, thinking i need to go down the route of using an SQL server.

@The_Doc_Man you mention creating multiple specialized web code? i assume if i dont want any form of website, this wont be needed? My thought here being i can create all tables on MYSQL server and then have access handle everything else. I would then aim to distribute the database using access runtime to the users.

Following this route, is there anything i need to be aware of? Will the basic design premise work as i imagine?
If you don't want any kind of web site then you are defining the boundaries of the problem and in the process, setting limits on what you can expect to do with what you build. Please carefully read Pat Harman's comments. She has a lot of hands-on experience where is most of my knowledge on external SQLs is indirect. I'm an O/S and networks and hardware person originally. I've managed a machine that was a DB server for ORACLE (with ODBC) but in that case I wasn't a DB manager, I was the SERVER manager. So a lot of what I got, I got second-hand.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Sep 12, 2006
Messages
15,614
I have some systems which users are using by connecting to remote data tables on a server (not a cloud server) using a VPN. It's slow, and not as good as a Terminal Server, but it works without issues. That's worth trying to see if its acceptable. A Terminal server is generally the best solution, but that's not free and you need licenses for each user, although they can use run time access.

You can help by careful design to minimise the amount of data that queries have to move from the backend database to front ends.

You can't use things like One Drive, because those systems work by using a local copy of the cloud file, and then synchronizing the local copy with the cloud copy, and only one person at a time can do this.

If you instead use a SQLServer back end, then you can host this in the cloud (eg Amazon AWS will give you a free trial), but it's not free, and needs some development, and also needs SQLServer expertise. It's also dependent on having a fast internet speed, and it's not going to be as fast as a LAN.

I think websites are very difficult because of the way people use web sites - the back button and so on. It needs a completely different logical approach to managing data.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:33
Joined
Feb 19, 2002
Messages
42,981
I've tried the Azure trials. They were like watching paint dry. Either the third party has to understand and configure for Access or you need to implement your own cloud with your own experts willing to get Access to run with acceptable speed.

One of my clients bought into a cloud for backups, etc. They kept a local server to run SQL Server for their Access apps and their accounting package. The local server died and while they were trying to rebuild it locally, they instantiated it in the Azure cloud. The result was so slow that everyone except the data entry people stopped using the app for two weeks. I was gone so I got the story second hand. It wasn't pretty.
 

Users who are viewing this thread

Top Bottom