Go Back   Access World Forums > Microsoft Access Discussion > Access Web

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-11-2018, 10:10 AM   #1
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 40
Thanks: 11
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Remote Access to Database

Good Afternoon,

I have built an Access database that I need our inspectors and maintenance technicians to be able to connect to (via an IPAD) from the apartments they are inspecting or performing maintenance in. Our office staff uses the database sa well. Right now, the BE sits on our server and each of our office personnel has a FE saved to their desktops. Ideally, we would like a "real-time" connection that updates the data as soon as it's changed in the field. I've started to research this and think that maybe remoting in to our server would be the way to go. Looking for feedback from those of you who have first-hand knowledge. What would be the best way to accomplish this? All advice welcome!

Thanks,
Heather

HeatherO is offline   Reply With Quote
Old 05-11-2018, 11:48 AM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 1,068
Thanks: 0
Thanked 254 Times in 254 Posts
June7 will become famous soon enough
Re: Remote Access to Database

This is not simple. I think options are VPN or web-based db, maybe using SharePoint.
June7 is offline   Reply With Quote
Old 05-11-2018, 05:36 PM   #3
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 40
Thanks: 11
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Remote Access to Database

Today we were able to successfully connect to the BE using VPN. What would be the advantages of using SharePoint instead? Isn't this being phased out? Also, we are concerned about the number of users that can successfully remote into the BE at one time. Any thoughts?

HeatherO is offline   Reply With Quote
Old 05-11-2018, 05:43 PM   #4
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 1,068
Thanks: 0
Thanked 254 Times in 254 Posts
June7 will become famous soon enough
Re: Remote Access to Database

Yes, I have heard SharePoint is going away. Never used it. I would expect accessing db via VPN by multiple users would be no different than accessing locally on network from the db point of view. Users may experience slow data exchange. Depends on bandwidth I suppose. I work at home and all access to office is VPN to servers thousands of miles away. However, not using Access for database. And linkup is over cable, not wireless. Wireless would be a performance issue.
June7 is offline   Reply With Quote
Old 05-11-2018, 06:09 PM   #5
Beetle
Duly Registered Boozer
 
Join Date: Apr 2011
Location: Camp Swampy (Denver, CO)
Posts: 1,806
Thanks: 13
Thanked 458 Times in 449 Posts
Beetle will become famous soon enough Beetle will become famous soon enough
Re: Remote Access to Database

If you're talking about a situation where each remote user actually has a workstation at the home base on the LAN, and they are using VPN to connect to their local machine and run the application from there, that may be doable. However..

Quote:
Today we were able to successfully connect to the BE using VPN
it sounds like you are trying to run the front end on the remote device and connect to a Access (Jet/Ace) back end via VPN. This is a recipe for disaster. You will almost certainly end up with corruption in this scenario. Access is simply not designed to operate over WAN or internet. MSoft made a poorly implemented attempt at Access web apps in recent years, but that was more or less a failure and has been scrapped. Terminal services (i.e. Citrix, etc.) is an option, but again, that is where the remote user logs into a profile on the server and the app is run from there.

You could also try using a different database (SQL Server, MySQL and the like) and use an Access front end with unbound forms, etc. but then you are losing all the advantages of using Access to begin with (rad, bound forms...)
__________________
Sean Bailey

Dim Thirsty As String
Thirsty = "Insert Into Glass (Select Beer From Fridge)"
DoCmd.Execute Thirsty, dbPourAnotherOnError
Beetle is offline   Reply With Quote
Old 05-11-2018, 07:07 PM   #6
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 40
Thanks: 11
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Remote Access to Database

The users would remote in to a virtual desktop that is housed on the same server as our BE. The FE is saved in that desktop.
HeatherO is offline   Reply With Quote
Old 11-14-2018, 06:09 AM   #7
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 40
Thanks: 11
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Remote Access to Database

Just a little update here - we have done this successfully and it worked beautifully!!

We are now looking to make the database accessible over the internet so that our potential tenants can not only apply online, but also update their application data themselves, etc... possibly eventually have our maintenance technicians connect to it to receive and update their work orders. We would want real time data.

I've done some research and am just not understanding the whole sharepoint mess... it looks like Microsoft is no longer going to support this? Unless it's "on-premises"? How exactly does this work and are there other options?

HeatherO is offline   Reply With Quote
Old 12-19-2018, 05:57 AM   #8
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 39
Thanks: 12
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Remote Access to Database

Hello All,

I've been trying to research the best way to access a BE, there is a lot of, I'll not say misrepresentation, by some of the hosting sites.

I've written a db split into FE/BE that logs our technical support calls and repairs (automatically generating the respective number assignment). My boss thinks that it should be rolled out to the company, which I agree. Now here is the problem, we have offices in the USA, Hong Kong and India.
The India office will not get it as the internet coonection is flakey as they come.

However, the db holds our customer details which is corporate sensitive, so he definitely does not want it published on the web. This would mean a re-write removing serveral tables which are intragal to the overall design of the db.

After reading Pat Hartmans excellent post on publishing on the web (https://www.access-programmers.co.uk...d.php?t=241226), I'm still at a stand still.

HeatherO you say that you have been using VPN, have you had any problems with this? If I can do it that way then my boss's objects will be removed and I don't have to re-write the db
Cliff67 is offline   Reply With Quote
Old 12-20-2018, 05:10 AM   #9
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 40
Thanks: 11
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Remote Access to Database

Hi there! Yes, we had great success with it! Our users removed in to a virtual desktop from IPADS anywhere in the field. The only issue we really had was that our server is pretty old so there were issues with lag time periodically... nothing major but definitely noticeable. We had to log each IPAD on one by one or we would crash our server. But once they were logged in, all was well. I would imagine a better server and a wired connection will solve those issues. Goodluck to you and please reach out if I can be of any further assistance!
HeatherO is offline   Reply With Quote
Old 12-20-2018, 05:13 AM   #10
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 39
Thanks: 12
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Remote Access to Database

Hi Heather

thank you, I am looking at using a terminal server or Citrix just got to get costings for them and see what the boss says

Cliff
Cliff67 is offline   Reply With Quote
Old 12-20-2018, 07:46 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,713
Thanks: 64
Thanked 1,247 Times in 1,146 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: Remote Access to Database

Note that using a VPN plus TS/Citrix to a host that is local to the primary network will prevent you from corrupting the BE if the remote link drops, but you will still have the problem of potentially hung sessions. And Access DOES claim a limit on the number of sessions you can have into a BE file.

If you are going down the "remote" path, you should look into a timer system behind your forms such so that if your network connection drops, that localized host (that is acting as your intermediary) will eventually kill itself as cleanly as possible. Granted, it is a pain in the toches, but if you want to protect your BE as much as possible, the hosted FE sessions have to be able to go away on their own if the VPN carrier goes down.

Just to be clear, running a remote FE over the VPN doesn't address the problem of unstable networks. All it does is allow you to run Windows File Sharing protocols remotely. A bad network is still a bad network no matter how fancy you make the protocol exchanges.

The article you mentioned relates to the cost of upsizing to web-based methods, and they are not trivial. IF you want to stay with Access, you have to realize that it was originally designed with SMALL businesses in mind. It is a welcome but nonetheless somewhat accidental fact that Access up-scales nicely when you have an intelligent back-end rather than a passive Windows File Sharing back-end.

I ABSOLUTELY am not telling you that you can't do what you are trying to do. I am merely trying to point out that you might not be finished as quickly as you had hoped.

The method I used when I had to do this sort of form time-out thing - and it DID NOT involve remote services - was about like this:

1. In a general module, add a public variable for the last time of day that someone did anything in the FE. Add some functions that can compute elapsed time from that "last action" time to now (or more specifically, the Now() function). My particular function just used

Code:
    Public LastTouch as Date

    ...

    TouchSince = CLNG( 86400 * ( Now() - LastTouch ) )
That gave me seconds since the last action.

2. The last action slot was updated at any event considered significant. I put in code that said

Code:
    LastTouch = Now()
This one line of code went behind any event that involved a voluntary action on the user's part. It was on the switchboard AND on the detailed action forms. I put it behind the GotFocus events, the command buttons, and on the OnCurrent event. I also put it behind the OnFormat event for reports so that folks could flip through the reports for quite a while.

3. In every form's OnTimer event, I set them up so that they would "tick" every 10 minutes. They tested the "seconds since last touch" time and, if the form appeared to have been idle for longer than one hour, the timer would set a flag to mark "timed shutdown" and then would trigger a Me.Undo followed by a Me.Close. The switchboard, however, had a longer lifetime. I gave it two hours. But EVERYTHING tested the same "LastAction" time of day variable.

In my case, it was because people would leave their sessions on overnight and their terminals would exit due to idle policies from the Group Policy rules. The main problem there was that it would be an "ugly" shutdown with sessions still open because that Windows-based exit would not go through the normal protocol handshakes. We were always trying to get folks to log out so we could do scheduled maintenance. But the same concepts would apply, I think.

This safeguard wasn't perfect because some people would just fail to reply to a modal dialog input box or message box and the timer would have issues because of it. But it DID get a lot of people to leave the DB in a graceful and timely manner. The same concept would apply to hung Terminal Services sessions as well as forgetful users, so think about how you want to approach this problem and how much you are willing to do.
__________________
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.

Last edited by The_Doc_Man; 12-20-2018 at 07:50 AM. Reason: clarified
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Cliff67 (12-20-2018)
Old 12-20-2018, 08:00 AM   #12
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 39
Thanks: 12
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Remote Access to Database

Thank you Doc_Man for your insight. I had an idea that it would not be as straight forward as just getting the VPN going and set up the terminal server.

What a fantastic idea of putting a last Touched function behind everything, I will definitely be implementing that.

We are a small to medium sized company, there will be possibly 5 users in the UK and 5 in the US with not all people connected in at the same time using the back end.

The US is roughly 5 hours behind the UK so at 12 noon it will be 5pm in the UK most will be going home with some overlap

Once again thank you for your guidance

Cliff
Cliff67 is offline   Reply With Quote
Old 12-20-2018, 09:53 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,713
Thanks: 64
Thanked 1,247 Times in 1,146 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: Remote Access to Database

Good luck and DON'T be surprised if you have to play a bit with the forms to get a good timer-based shutdown. It is sensitive. I would post the code that I used but I don't own that DB and since I retired, I lost all rights to access it.
__________________
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 (12-21-2018)
Old 12-21-2018, 12:49 AM   #14
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 39
Thanks: 12
Thanked 1 Time in 1 Post
Cliff67 is on a distinguished road
Re: Remote Access to Database

Thanks for the warning. I will start with 10 minutes and tweak them as I see which ones are used the most. It might be worth my while logging the form/control usage on the lesser used ones
Cliff67 is offline   Reply With Quote
Old 12-21-2018, 01:47 AM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,791
Thanks: 96
Thanked 1,942 Times in 1,806 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Remote Access to Database

Here's a slight variation on the same idea which allows for both:
a) orderly closedown after a specified period of inactivity
b) enforced closedown where essential maintenance is required

NOTE: This approach doesn't require code to be added to each individual form control

When users login, open a hidden form (frmLogoutTimer) with a timer event - I set this at 30 seconds

a) Inactivity Timer
The timer event detects whether the current active control has not changed during a pre-set time.

A global variable intTimeDelay determines how much idle time to wait (in seconds) before running the IdleTimeDetected subroutine.
For our clients, most found 20 minutes = 12000000 sec) suitable
(If the feature isn't required, just set intTimeDelay =0)

If after this time there is no change, a warning form frmSystemMessages opens and gives the user 30 seconds to cancel, otherwise it closes the application

The code detects the currently active form and control and resets the ExpiredTime if:
1. They have not been recorded previously (code is running for the first time in that login).
2. The previous names are different than the current ones i.e. the user has done something different during the timer interval
3. The user does something to wake up the system during the 30 seconds between frmSystemMessages opening and the application closing

b) Forced closedown
Although hopefully rare, there may be times when it is necessary to close the database for essential maintenance
This is implemented using a table tblKickout with one boolean field Kickout which can be set true by the program admin
The same hidden form (frmLogoutTimer) also checks whether the Kickout field is true

If so, another form frmLogoutStatus appears and counts down a 5 minutes interval before the app is closed automatically.
This cannot be cancelled by the user
Before closure, any unsaved records are auotmatically cleared

In addition, new users are unable to login when Kickout= true

The forced closedown form used by the sysadmin shows a list of logged in users so they will know when nobody else is logged in
At most this takes around 6 minutes using the above intervals

After maintenance is complete, the sysadmin sets Kickout = False and sends an automatically generated email to all users stating the app is avaialable again

As the Doc indicated, getting all the above working 100% reliably did require several tweaks to the code.
However, once we had done so, we completely eliminated problems with recalcitrant users staying logged in

NOTE: One last thing.
Having an always on hidden form linked to a BE table improves performance as it maintains a persistent network connection
However, an always on hidden form with a timer event does have 2 disadvantages
a) minor flicker each time the timer event triggers - I wouldn't use an interval of less than 30 seconds. You may prefer longer than that
b) working in the VBE in a live system with a timer event can cause all sorts of issues e.g. cursor moving in random ways
EITHER set the interval to 0 if you MUST work in the VBE on a live system OR much better still ONLY do development work in a system that's not live!

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 12-21-2018 at 02:20 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Cliff67 (12-21-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Data From Remote Access Database to a Local Access Database twcaddell Modules & VBA 3 01-26-2015 06:02 PM
Remote Database Access abbaddon223 General 7 06-22-2010 02:30 AM
[SOLVED] Remote DataBase Access aimran Tables 0 06-12-2006 09:00 PM
Web Programming or remote database access peggypph General 2 10-24-2004 02:53 AM
Remote Access to a Database Adam Highbury General 8 08-21-2003 04:00 AM




All times are GMT -8. The time now is 03:51 PM.


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