Remote Access to Database (2 Viewers)

HeatherO

Registered User.
Local time
Today, 08:36
Joined
Apr 21, 2017
Messages
45
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:

Thanks,
Heather
 

June7

AWF VIP
Local time
Today, 04:36
Joined
Mar 9, 2014
Messages
5,423
This is not simple. I think options are VPN or web-based db, maybe using SharePoint.
 

HeatherO

Registered User.
Local time
Today, 08:36
Joined
Apr 21, 2017
Messages
45
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

AWF VIP
Local time
Today, 04:36
Joined
Mar 9, 2014
Messages
5,423
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

Duly Registered Boozer
Local time
Today, 06:36
Joined
Apr 30, 2011
Messages
1,808
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

Registered User.
Local time
Today, 08:36
Joined
Apr 21, 2017
Messages
45
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

Registered User.
Local time
Today, 08:36
Joined
Apr 21, 2017
Messages
45
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

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
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/forums/showthread.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
 

HeatherO

Registered User.
Local time
Today, 08:36
Joined
Apr 21, 2017
Messages
45
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

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:36
Joined
Feb 28, 2001
Messages
26,996
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.
 
Last edited:

Cliff67

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:36
Joined
Feb 28, 2001
Messages
26,996
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

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
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

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
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!
 
Last edited:

Cliff67

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
Hi Colin

many thanks for that, I've mainly developed desktop databases with multi-user access so remote access is really new to me - I found it easier in the days of replicated databases with a master and slave databases. Just a quick question, would you use the active controls dirty function to detect if the control has been used?
I assume I would use something like this to set it initially

Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl

if ctlCurrentControl.dirty then
insert timer reset here
else
do the time out bit here
end if
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
I just check what the active form and control name are in the timer event and whether they are identical to those in the previous check
Here's part of the code I use:

Code:
CheckActivity:
'-------------------------------------------------------------
'This section detects if the current active control does not change for a pre-set time.
'If after this time there is no change, form frmSystemMessages opens
'and gives the user 30 seconds to cancel, otherwise it closes the program.

'The Global variable intTimeDelay determines how much idle time to wait (in seconds) before running the IdleTimeDetected subroutine.
   
Static PrevControlName As String
Static PrevFormName As String
Static ExpiredTime
Static Timeout

Dim ActiveFormName As String
Dim ActiveControlName As String
Dim ExpiredSeconds
Dim Lockout As String

On Error Resume Next

'Check to see if any program activity
If intTimeDelay > 0 Then
'Get the active form and control name.
    ActiveFormName = Screen.ActiveForm.Name
    If Err Then
       ActiveFormName = "No Active Form"
       Err = 0
    End If
    
    ActiveControlName = Screen.ActiveControl.Name
       If Err Then
       ActiveControlName = "No Active Control"
       Err = 0
    End If

    ' Record the current active form and control and reset ExpiredTime if:
    '  1. They have not been recorded yet (code is running for the first time).
    '  2. The previous names are different than the current ones
    '       (the user has done something different during the timer interval)
    
    If PrevControlName = "" Or PrevFormName = "" Or ActiveFormName <> PrevFormName Or _
    ActiveFormName = "frmLogoutStatus" Or ActiveControlName <> PrevControlName Then
       PrevControlName = ActiveControlName
       PrevFormName = ActiveFormName
       ExpiredTime = 0  'reset
    Else
       '  . . .otherwise the user was idle during the time interval, so
       ExpiredTime = ExpiredTime + Me.TimerInterval  ' increment the total expired time.
    End If
    
    ' Does the total expired time exceed the intTimeDelay?
    ExpiredSeconds = (ExpiredTime / 1000)
   ' Debug.Print ExpiredSeconds
    If ExpiredSeconds >= CLng(intTimeDelay) Then 
       'Added clng() entering loop when intTimeDelay is larger than ExpiredSeconds
        '  . . .if so, then reset the expired time to zero . . .
        ExpiredTime = 0
        '  . . .and open the form frmSystemMessages if no activity
        If intTimeDelay > 60 Then
            strMsg = "No user activity has been detected in the last " & CLng(intTimeDelay / 60) & " minutes."
        Else
            strMsg = "No user activity has been detected in the last " & intTimeDelay & " seconds."
        End If
        
        InactivityFlag = True
        
        DoCmd.OpenForm "frmSystemMessages", , , , , acDialog, "ShutDown"
    End If
End If

HTH
 

Cliff67

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
Wow Thanks Colin.
I have started to work my way through the code based on the ideas you gave me, this gives me some extra things to think about implementing. I normally develop one part and test it to see what goes wrong then 'bug fix' I know what I want to do and I tend to write it in pseudo-English then code. I hadn't thought of checking for the control name before everything. Guess that is why I started with a debug at the first timer event lol.
 

Cliff67

Registered User.
Local time
Today, 05:36
Joined
Oct 16, 2018
Messages
175
Cheers, Have a good Christmas, I'm off soon until the new year :)
 

Users who are viewing this thread

Top Bottom