Split Back End database in the Cloud??

[FuzMic]

Hi peers

I am toying with idea to place a Split Back End mdb in Cloud storage. Is this a viable path to take in terms of:

1 Will it be any lag in reading or transferring data to and from the backend?
2 Can we use UNC for the backend eg \\myData
3 What if i want to use sql server then where will the server reside?

Hope to hear your views to tinker along. Thank you first.

 

[theDBguy]

Hi. If you’re thinking of using an Access back end file in the cloud, I doubt you will find a lot of good choices out there. But if you’re thinking of using SQL Server as the back then that’s a better option.

 

[FuzMic]

Db guy

I think One drive should do the trick. Once installed in it appears in win Explorer.

 

[isladogs]

It is a bad idea to run your backend in a cloud storage area such as OneDrive or Dropbox.
If there are any interruptions in connection whilst you are writing or editing data, corruption is likely to occur.

OneDrive etc are fine for storing files but not for the purpose you describe.

 

[FuzMic]

TQ isla
Yes bad idea but then what other option if i want to share data in the cloud.

 

[CJ_London]

what other option if i want to share data in the cloud.

the cloud environment is completely different from local networks and comes with all sorts of requirements around performance, accessibility, security.

Be clear what you want - to share data (i.e. you are the creator and everyone else only views the data), or you want to allow others to add and modify data.

The only way an access backend will work on the web is if you use something like citrix or terminal server.

You mention mdb - this is really old technology and although still works, if the FE is mdb/e as well you may struggle.

If you just want to share data using access and one drive, keep your FE and BE local and export the data you want to share as an excel or text file for others to view.

What if i want to use sql server then where will the server reside?

SQL server can reside on the web - investigate azure and google apps. You typically pay for two things, the amount of storage you require and the number/size of of transactions (moving data in/from the server)

Will it be any lag in reading or transferring data to and from the backend?

definitely - even with superfast broadband connection speeds will only be a fraction (perhaps 5%? someone can perhaps be more accurate?) of the speed you get on a lan - so everything has to be written in a way to minimise data volumes being transferred - think how websites work. You will also have to determine how big a portal you need for your connections - the bigger the portal, the bigger the cost.

 

[isladogs]

Agree with everything that CJL wrote.
Just a couple of things to add to that response
1. If you just want to transfer your data to other people/places then OneDrive is fine ...as long as the recipient then copies that data to their own hard drive before using it.
2. There were several failed attempts by MS to create Web versions of Access databases. All were flawed and all have now been deprecated.
If using Azure or Citrix/Terminal Services isn't suitable for your needs, then I would move to a non-Access solution.
3. MDB files are inherently insecure and I really wouldn't think of sharing your data online in that format. For more details, see http://www.mendipdatasystems.co.uk/compare-access-file-security/4594444323

 

[FuzMic]

Thank you for the care in the response. I guese i need to think again what i really want to sub optimize. TQ again guys.

 

[Pat Hartman]

Let me be clear:

One Drive and other similar options are not a viable way to share an Access BE UNLESS NO ONE EVER updates the BE. If you need people to update the BE data, then this is NOT an option.

Someone already mentioned Citrix and RDP. Those are your best options since they provide excellent service and no (or minimal) changes to the Access app.

Azure is an option if you want to convert the BE to SQl Server but I have not had any success with this method although others have. I think it probably depends on the quality of your Azure environment and I was using the cheap offerings on the web. The people who had success were using custom, internal cloud implementations.

 

[CJ_London]

Other thing to point out is that anything to do with the web costs money. Whether it be a redesigned front end in a different language or the cost of hosting coupled with all the extra cost of implementing security measures against data theft, trolls and malicious attacks

 

[bobsy852]

This seems to be the exact problem our company is now experiencing.
We have an Access database which has been used since 2000 by all staff members on a LAN within an office.
Now with remote working, we need to update this to work remotely will all users being able to add and read data to the database.

As I will be using the feature for longer than most other staff (nearing retirement ages) I'm keen to introduce a long term solution but we are only a small company (4-5 staff members) so cannot afford a large outlay for this.
A lot of people mention Cirtrix and SQL. I'm looking into both, but both look to be a large learning curve and SQL servers seem expensive?
There are a number of Citrix software packages, so I'm not even sure which would be needed for this application, never mind the cost of them?

 

[Minty]

If you can host your own Terminal Server, and SQL Server Express internally this is definitely the most cost-effective route to go.

Citrix is a wrapper application (quite expensive) around a basic TS remote desktop solution.
SQL Server Express is free.

An alternative if you don't have your own server environment, would be to either find someone to host the above or look at an Azure Cloud based SQL solution. A low spec Azure version can cost as little as ~£140 a year.

You would probably in either case need to look at altering your FE Access app to better make use of the SQL Server, and reduce network traffic if you don't go down the Remote Desktop route.

 

[CJ_London]

A web hosted Terminal server linked via remote desktop will be cheaper than citrix and requires minimal changes to your app. Azure can be cheaper still for a well designed database generating little network traffic but will almost certainly require a redesign. It will also be a slower. You'll need to look around for prices but looks like a hosted terminal server for 5 users is around $500/year plus perhaps $100 for additional support

To buy a terminal server license is expensive and you need the equipment to put it on

I'm not recommending them, but here is a example - give them a call

TSplus Official ® | Remote Access Software Suite

The official TSplus website. TSplus offers affordable Remote Desktop Solutions designed to provide Secure Web Remote Access and Application Delivery.

www.terminalserviceplus.com

Alternative is don't work remotely

 

[theDBguy]

This seems to be the exact problem our company is now experiencing.
We have an Access database which has been used since 2000 by all staff members on a LAN within an office.
Now with remote working, we need to update this to work remotely will all users being able to add and read data to the database.

As I will be using the feature for longer than most other staff (nearing retirement ages) I'm keen to introduce a long term solution but we are only a small company (4-5 staff members) so cannot afford a large outlay for this.
A lot of people mention Cirtrix and SQL. I'm looking into both, but both look to be a large learning curve and SQL servers seem expensive?
There are a number of Citrix software packages, so I'm not even sure which would be needed for this application, never mind the cost of them?

Hi. This may be simpler than you thought. I'd say the first thing you need to figure out is how would the staff "work remotely?"

We use split Access databases at work. When we had to work remotely, all we had to do is VPN into the company's network, and we're able to continue using the Access databases as if we were at work.

 

[bobsy852]

thanks for the replies.

We use split Access databases at work. When we had to work remotely, all we had to do is VPN into the company's network, and we're able to continue using the Access databases as if we were at work.

This is what we are currently doing. But when the VPN drops on someone's connection it seems to cause an error with the database so we have to ensure it is backed up.
To be honest, this problem has only just occurred for the first time in over a year of use now so it isn't common.

 

[The_Doc_Man]

First and foremost, a regular backup regimen is mandatory anyway if the data has any value to your company.

Second, a VPN is still no more stable than the backbone on which it rides. Your solution will involve the painful choice of "what do I want to give up in order to make this work?"

 

[theDBguy]

thanks for the replies.


This is what we are currently doing. But when the VPN drops on someone's connection it seems to cause an error with the database so we have to ensure it is backed up.
To be honest, this problem has only just occurred for the first time in over a year of use now so it isn't common.

Hi, sorry to hear that. I didn't have to do anything with our databases. They seem to work just fine, as if we were using them from work. So, I guess it could be a matter of how the VPN was set up.

 

[Pat Hartman]

VPN's tend to be too slow when your BE is Jet/ACE. They work better when the BE is SQL Server.

If you have workstations that can remain in the office, you have other options. There are several products that allow you to connect from your home computer to your office computer and have access to other things on the LAN such as printers. This would be a short term or long term solution but it REQUIRES two sets of hardware. One at the office connected to the LAN and a second wherever the user wants to work.

Here are some options. Price varies widely. I would NOT recommend using a free option. When software is "free", YOU are the product.

Best remote desktop software of 2021: Paid and free choices for businesses | TechRadar

This is probably the most cost effective assuming you already have the hardware and the Consumer version will be sufficient for your needs.
Plans and Pricing - RemotePC™

I've used both server based RD and RD that connects to a specific PC that is not shared. Both work fine. The option that connects to a specific PC will require someone to be on-site should the PC need to be rebooted. I don't know if any product actually solves this problem but in all the cases where my client has given me remote connections, if they were not server based, someone always needed to go kick the PC if it was off which of course made it awkward for me to work weekends and evenings but not impossible.
How to use Remote Desktop (microsoft.com)
Guide to Connecting to your Home or Office PC from Anywhere (groovypost.com)