Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-09-2018, 12:00 PM   #1
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Access without network server

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!

eacollie is offline   Reply With Quote
Old 10-09-2018, 03:31 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,700
Thanks: 40
Thanked 3,471 Times in 3,359 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access without network server

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-18-2018, 04:57 PM   #3
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Access without network server

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.

eacollie is offline   Reply With Quote
Old 10-18-2018, 07:10 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,553
Thanks: 13
Thanked 1,452 Times in 1,384 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Access without network server

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-18-2018, 07:10 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,308
Thanks: 70
Thanked 1,413 Times in 1,304 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Access without network server

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-19-2018, 12:36 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,700
Thanks: 40
Thanked 3,471 Times in 3,359 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access without network server

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...d.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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-19-2018, 06:43 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,501
Thanks: 54
Thanked 959 Times in 927 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Access without network server

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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-20-2018, 05:32 AM   #8
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Access without network server

Thank you to all! This gives me a lot to look at.
eacollie is offline   Reply With Quote
Old 03-04-2019, 06:47 AM   #9
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 64
Thanks: 27
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Access without network server

Hi CJ

you said this in your answer

Quote:
Originally Posted by CJ_London View Post
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 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
Cliff67 is offline   Reply With Quote
Old 03-04-2019, 07:30 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,308
Thanks: 70
Thanked 1,413 Times in 1,304 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Access without network server

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Cliff67 (03-04-2019)
Old 03-04-2019, 07:42 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,700
Thanks: 40
Thanked 3,471 Times in 3,359 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access without network server

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Cliff67 (04-02-2019)
Old 03-04-2019, 07:45 AM   #12
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 64
Thanks: 27
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Access without network server

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 is offline   Reply With Quote
Old 03-04-2019, 07:48 AM   #13
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 64
Thanks: 27
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Access without network server

Hi CJ

Great, clear advice as usual

Hopefully one day I can help someone too

many thanks

Cliff
Cliff67 is offline   Reply With Quote
Old 03-04-2019, 07:51 AM   #14
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 64
Thanks: 27
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Access without network server

Quote:
Originally Posted by CJ_London View Post

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:\

thanks again
Cliff67 is offline   Reply With Quote
Old 03-04-2019, 07:57 AM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,700
Thanks: 40
Thanked 3,471 Times in 3,359 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access without network server

Quote:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Cliff67 (03-04-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Client/Server app issue on a network. jasabel General 5 08-23-2012 03:17 AM
Question Find if a Network Server is on AliyuKatsina General 8 06-22-2012 04:43 AM
Network Print Server Question rkrause Hardware Questions and Answers 2 06-05-2009 04:07 AM
Office network to web server Fernando General 0 06-25-2007 07:41 AM
Hosting DB on a network server lilFlip General 4 11-01-2006 03:01 PM




All times are GMT -8. The time now is 05:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World