Working Remotely with Access DB on Company Network (1 Viewer)

Lilly420

Registered User.
Local time
Today, 06:52
Joined
Oct 4, 2013
Messages
126
Hello,

I was just wondering if anyone could offer any guidance on working remotely and using 2016 Access Databases that are on our Company Network. I have a front-end on my local drive and the back-end is on a network folder as there are a few of that use the Database. It is extremely slow and has even crashed a few times...Is there anything I could be doing differently that I may not have thought of or is this a normal thing? The Database is not huge, only 31KB and is compacted regularly.

Thank you for any help.

Lilly
 

Micron

AWF VIP
Local time
Today, 06:52
Joined
Oct 20, 2018
Messages
3,476
Is the db split into front and back ends? If no, will surely cause corruption.
Is anyone accessing it over WIFI? If yes, ditto.
Compacting regularly is not necessarily a good idea. Better to base this procedure on db close IF it attains a maximum size you specify and then only after a backup.
By remotely this means what? From home or another business location?
 

Lilly420

Registered User.
Local time
Today, 06:52
Joined
Oct 4, 2013
Messages
126
Yes, front end on local and back end on network for this DB. Should all databases be set up this way when you have multiple users?

We are all working from home with COVID so we are logging in with our RSA tokens and accessing the Network and I am using my personal WIFI to do that.

We back up the DBs once per week and then run the compact or if we have an issue we run.

So the slowness and errors that are happening is to be expected with what I have told you and no way around it?

Thank you again.

Lilly
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:52
Joined
Feb 19, 2013
Messages
16,553
it depends on whether working from home is a temporary measure or going to be a way of life as you will need to invest in different technology to support access.

But one thing to check is how your forms are working - the objective is to minimise the amount of data that is transferred across the internet.

If your forms are based on a table or simple query (i.e. select * from myTable) then you are bringing the whole table across - which takes time

if you are opening your forms using docmd.openform using the where parameter then you are still bringing through the whole table - it is just that the where parameter prioritises which records it brings through first.

And don't forget your subforms, combo's and listboxes - they may also be bringing through whole tables - perhaps not an issue for a list of months or states, but a list of customers or invoices can be a whole different thing.

There are ways to open the form with an empty recordset, the user chooses a record in some way, and just those records are brought across. Or a combo requires the user to type one, two or three characters before the dropdown is populated.

Also don't forget indexing - any field which is regularly used for joining, criteria or sorting should be indexed - witht eh excepot of fields with limited number of values such as booleans. And using the initial * with Like criteria prevents the using of the index on that field.

If this is going to be a permanent change in the way you work, consider using terminal server. It has a cost, but performance is very good - almost as good as if the backend is on your local machine.

Finally, front ends should be 'hardwired' to the backend. If working from home, this means a cable from your laptop to the router. This still leaves the potential for internet interrupts causing problems, but should be reduced
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 03:52
Joined
Mar 14, 2017
Messages
8,738
We are in the same working situation with COVID-19. Although I don't disagree at all with what others have stated about Wifi, I sympathize with the dilemma, because out of all the people who work from home using some kind of VPN to get to a network, I'd think 95% of them are using Wifi - I mean that being the modern norm. I am doing this, and my Access databases still function, but definitely slower.

Great advice already given on reducing the size of recordsets..especially that about docmd.openform+where parameter, which sometimes people think means a smaller recordset.. I often do what CJ mentioned - bring in an empty recordset (with all required columns, to support my bound controls) on the form Open event. Use other portions of the interface to allow users to find record(s).
At the very worst, open any form with a reduced (query) recordset, not a table.
 

Lilly420

Registered User.
Local time
Today, 06:52
Joined
Oct 4, 2013
Messages
126
You have given me a lot to think about. Thank you.

This is temporary but could go through to the end of the year.

My forms are based tables. I will go through and index those fields you mentioned. Most of my combo boxes are based on tables with not a lot of data. But the forms that are most used are the customer form and exam data form which those tables contain a lot of data. The database has a switchboard in which the user would pick from the list and the form opens...is there a better way to do this? You mention the docmd.openform - would that be accomplished with a button versus using the switchboard I currently have in place?

Thank you for all your help with this, so appreciated.

Lilly
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:52
Joined
Feb 19, 2013
Messages
16,553
there has been a lot on the subject due to the current situation - if you are referring the access switchboard, it is not something I use so cannot help with that - but you can write your own switchboard form - might be a series of buttons which have the docmd.openform code in the click event. The changes to the form you need to open are fairly simple

To get to where I was describing, do the following for a form

1. where your form recordsource is say 'table1', change it to 'SELECT * FROM table1 WHERE false'. This will return an empty recordset.
2. the docmd.openform has a number of parameters, the WHERE parameter as already discussed, there is also an openargs parameter - move whatever you have in the where parameter to the openargs parameter
3. back to your form, In the form load event put

me.recordsource=replace(me.recordsource,'false', openargs)

alternatively, perhaps your form has a search button/combo/whatever and uses code something like

me.filter='ID=" & cboSearch
me.filteron=true



For these, do the following
1. where your form recordsource is say 'table1', change it to 'SELECT * FROM table1 WHERE false' (as above)
2. where you have the filter code above, replace it with

me.recordsource=replace(me.recordsource, mid(instr(me.recordsource,"WHERE ")+6),"ID=" & cboSearch)
 

Cronk

Registered User.
Local time
Today, 21:52
Joined
Jul 4, 2013
Messages
2,770
My experience with my clients working remotely over internet is slowness, frequent corruption of the backend and loss of entered data. All these issues have been solved by those that have followed my advice to use Citrix or similar, which in effect means that the remote PC becomes a dumb terminal driving a virtual front end at the remote location.
 

Micron

AWF VIP
Local time
Today, 06:52
Joined
Oct 20, 2018
Messages
3,476
Yes, front end on local and back end on network for this DB. Should all databases be set up this way when you have multiple users?
Yes. For some of the questions I asked I knew would be of interest to those who have more expertise in the issue of working remotely. Years ago I used Citrix to log on to the work servers but I was hard wired from my pc to my router. I don't know what risk anyone takes using wifi on a home network, especially if their pc is using wifi to a router that is connected to the router of their ISP. AFAIK, this is a common setup on home networks. I do know that I would not risk using wifi to connect to Access between the pc and server. IIRC, Citrix is an app that will buffer and do checksum validation between a home pc and the company server, but I do believe that you want that work connection to be a hardwired LAN.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:52
Joined
Feb 28, 2001
Messages
26,996
This may be just an "aside" but when I had that problem, I was with the U.S. Navy as a contractor. I had a laptop but the rule was that I had to have a hard-line connection to my router, not wi-fi, and they had software installed on the government-supplied laptop that when I connected to my local network, they would test it and verify hard-line connections. Then they would establish a VPN. Once that happened, I had full access to everything I needed. My local home network was slow compared to the in-house network, but at least I had a secure, somewhat reliable connection. There was still the matter of anything that happened on the multiple hops between my home system and the nearest MILNET connection.

For security purposes, they required the VPN. For stability purposes, they required the hard-wired connection. I'm not saying that is right for you, but that is what the Navy required for me and my fellow contractors.

I will say that ANY connection over wi-fi has a VERY high probability of an occasional drop. Unfortunately, from the network protocol viewpoint, that is not good because what it does is it locks up a part of the DB file. That is the nature of the beast when using SMB protocols, and that is what Windows File Sharing uses. A lost connection cannot be re-established (by default) and there is a sequence numbering issue that would prevent some other connection from taking over what you were doing. So just be aware that inherently in the fact of using Win File Sharing, there is a big risk in wi-fi.

Using RDP or Citrix will perhaps eliminate the network drop from Access to its files, but would introduce one from your terminal to Access. Fortunately there ARE ways (if allowed by your network IT people) to reconnect to a dropped session. So there is less risk. But there is never zero risk if any wi-fi is any part of the connection.

If you have any say at all in how this is set up for the longer term, by all means mention that some situations won't work for reasons of protocol issues and that there is a risk/reward issue to be considered.
 

Lilly420

Registered User.
Local time
Today, 06:52
Joined
Oct 4, 2013
Messages
126
Thank you all for your help, so I am going to try using the hard-line connection to my router, I just need to order the cable to do that, and I will also try a different navigation panel with what was suggest above by CJ, but may need your help with the code--I am a novice with code and get most of my help from nice people like you all. Again, thank you so much for all your guidance.
 

bob fitz

AWF VIP
Local time
Today, 10:52
Joined
May 23, 2011
Messages
4,717
Thank you all for your help, so I am going to try using the hard-line connection to my router, I just need to order the cable to do that, and I will also try a different navigation panel with what was suggest above by CJ, but may need your help with the code--I am a novice with code and get most of my help from nice people like you all. Again, thank you so much for all your guidance.
Please let us know how you get on with your proposed changes and the results
 

Lilly420

Registered User.
Local time
Today, 06:52
Joined
Oct 4, 2013
Messages
126
I will let you know and like I said I might be back for help with code...you all are truly wonderful and I am so glad we have this forum to get help.

Lilly
 

Dumferling

Member
Local time
Today, 12:52
Joined
Apr 28, 2020
Messages
102
Working remotely (like everyone else) I am hardwired to my router and have to use a VPN to log into my database BE. If I go off hardwired and onto WiFi the whole system slows down drastically and is more prone to drops. On a hardwired link with fibre (in South Africa fibre is not so widespread) I usually have a very good connection and high stability. Occasionally things slow down but then I reset the VPN and login again and it works fast.

There are some great tips here about limiting data coming over the line that I will want to understand and probably implement as the BE grows.
 

AccessBlaster

Registered User.
Local time
Today, 03:52
Joined
May 22, 2010
Messages
5,823
Agree with others who use a company VPN to network in. The VPN is setup through your IT department. Once the permissions have been settled, you can remote in without any special code.

Edit: We left our desktop computers on and remote in with laptops, the target computer must remain on.
 

Isaac

Lifelong Learner
Local time
Today, 03:52
Joined
Mar 14, 2017
Messages
8,738
Agree with others who use a company VPN to network in. The VPN is setup through your IT department. Once the permissions have been settled, you can remote in without any special code.

Edit: We left our desktop computers on and remote in with laptops, the target computer must remain on.
That worked very well for me at a certain company from my past. Simple and perhaps old fashioned, but worked like a charm! I'd have to log into a Citrix page which has its own version of RDP, then log in to my work box from there.

Unfortunately currently most at-home workers in my company, including me, only have a laptop, so the oft-repeated recommendations to use "remote desktop" ..well, there is nothing to remote into. We have some Citrix virtual desktop capability somewhere in the company, but it's not something my department has access to except in very unique circumstances. It might take me 10 years to convince them to queue up one of these environments dedicated to an Access database..
 

AccessBlaster

Registered User.
Local time
Today, 03:52
Joined
May 22, 2010
Messages
5,823
Unfortunately currently most at-home workers in my company, including me, only have a laptop, so the oft-repeated recommendations to use "remote desktop" ..well, there is nothing to remote into. We have some Citrix virtual desktop capability somewhere in the company, but it's not something my department has access to except in very unique circumstances. It might take me 10 years to convince them to queue up one of these environments dedicated to an Access database..
Yeah good point Isaac, we have an abundance of tech (thanks to the taxpayers) I need to keep that in mind!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 19, 2002
Messages
42,970
Slowness connecting to an Access BE over a VPN is a well documented issue. Sounds like your app is designed with typical old style Access techniques rather than more modern client/server techniques. You can rework the forms to bind them to queries with selection criteria and that will help somewhat but you would need to do that plus convert the BE to SQLserver to get back to something approaching a good response time.

Do you have a desktop at the office? If so, you can use RDP to connect to it. You would need to buy a license if the desktop does not have a professional version of Windows installed. Other alternatives are available such as GoToMyPC but they all require hardware for you to connect to. Neither of these solutions would go through the VPN. Or the VPN would "surround" the connection so that both the app and the be are "inside" the VPN.

A more wide-reaching solution for everyone would be having your company add an EDP or Citrix server and having everyone connect to that to run the Access apps. This method would not require modifying the app at all. It would work as it is because you would have a personal directory on the server where the FE is located and it would link to a shared directory where the BE is located. Since both the FE and BE are on the same server, response time will be better even than it was on your LAN.
 

Users who are viewing this thread

Top Bottom