Access without network server (1 Viewer)

eacollie

Registered User.
Local time
Today, 08:18
Joined
May 14, 2011
Messages
159
I help a company currently using Access to manage day-to-day activities of their site. They have historically maintained a network server and the database (back end) lives here. They are considering "going to the cloud" so as not to have to maintain this network server. Not completely sure what they have in mind, but what do we need to do to continue using the Access database? Thank you so much!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:18
Joined
Feb 19, 2013
Messages
16,555
access is not intended to work in the cloud however there are options.

1. migrate the back end to sql azure - potential issue is security - you tell sql azure which IP addresses are allowed to access the database, so not suitable for access while on the move. Benefit is you can continue to use the access front end, but linked to azure rather than the LAN based backend.

2. use citrix or terminal server - both front end and (access) backend are hosted on the server - each user has their own profile so still retains their own copy of the front end. Generally quite fast as the only movements across the internet are screen refreshes one way and keyboard and mouse events the other. Also can be run from none windows devices. Potential issue - needing to reference local files/printers/email/etc

This is a fairly common question and you need to be very clear about what 'in the cloud' means to you. Is access required from home/while travelling?, continue to work without an internet connection? access via smart phones? What is the performance of your broadband connection? etc.

None of the above is comprehensive, you will need to do your own research to determine the full range of upsides and downsides and which route to go, but you have some key words you can use to search. And be aware none of it is particularly cheap although I presume you will have savings by not maintaining the existing network server.
 

eacollie

Registered User.
Local time
Today, 08:18
Joined
May 14, 2011
Messages
159
Thank you CJ.

As I understand the plan, it is to purchase MS Office 365 Business. This solves their problem of maintaining a network server and works well, I understand, for Outlook but Access is a problem.

Currently there are two users of the database but more (probably not more than 10) need to be able to use it. It is strictly used on-site. It needs to be multi user. They do need to print reports, etc locally.

With this outline, which do you think is the best way to go?

I appreciate your help very much! Thank you in advance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
I've had good success with Citrix. However, my clients all host Citrix on their own server. You can find a provider to do this for you but it might be expensive if you have too many users. The price I was quoted (about two years ago) was $35-$50 per user per month and this included the price of O365 which they required. The version of O365 might affect the cost and so will the number of licenses. If your BE would be SQL Server rather than Jet/ACE, the cost would be higher due to the cost of the SQL Server license and database support. Even if they allow the use of SQL Express (which is free), you still need support to manage the database.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:18
Joined
Feb 28, 2001
Messages
27,003
Keeping it on site offers you a cheap if not perfect alternative. Dedicate one machine on the site network as the "permanent" back-end host. It DOES NOT have to be a server. Heck, if it stays on-site and has a hard wired network, just set it up for Windows file sharing, set aside a folder for the BE file, and get working.

Note: If the site uses a Wi-Fi network, this is NOT suitable because Access doesn't work well over Wi-Fi.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:18
Joined
Feb 19, 2013
Messages
16,555
If all your users are office based (and will remain so) and the objective is to do away with your local server and use the cloud then I would look at using sql azure as your backend as a possibility. In principle you can continue to use your access front ends basically unchanged other than linking the tables, but in my experience will need to make some changes for the fact you are working across a WAN rather than a LAN - see this link for the sort of things you will need to consider https://www.access-programmers.co.uk/forums/showthread.php?t=291269

Otherwise stick with citrix/terminal server. Budget is always a consideration - they may or may not be more cost effective than azure. However the migration should be simpler.

I also agree with Doc, you could dedicate a 'normal' computer as a server, or invest in a NAS device (Network Attached Storage) which is easier to maintain than a full blown server
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:18
Joined
Sep 12, 2006
Messages
15,614
None of this is trivially easy though.

A single user can use an Access solution on his own PC.

A system with multiple users requires a different solution. Each user needs access to (ie the ability to use) a database with the relevant forms, queries, and modules to manipulate the data (the front end). All the front ends need to connect to the same set of data, which is termed the back end.


You don't have to "split" a database into two, but for multi-user systems every developer will recommend splitting a database.


So you need two things,

a) a central hosting for the back end, which can be on server located somewhere in the business, or on a remote server, maybe in the "cloud".

Now access doesn't work tremendously fast with remote data, so you would need to test both the connection and performance. The worse your broadband speed, the worse the experience.

b) a local hosting for the local databases. Now if you solved the first problem by having a business server, then the PC's need to be on the same LAN as well. They may work from remote locations, but there will almost certainly be performance issues. If you chose a "cloud" solution, such as azure, as mentioned, then you may be able to work from anywhere, but there may be performance issues.

There may be hosting services where you can connect to your front end database over the internet, but you would need to investigate.

c) Finally note also that although in theory all users can use the same version of the front end, the practice is different, and recommendations here will always be for each user to have a separate copy of the front end. We would not recommend having a single copy of the front end database on a server, used by multiple employees within the company.


A lot of this is going to be "try it and see". You need to be able to build a working database, and then determine the best way (or at least an acceptable way) to use within a working environment.
 

eacollie

Registered User.
Local time
Today, 08:18
Joined
May 14, 2011
Messages
159
Thank you to all! This gives me a lot to look at.
 

Cliff67

Registered User.
Local time
Today, 08:18
Joined
Oct 16, 2018
Messages
175
Hi CJ

you said this in your answer

access is not intended to work in the cloud however there are options.

1. migrate the back end to sql azure - potential issue is security - you tell sql azure which IP addresses are allowed to access the database, so not suitable for access while on the move. Benefit is you can continue to use the access front end, but linked to azure rather than the LAN based backend.

2. use citrix or terminal server - both front end and (access) backend are hosted on the server - each user has their own profile so still retains their own copy of the front end. Generally quite fast as the only movements across the internet are screen refreshes one way and keyboard and mouse events the other. Also can be run from none windows devices. Potential issue - needing to reference local files/printers/email/etc

I have found out that our company has Citrix Receiver :banghead: and from my research there is a module within Citrix that allows users to import their databases directly to Citirx.

This would stop a lot of my boss's reservations about using my DB as it means we already use a secure means to access corporate sensitive information. He is a little paranoid about his info - can't blame him though.

Do you have any experience of this? what do you need to change WRT the backend connection. The front end links the tables located on one of our servers so I assume I have to force a re connection when a user logs into Citrix and uses my DB?

any help would be greatly appreciated.

many thanks

Cliff
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:18
Joined
Feb 28, 2001
Messages
27,003
Cliff67: The most important part of this would be the ability to have separate storage for each user on the Citrix server so that you can have non-shared front-end files. The back end can be on a shared area on the Citrix server. However, be aware that you run into the issue of licensing, since the "off-the-shelf" version of Access that you get from your local technology store is a single-user version. However, Citrix will want to run the version of Access installed to its Programs folder (shared), and that requires special licensing.

With respect to the links, what you do is make a "master copy" of the FE file that you do not allow people to run. Make the links on the master copy point to the BE file in the shared area. Make the FE have a startup form that checks its run location (CurrentDB.Path, for example) and look at the connect string of any table residing in the BE file. If they are the same, make the startup form do an Application.Quit. But if the user is running from other than the shared folder, allow it to proceed.

DISCLAIMER: There are other ways to assure that nobody runs the master copy. This is one of the easier methods.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:18
Joined
Feb 19, 2013
Messages
16,555
each citrix user will have their own login and workspace on citrix server - so it is much the same environment as a windows network.

I put each users front end in their own Citrix workspace - typically something like

C:\Users\{username}\Documents

or a bit more professionally, hidden away in

C:\Users\{username}\AppData\Local

with a shortcut on the desktop

and the backend goes in a shared folder of your choice

providing you use a UNC path (\\servername\myFolder) rather than a mapped drive path (z:\myFolder), then your master front end can be copied to each user without change.

Whilst writing this I see Doc has responded - he makes a valid point about 'sharing' the access program, but that would apply to all apps including excel, work, outlook etc so check, it may be that all users are properly licenced already.
 

Cliff67

Registered User.
Local time
Today, 08:18
Joined
Oct 16, 2018
Messages
175
Thanks Doc
We have corporate licensing or Enterprise not sure which but I can check that. All other users will have run time versions on their machines. I was thinking they would have a FE copy installed on their PC but it looks like the ones who need it will have a Citrix account. We have people in the UK, USA and China who will use this DB to log technical support requests and repair requests.

Great advice about the DB path. I was wondering about stopping people using the FE directly

Many thanks

Cliff
 

Cliff67

Registered User.
Local time
Today, 08:18
Joined
Oct 16, 2018
Messages
175
Hi CJ

Great, clear advice as usual

Hopefully one day I can help someone too

many thanks

Cliff
 

Cliff67

Registered User.
Local time
Today, 08:18
Joined
Oct 16, 2018
Messages
175
and the backend goes in a shared folder of your choice

providing you use a UNC path (\\servername\myFolder) rather than a mapped drive path (z:\myFolder), then your master front end can be copied to each user without change.

The BE is already referenced like that as all the users have the BE server mapped as something different anything between F:\ through to Z:\ :rolleyes:

thanks again
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:18
Joined
Feb 19, 2013
Messages
16,555
I was wondering about stopping people using the FE directly
you know the path to the folder where the master FE is located. So the first bit of code run checks the currentdb.path against this if the same, it exits the app, perhaps with a message 'cannot be opened in this location'

There are other ways such as copy itself to the user FE location, open access there and close the master copy - which solved how you update the user FE when there are changes
 

Cliff67

Registered User.
Local time
Today, 08:18
Joined
Oct 16, 2018
Messages
175
Nice solution, maybe a batch file the copies FE then opens it as well as the checks
 

Users who are viewing this thread

Top Bottom