Running Access Split Database on RDS Server

Phil S 261 Reputation points
2022-03-31T09:57:06.403+00:00

Hello Access Experts

For years I have been running a split access database across a local LAN, the mechanics of which I understand. I have just saved the FE and BE to accdb format to bring them up-to-date.
To remove reliance on local hardware our Group IT want to run all apps in the cloud.
To this end they have copied the front and back end to an RDS server for testing.

This arrangement seems to be working although it has not been tested with more than 2 concurrent users so far.

The questions....

Is this a known viable method for provisioning an Access database, or will there be issues that have not been foreseen by the IT team? Number of concurrent users, data corruption etc.
It still seems useful to me to keep the front end/back end split for future development changes - is this advisable?
Any other gotchas or recommendations appreciated.

Thanks

Phil

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
881 questions
0 comments No comments
{count} votes

Accepted answer
  1. Albert Kallal 5,256 Reputation points
    2022-04-04T02:20:03.11+00:00

    yes, deploy access via remote desktop is a great solution. And one that been around long before cloud hosting options existed.

    As for the front end location? While in a "complex" theory as to how such deployments work?

    As a general rule, each user (now on that remote box), is very much still required and should get their own front end copy.

    However, you of course can't use c:\MyInvoiceSystem.Accde, but have to use a path name that is separate for each user.

    The reason of course is issues like upgrading the front end. (you always provided a new copy for updates). You are "still" high recommended to do this for each user. The challenge then becomes what folder?

    You could say create a folder in My Documents\MyInvoiceSystem.

    So, you want (and should) thus use a folder that is "seperate" for each logged on user.

    You can try and let all users work with the same front end, but it not a good idea, since if you have some temp tables, and even sometimes things like filters and ohter code? That code don't work all that well with multiple users in the SAME front end. (so, that's way in the past you gave each user their own copy).

    And of course another reason was if one user damaged, or corrupts the front end, then everyone else in the building can continue to work - without issues.

    so, the only hard part is to find a folder that is "per user" logon.

    Currently, for remote desktop (and terminal services), then we now have started using the folder "%appdata%.

    That folder will resolve to something like this:

    C:\Users\AlbertKallal\AppData\Roaming\MyInvoiceSystem\Invoice.accDE

    So, it depends on how you roll out and setup each user. Maybe you started using some "installer" program to setup each user. So, as noted, you do want to adopt a standard location for each user, and you now can't use the "c:" drive like you did in the past. And hopefully you don't have some dark caves of some VBA code that has hard coded path names - as those can break if not written say to use the current location of the running FE (front end).

    the next challenge:

    If you have some code that automates outlook, then such code can be a issue. Since now your running Access remote (via remote desktop), but that outlook automation code assumed outlook on your desktop. So, there are ways to address this isuse, but for the most part?

    This setup works well, can work even with say a poor internet, or even some connection that might break. In most cases, the users can simple re-connect, and they are right back to where they were before.

    But, for the most part, things should work as before. The challenge as noted can be say code that supposed to create a PDF report, and then say email it - since you might not have say Excel, or Outlook installed + setup correctly on that remote computer. So, some of these issues "depends" on if all of office and including say outlook etc. is ALSO installed on that remote computer. If not, then you have to deal with that issue. (say maybe don't use outlook to email from your Access applcation. Or, you could build a custom RDP bridge (that's what we did). This allows users to remote use our Access applcation, but when they click on a say a button to email a report, we launch + use their local copy of outlook, and outlook not even installed on the remote desktop running our Access application.

    And the same goes for printers - they for the most part work, and even printing to a local printer tends to work, but NOT for very large reports - as that travels over that internet connection.

    Other then above issues, I can't really think of much else that you have to worry about. Our big issue was Outlook, and we did not want to maintain or have two copies of outlook for each user (one on their local desktop machines, and then another copy on the remote desktop computer).

    But yes, in all cases, you want to continue to work + develop + deploy based on a split system. The FE (front end) is in effect the software you deploy, and the back end (BE) is the data file that all such users will share. And if in the future, you decide to move the BE to sql server, then again, this setup of continued "split" FE/BE model also facilitates how this should work.

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.