Advice Request : Simplest method to allow multi-site access to a prototype database

[Bob McDuff]

I have a database that I've been working on for some time. It's heavy on front-end functionality, has a custom-built user accounts system, and has a large number of vba routines performing calculations etc. The actual volume of data is fairly low however (a few thousand records spread across a couple dozen tables).

The time has come to deploy a test version of the database to a wider group of users. Likely 6-8 users across 3 different locations.

For the final roll-out the plan is to host the back-end data in a cloud system (likely Azure? Any advice on this is also welcome). But for the next few weeks of testing I would like to take the simplest approach (I've not worked with any cloud systems before). Speed is not a high priority at this stage. Security is not a particular concern either, as the database is populated with representative test-data only.

The three different sites have different IT setups; two use MS One-Drive, and one uses self-managed Windows installs. There is no server that can be easily accessed by all parties, though I have access to a desktop PC with a fast and reliable internet connection. I could host things on this machine in the short-term, if I can provide the relevant permissions.


I'm looking for advice on how best to proceed. I have split the database, but I'm unsure where best to place the back-end data, or how to link the front-end with a remotely-stored back-end for all users.


Thanks in advance for any help or advice you can provide.

 

[cheekybuddha]

Access does not play at all with OneDrive (or equivalents) - do not go there!

Nor does an Access backend work well over a WAN. Very slow and huge risk of corruption.

Your best bets are either to port the backend do a server-based RDBMS (e.g. SQLServer, MySQL, postgres etc) or look into remote desktop solutions.

 

[theDBguy]

Hi. Welcome to AWF!

I agree. What's the point of testing an Access backend anyway if the final product won't be using an Access backend? Surely the experience won't be the same.

 

[Bob McDuff]

Access does not play at all with OneDrive (or equivalents) - do not go there!

Nor does an Access backend work well over a WAN. Very slow and huge risk of corruption.

Your best bets are either to port the backend do a server-based RDBMS (e.g. SQLServer, MySQL, postgres etc) or look into remote desktop solutions.

Hi cheekybuddha, thanks for the reply.

Yes, I realised early on that Access + Multi-User + OneDrive is a no-go!

Could you talk me through the basic steps for setting up a remote desktop solution? I've really never touched anything to do with networking before (prior to this project my work has been algorithmic development for scientific computing). I'm guessing it goes something like:

- Obtain a 'clean' desktop PC with only the database installed
- Have users use TeamViewer (or similar) for to remote log-in
- Use the database as if it were installed on a local machine (?)

Thanks

 

[Bob McDuff]

Hi. Welcome to AWF!

I agree. What's the point of testing an Access backend anyway if the final product won't be using an Access backend? Surely the experience won't be the same.

Hi, thanks for the reply. Happy to be here!

At this stage, the performance and 'experience' is not the main concern. The database represents a resource management system, including purchase / sales / processing / manufacturing / lab analysis of material, and various other functions. The test-users are interested in different facets of the software, and want to provide feedback on the functionality of different modules. More specifically, the company owner has taken a sudden interest in the project, and wants an accelerated timetable for selected users to provide feedback.

I'm a bit apprehensive about switching to an SQL Server approach at this stage for three reasons:

1) I have never used it before, and and anticipate something of a learning curve.
2) The company owner is not keen to pay for any subscriptions until the software has been tested and approved by end-users.
3) I have a lot of queries written in vba (several hundred at least), all tested with the pure-access implementation, and I don't want to risk having to adapt them at this stage.

If I'm misinformed about point 3 above, then please do let me know!


Thanks

 

[The_Doc_Man]

The simplest (but not necessarily the cheapest) solution is to get a product that is equivalent to CITRIX. Then set up the CITRIX to allow users to have a private storage area on the hosting machine. This would probably require a multi-user Access license as well. Each user would have a copy of the front-end file and the shared element would be the back-end file in its own private folder. CITRIX admins frequently do not understand the "separate private folder per user" requirement and therefore need a little extra attention if at first they don't "get" it. The need is due to a combination of Windows file-locking behavior and the perils of literally sharing a common front-end file due to the risk of destructive interference (a.k.a. "left hand doesn't know what the right hand is doing.")

Glad you realized that "Access" and "cloud" do not, at the moment, go well together. The technical reason is that Access was designed in a time when the local-area network was the big thing and wide-area stuff was strictly the domain of web-based protocols. The cloud, Azure, and a couple of other wide-area methods have tried to improve matters, but the problem that remains is the instability of wide-area network connections. Access uses the SMB (Server Message Block) protocol sub-family of the TCP family within TCP/IP. Clouds do not. And the TCP family does not tolerate interruptions based on the session-oriented nature of TCP... (as opposed to the other major family, the UDP group, which IS more tolerant of outages.)

 

[theDBguy]

Hi, thanks for the reply. Happy to be here!

At this stage, the performance and 'experience' is not the main concern. The database represents a resource management system, including purchase / sales / processing / manufacturing / lab analysis of material, and various other functions. The test-users are interested in different facets of the software, and want to provide feedback on the functionality of different modules. More specifically, the company owner has taken a sudden interest in the project, and wants an accelerated timetable for selected users to provide feedback.

I'm a bit apprehensive about switching to an SQL Server approach at this stage for three reasons:

1) I have never used it before, and and anticipate something of a learning curve.
2) The company owner is not keen to pay for any subscriptions until the software has been tested and approved by end-users.
3) I have a lot of queries written in vba (several hundred at least), all tested with the pure-access implementation, and I don't want to risk having to adapt them at this stage.

If I'm misinformed about point 3 above, then please do let me know!


Thanks

In that case, if you're only concerned about feedback on application functionality and not necessarily on data collaboration right now, then I would suggest just giving each user their own copy of the FE and BE and let them perform the application testing in isolation. Setting up a citrix or vpn server would take some time and you'll end up migrating the data out of Access and do another round of testing then anyway.

 

[CJ_London]

I concur with @theDBguy - for testing only, provide your testers with copy of the FE and BE. At least you will get feedback on design, ease of use, your interpretation of the requirements, etc. Make sure part of the feedback is around performance requirements - moving to something like SQL Azure will have slower performance, dependant on the speed of connection. Moving to something like citrix or terminal server should have little impact on performance and has the benefit of a) other than relinking, you can continue to use the app as designed with an access back end and a FE for each user profile and b) your users can use iOS devices since all the work is done on the server, only screen updates and mouse/keyboard events cross the web. Both methods comes with a cost.

 

[Mark_]

I'll third @theDBguy's comment. Main reason is to avoid having each tester see changes made by another users UNLESS this is what is being tested. Think through how an end output, such as a report, will look when multiple users are using the system at the same time. Can be a pain if others are adding things and totals don't come out as expected.

 

[GPGeorge]

I have a database that I've been working on for some time. It's heavy on front-end functionality, has a custom-built user accounts system, and has a large number of vba routines performing calculations etc. The actual volume of data is fairly low however (a few thousand records spread across a couple dozen tables).

The time has come to deploy a test version of the database to a wider group of users. Likely 6-8 users across 3 different locations.

For the final roll-out the plan is to host the back-end data in a cloud system (likely Azure? Any advice on this is also welcome). But for the next few weeks of testing I would like to take the simplest approach (I've not worked with any cloud systems before). Speed is not a high priority at this stage. Security is not a particular concern either, as the database is populated with representative test-data only.

The three different sites have different IT setups; two use MS One-Drive, and one uses self-managed Windows installs. There is no server that can be easily accessed by all parties, though I have access to a desktop PC with a fast and reliable internet connection. I could host things on this machine in the short-term, if I can provide the relevant permissions.


I'm looking for advice on how best to proceed. I have split the database, but I'm unsure where best to place the back-end data, or how to link the front-end with a remotely-stored back-end for all users.


Thanks in advance for any help or advice you can provide.

The first time I moved an Access BE to SQL Server it nearly cost me my job because the resultant loss of performance created widespread displeasure.

A dozen long days (and nights) of figuring out the problems and correcting them saved the day, and my job. In other words, you are right not to want to jump off the deep end before you've even had the basic testing completed.

I would have no serious reluctance to do the same thing today, knowing what to do and not to do when migrating from Access to SQL Server or another server-based RSBMS. Do your due diligence ahead of time and you'll be fine, I think. There are lots of resources available with that in mind.

However, that's long-term planning. Short-term, I would add a 4th to the suggestion theDBGuy offered. Get the first stage of testing done while you plan for the collaborative stage deployment.

 

[Pat Hartman]

For the final roll-out the plan is to host the back-end data in a cloud system (likely Azure? Any advice on this is also welcome).

You needed to work this out a long time ago and you should have already been testing in this environment even if you were the only tester.

One-drive or other cloud file system is a non-starter. Access CANNOT work in this environment PERIOD.

As the others have said, you can do unit testing which tests the functionality of the app but not its performance in a production environment. You may as well start with that. Each user's testing will be isolated since each user will have both a FE and a BE installed on his personal computer.

You really need to talk with your IT people. Programmers simply don't get to tell management how to organize their IT solutions. or go out and contract with a third party provider to set up a test environment. YOU get to work withing the framework YOUR IT gives you.

If the people are distributed (not on the same LAN), how are they working today? During COVID a lot of companies uprooted everything and moved all the users to remote desktops so your users may already be set up to work that way. In that case, you need to work with the RD team and determine how they will publish the Access app. These people know nothing about Access or the best way to implement a shared environment and so will try to have everyone connect to a single copy of the FE. DO NOT ALLOW THIS. Even RD/Citrix/Azure REQUIRE each user to have his own physical copy of the FE that runs on HIS desktop and is linked to the shared BE on a central server.

Once you find out from your IT how the app will run, then we can talk about what changes if any need to be made.