Access World Forums

Access World Forums (
-   Access Web (
-   -   Remote Access to Database (

HeatherO 05-11-2018 10:10 AM

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! :confused:


June7 05-11-2018 11:48 AM

Re: Remote Access to Database
This is not simple. I think options are VPN or web-based db, maybe using SharePoint.

HeatherO 05-11-2018 05:36 PM

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?

June7 05-11-2018 05:43 PM

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.

Beetle 05-11-2018 06:09 PM

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..


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...)

HeatherO 05-11-2018 07:07 PM

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 11-14-2018 06:09 AM

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?

Cliff67 12-19-2018 05:57 AM

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 (, 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

HeatherO 12-20-2018 05:10 AM

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!

Cliff67 12-20-2018 05:13 AM

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


The_Doc_Man 12-20-2018 07:46 AM

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


    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


    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.

Cliff67 12-20-2018 08:00 AM

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


The_Doc_Man 12-20-2018 09:53 PM

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.

Cliff67 12-21-2018 12:49 AM

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

isladogs 12-21-2018 01:47 AM

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!

All times are GMT -8. The time now is 11:56 PM.

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