X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Persistent Connections, Methods And Corruption Risk, Any Version    
post Jan 23 2020, 08:18 PM

Posts: 8
Joined: 23-January 20

Most Access gurus recommend opening a persistent connection to improve performance when opening objects. But, where connectivity is spotty unnecessary open connections present risk.

There are two methods I've seen of maintaining a connection; I'm wondering if one is less risky than the other, or if both are a bad idea where corruptions happen frequently:

1) Open a hidden form bound to a small table, or

2) Use DAO and Set dbsOpenDb = OpenDatabase("H:\folder\MyBackend.accdb") (and keep the dbsOpenDb variable alive)

Is the second method any safer since it doesn't touch any records? Just a hopeful wish. Otherwise, I'll want to disconnect the frontend (but not close it) as often as possible.

I've seen code that will periodically check a table and boot users if a flag is set. The same code will boot them if they leave the database unattended. I think I could alter it to close any bound forms, open tables or queries instead? This would be less disruptive to my users than a full shutdown while reducing corruptions.

No, I don't have easy access to SharePoint or SQL Server. Just CITRIX. Trying to avoid CITRIX as this would complicate things for users.

Thoughts? Thanks, -Tony.
Go to the top of the page
Doug Steele
post Jan 23 2020, 10:11 PM

UtterAccess VIP
Posts: 22,246
Joined: 8-January 07
From: St. Catharines, ON (Canada)

If connectivity is spotty, you run the risk of corrupting your back-end database regardless which approach you take.

Why is connectivity spotty? You're not using WiFi, are you?

Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Jan 24 2020, 12:09 AM

Posts: 8
Joined: 23-January 20

Hi Doug, nice to get a pro on the line. Yes, sometimes we are Wi-Fi, sometimes not, sometimes VPN, sometimes not. WAN situation. No matter what I tell my users about the risk, sometimes they have to access the data via telework and the connection is super slow. I'm trying to minimize risk given these conditions. I know I can't eliminate it. Any improvement I can give them in performance or stability will be appreciated. Wanted to discuss the options before pursuing CITRIX. Might be able to beg for access to a SharePoint server too, but from a different org and therefore politics involved.
Go to the top of the page
post Jan 24 2020, 07:38 AM

UtterAccess VIP
Posts: 8,031
Joined: 24-May 10
From: Downeast Maine

I don't think there is much risk from viewing the records, although it can be very slow. The corruption risk arises during a write operation, as I understand. Access creates a local instance of the database engine that processes the data and writes it to disk. Other systems such as SQL Server, Oracle, etc. send the raw data to the server, where it is processed and written to the local disc. I suppose there's a stop bit or something when all of the data have arrived. In any case, it can tolerate an unsteady connection with little risk.

Citrix and Sharepoint are other options, although I have not used either, so cannot say more than that I have read about them. I am working on converting databases to SQL Server, starting with the ones people are most likely to use remotely.

The way I often handle a persistent connection when I have an unbound switchboard that stays open all the time is to bind the switchboard to a small table. Same effect as using a hidden table, but my thinking is that if the form is open anyhow I may as well use it. If the form that is open all the time is bound, of course that takes care of the connection. A hidden form may be the best option if no form is always open.
Go to the top of the page
post Jan 24 2020, 08:04 AM

UtterAccess Administrator
Posts: 10,454
Joined: 7-December 09
From: St. Augustine, FL

I don't think there is much risk from viewing the records, although it can be very slow. The corruption risk arises during a write operation, as I understand

I'd wager the same.

Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
post Jan 24 2020, 10:56 AM

UtterAccess VIP
Posts: 4,752
Joined: 5-June 07
From: UK


For what it's worth, I use a variation of your second idea. I have a dummy table - not used for any other reason than maintaining the persistent link, and then open a recordset to that table. You can test whether the persistent connection is active by just testing whether the recordset object is nothing. I imagine you can do the same with your method as well.

Dave (Male)

(Gemma was my dog)
Go to the top of the page
post Jan 24 2020, 11:32 AM

Posts: 1,043
Joined: 12-November 03
From: Iowa Lot

Alternative to Citrix --
Can the user remote in to a desktop that is connected local to the backend?
Go to the top of the page
post Jan 24 2020, 11:53 AM

UA Moderator
Posts: 77,523
Joined: 19-June 07
From: SunnySandyEggo

Hi Tony. Welcome to UtterAccess! welcome2UA.gif

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Jan 24 2020, 12:32 PM

Posts: 8
Joined: 23-January 20

Thanks for the welcome. I've been haunting this site for years. I think I was a member once before. Remoting into desktops is not an option. I'm starting to think about a replicated design, storing the tables (just 2) on the front-end files and then storing the IDs of rows changed in a backend file, and a copy of the master tables in another. In this way the only time they would be connected would be when synchronizing data. Any simple examples of this you guys know about, something I can download or study?
Go to the top of the page
post Jan 24 2020, 01:19 PM

Posts: 8
Joined: 23-January 20

Or perhaps unbound forms is the way to go? I don't want to overcomplicate things, but we need something that works with a very slow pipe.
Go to the top of the page
post Jan 24 2020, 03:58 PM

UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK

I use the same system as Dave (gemma the husky) - a persistent connection using a hidden form bound to a table tblKickout with one boolean field and one record.
In my case this doubles up as a method of kicking out users on the rare occasions the application needs to be closed for maintenance by checking the boolean value every 30 seconds

As stated there is no risk when users VIEW data using a wifi connection. The risk is when a connection is broken if they are EDITING data.
In the past I tried to mitigate this risk by attempting to make forms read only for using on WiFi.
However, whilst you can easily detect whether users have a wireless connection, there seems to be no method using VBA of detecting whether they are actually using it.
NOTE: I would be VERY pleased to hear from anyone with evidence to the contrary

I trialled displaying a warning message for users with a wifi connection but this was very unpopular so I dropped it.
Suggest you try educating your users not to write to the db on a wireless connection. Good luck with that! smirk.gif

FWIW - using a SQL Server BE makes it much more immune to issues caused to dropped connections

Colin (Mendip Data Systems)
Website, email
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    24th February 2020 - 06:25 PM