direct remote access question (1 Viewer)

[beara]

I have a fairly small database that's split into front and back ends, that I originally wrote in Access 2 for a friend's small business. they've been using it for the past 30 years and its now running on Microsoft 365. (Up until recently it was still the original Access 2 DB and I was having to nurse it along on an ancient XP machine, but they finally caved in and let me update it - long story .)

Anyway, its been updated and is still fit for purpose, but it now has to be accessed by both Mum in the office (where the system lives) and son who has his own home office. One aggravating factor is that he's a Mac user, so we have been using Teamviewer remote print to print at his home office, but, just at a critical time, this has stopped working. Our backup of PrintToEmail has also become flaky.

I have been remotely supporting them from 300 miles away for the past 15 years, with regular site visits. My short term solution is to travel down ( I was going in a couple of days anyway), move the machine to the Son's location as he does the most printing, and let mum remote in using Teamviewer. However, as there's only two of them and because of the way their business days are structured, they DON'T ever have to access the system simultaneously.

So, with that in mind, is there any way they can both have the front ends on a local windows machine, and the back end of just the tables live somewhere that they can both be connected to? It doesn't matter if they have to establish a connection to the tables each time they load the front end. We cvan get them to check with each other that the other has closed down before they log on.

 

[GPGeorge]

There are ways to manage this, but almost all of them involve an investment in resources to support it, so that's the first question, really. Are they willing to invest in their business? This appears to be a mission critical application (after more than 30 years). I would imagine they're interested.

1st, there is a way to run Access on a Mac. Windows Emulation software can be installed on the Mac to run Access. The most commonly cited application is called Parallels, although there are others of that same type. I see that Parallels is now a subscription at $99.99 a year (US), instead of a perpetual license, so I'd probably search further.

Once that's set up, the next step is to decide whether you can implement a remotely hosted back end database (i.e. "in the cloud") so that both users can work with the shared data simultaneously. Again, there are optional ways to do that, so there are further decisions to make.

One option, which I would explore first, is to migrate the data from Access to SharePoint lists in their Microsoft 365 environment. Depending on the amount of data involved, that could be a reasonable way to share the data. SharePoint is probably best suited for tables with no more than 30,000 or so records in a given table. From the sounds of it, a 30 year old application might exceed that.

Another option is a remotely hosted SQL Server database, which can be obtained through a provider (I use Winhost.com, but there are many others). The data is migrated to the SQL Server database and both instances of Access link to it. Such hosted solutions can be as little as $5-$10 a month or more, depending on your needs.

The biggest issue with this approach is the need to implement a solid client-server design for the Front End and Back End.

What are the options your friend's business can work with and budget for? That will help guide your further research.

 

[Minty]

You can't store an Access backend on the cloud and have it work with any reliability whatsoever.
As George has pointed out you will, ideally, need to migrate the backend data to a different platform that your Access front end can talk to, with allowing for making things work over an WAN environment.

The other option would be to get a Remote Desktop solution working, and retain the current environment.
This would involve the least amount of work.

Both options come with a cost.

 

[beara]

Thanks George and Minty
They are now in a position to do what is necessary, so I'll go through the points raised. Parallels is certainly an option, I use it myself to work on the systems for them as I work on Macs.

The system now is much leaner than it used to be. The front end is less than 20mb and the rear end under 5mb! Our largest table has under 600 records. They deal with providing shipping and support services to clients attending trade exhibitions around the world, but we only deal with 4 exhibitions and a small number of active clients at any one time. we run a separate system per exhibition - their choice - and the system records orders, creates shipping labels and associated documentation. One user does all the shipping so accesses it to print the shipping stuff, whilst the other creates invoices in it.

It would be easy to pop a tiny form factor windows machine in the remote office, or he could run parallels so he can travel with the system.

My issue is that I now only do Access stuff for them and nobody else. I've been their jack-of-all-trades go-to for IT support and my access development is ad-hoc when they need it, so I get very rusty with the code and all that! Takes me a while (and lots of google/youtube) to get tuned back in.

It does sound like sharepoint lists might be the way to go, I'll do a bit of research...

 

[beara]

You can't store an Access backend on the cloud and have it work with any reliability whatsoever.
As George has pointed out you will, ideally, need to migrate the backend data to a different platform that your Access front end can talk to, with allowing for making things work over an WAN environment.

The other option would be to get a Remote Desktop solution working, and retain the current environment.
This would involve the least amount of work.

Both options come with a cost.

It's frustrating - the old system had to use print to pdf to a shared folder but was clunky and slow. When Remote printing works everything is fine.... I'm beginning to think that it might just be simpler to pop a small windows machine in his office to do the remote stuff on, as windows to windows printing is easier to troubleshoot

 

[Pat Hartman]

Remote desktop is still the cheapest solution. I don't know if this solves the mac user problem. Citrix does solve that problem since it allows the mac user to run in a browser rather than in native windows mode.

Both the host computer (probably the one in the office which must remain on 24/7 for the connection to work) and the client must install the RDP client. It is free with Win pro but ~ $100 each with Win home versions.

Citrix can be hosted in the cloud but that will probably run $40 per computer per month including an Office 365 subscription. That may be negotiable if you already have subscriptions.

Much as I dislike SharePoint, that should get you past the mac issue since it runs in a browser. Apple sure has a great sales pitch. They sell the most over priced gear in the marketplace by appealing to your "specialness". Then they get you to buy a new thousand dollar phone every year or two. Great marketing or is it that they still deliberately make the batteries fail?

 

[beara]

Remote desktop is still the cheapest solution. I don't know if this solves the mac user problem. Citrix does solve that problem since it allows the mac user to run in a browser rather than in native windows mode.

Both the host computer (probably the one in the office which must remain on 24/7 for the connection to work) and the client must install the RDP client. It is free with Win pro but ~ $100 each with Win home versions.

Citrix can be hosted in the cloud but that will probably run $40 per computer per month including an Office 365 subscription. That may be negotiable if you already have subscriptions.

Much as I dislike SharePoint, that should get you past the mac issue since it runs in a browser. Apple sure has a great sales pitch. They sell the most over priced gear in the marketplace by appealing to your "specialness". Then they get you to buy a new thousand dollar phone every year or two. Great marketing or is it that they still deliberately make the batteries fail?

We use the Microsoft Remote Desktop app within the office, but I have never managed to get it to work on the WAN, beyond my technical expertise, and it does play nicely on the Macs. Got to say that what moved me away from windows to mac was the ‘they just work’ sales pitch and in my experience, generally they just play well together within their ecosystem, and we are all using 10 year old MacBook airs for a lot of day to day stuff and they’ve not gone slow like my equivalent windows laptops. Thankfully I sit with feet in both camps but prefer Mac OS for my day to day stuff.
All our machines are win 11 pro, perhaps I need to try getting the RDP working over the WAN…

 

[Pat Hartman]

they just play well together within their ecosystem

That's how they get you to pay thousands of dollars more. The problem is they do not play well with others. Apple made a conscious decision back in the 80's to be incompatible with the world and Apple was circling the drain as a company until they came up with a magic product.

I've never used RDP on a LAN. Not sure why you would do that. The point is remote connections. I haven't set it up in years. You need to configure the computer at the office that will receive the connection as well as the mac that you want to connect to it.

 

[The_Doc_Man]

It doesn't matter if they have to establish a connection to the tables each time they load the front end. We cvan get them to check with each other that the other has closed down before they log on.

First, a disclaimer. There ARE exceptions to every rule. Therefore, I am about to commit heresy as I step through a loophole.

As stated before, there are very solid reasons why you cannot use a true cloud-hosted back-end file. Simultaneous access to the file will eat your back-end extremely quick. However, there is potentially a special case here based on your comment.

IF you can make the DB reside on the cloud or other remotely visible machine, but set up special directories on each machine that are NOT on the cloud and NOT subject to cloud-like automatic updates, and if you can make them have the same path on both machines, ... and if you can impress upon your two clients that they must NEVER EVER violate the "no simultaneous usage" rule, ...

Then you can manually force the back-end to be copied to the cloud/other machine when you are not using it, and download it from the cloud/other machine when you need it.

To use it, you put it in the special folder path. You can get away with this because Access uses a text-oriented method to identify back-end locations for its tables. So if BOTH back-ends host machines have, say, C:\SpecialDBpath\ as a folder, you can force your Access front-end file to link to C:\SpecialDBpath\SharedDB.ACCDB (or .MDB if you preserved that shorter file type) for each table.

IF you take this approach, this is crucial: They MUST remember that before using the DB they must download it (copy it) to overwrite the copy already in their local folder.

If they have non-LAN connectivity (which it seems they might, since they use TeamViewer) then also look into using FTP as a way to copy the entire DB from the other machine. You have to set up your systems to accept an FTP (or SFTP) request coming from either direction. HINT: This is something that CAN be scripted into a batch job, and if it works, a cloud account isn't required. They can just get the latest copy from each other.

The loophole I'm exploiting is that if their usage is NOT simultaneous, and if they REMEMBER and LIVE BY that fact, then it is possible for them to coordinate their actions. Using the "standard" cloud will do this if you set it up to force updates on demand. Using the FTP solution will also work. The key will ALWAYS be non-simultaneity. The first time they forget? Bang, Zoom, straight to the moon!

This is the absolute, dirt-cheap, ugliest method to accomplish what you want. Remember, it is an heretical solution. I'll probably get members of the forum to throw sharp objects in my direction. They'll come after me with pitchforks and torches; or maybe with a rail, a bag of feathers, and some tar... but as one way to accomplish your goal with minimum cost, this will work. It is minimum cost, maximum risk.

Pat Hartman correctly points out that some versions of Windows will have RDP already available, some won't. If they have the versions that support RDP, then THAT is the far superior solution. Far safer, and if RDP is already available, a lot cheaper.

 

[beara]

That's how they get you to pay thousands of dollars more. The problem is they do not play well with others. Apple made a conscious decision back in the 80's to be incompatible with the world and Apple was circling the drain as a company until they came up with a magic product.

I've never used RDP on a LAN. Not sure why you would do that. The point is remote connections. I haven't set it up in years. You need to configure the computer at the office that will receive the connection as well as the mac that you want to connect to it.

RDP on a LAN? Controlling a windows box from my Mac, working on a legacy machine running old software, working on the backup machine that's in another office. That's what I use it for.

 

[Pat Hartman]

It's the mac that prevents you from the direct connection you would otherwise get with a LAN. But, the reason for RDP is to support WAN connections. They work fine

 

[Minty]

@Pat I use RDP for our LAN work servers all the time.
It's the normal way of getting to them, it's not reserved for WAN use, I would say that is more the exception than the rule. Most of our servers don't have a keyboard or a monitor even attached, RDP is the only way to administer them.

 

[Pat Hartman]

I would say that is more the exception than the rule.

I think it depends on your work. I forgot that people who do tech support use RDP to log in to manage servers. That isn't the kind of work I do. My clients always want to use RDP to access their office LAN remotely, hence via a WAN.
Or they want it so that I can log into their LAN and do work for them remotely.

 

[The_Doc_Man]

Because of issues with locked and secured server rooms, sometimes when I was still working with the U.S. Navy we used RDP to get to the consoles on our systems. We almost never had to actually walk into the server room unless it was to do a physical replacement of a board or an HDD. (When I was still doing this kind of work, SSDs were not yet commonplace.)

 

[gemma-the-husky]

I think there must be benefits for sysadmins if users run system wide apps through terminal services, rather than their own PC.

It must be easier to enforce and maintain group policies if all users use the same single login script, and all run the same version of Excel, Access and so on.