Database Design

[upeenarce]

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]

No, you can't link to an MS Access database on Google Drive.

 

[upeenarce]

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?

 

[The_Doc_Man]

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]

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]

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.

 

[upeenarce]

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]

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.

 

[The_Doc_Man]

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.

 

[CJ_London]

Didn’t I read somewhere that with office 365 multiple users can edit excel/word files at the same time. it’s called co- authoring.

edit: here’s a link https://support.microsoft.com/en-us...uthoring-7152aa8b-b791-414c-a3bb-3024e46fb104

Not sure if it meets your needs

 

[gemma-the-husky]

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.