Workaround Networking Interruptions in Split Databases

[MushroomKing]

Howdy ladies and germs!

Well, this is probably a topic many of us struggle with and have it solved i guess.

GENERAL PROBLEM:
We have a big company network with multiple windows and access versions. We're running a split database (front/back-end).

When a network interruption occurs, the connection between te front and back is lost. This causes the backend to be corrupt and the application(s) to shut down along with all running processes. The on site engineers need to fix this problem almost daily!

I'm looking for a way to solve this or work around with.


Possible directions for solutions im thinking of the top of my head:

- Have excel run as data storage instead of a split database?

- Have the database run a timer, that checks the connection, if no connection> Quit! (before it becomes corrupt)?

- Have no split database but somehow make it workable for multiple users?

- Some client settings that can be changed for this?


Anyone has any ideas for this critical problem?
Some sparring around/brainstorming would help alot!

Cheers!

 

[Ranman256]

Fix your network. It should not loose connection.

You could try not have any forms direct connect to the data.
Use an bound form to a local table, enter data,run an append query.
This way the connection only need last 1 second.
For edits, run append qry to load data to local table,
Then click save to run update query.
Again, connection for 1 second.

 

[MushroomKing]

If we in ANY possition to fix the network, it would be solved today

The thing is that our global IT department does not "support" Access.
Although our argument is that they dont need to support Access, but the network causing the problems! But alas...no avail.

I think it's a cool challenge to fix this though.

Thanks for your solution though!

But what if there is an interruption going on that might last a few seconds?
Even that 1 second of trying to append would corrupt the back end right?

 

[Uncle Gizmo]

- Have no split database but somehow make it workable for multiple users?

This is often done, and it's done with Citrix...

However if MS Access is not supported then does that mean you shouldn't be using it at all?

Sent from my SM-G925F using Tapatalk

 

[The_Doc_Man]

To say that Access is not supported is to say, Ho-hum, what else is new. IT people NEVER support it because Access back-ends are just files on a file server. IT really doesn't have much to do with things like that.

MushroomKing, this is the problem you face. If your network is THAT flaky, you should not be running ANY mission-critical software on it AND this fact should be made clear to the powers that be. If your network problems relate to hardware failures, you need new hardware. If the problem is that your routers crash you need more robust routers. If your IT people say they can't fix it then either (a) their problem is their budget or (b) they are blowing smoke. You can't fix either of those but you can call attention to the problem and make it known to folks who CAN fix either of those cases.

There is no simple solution to the problem. I've thought about this many times. The common suggestion is Citrix or an equivalent. Even if you were to use a Terminal Services-based solution, losing the TS connection usually means that you have a hung session that is local to the Citrix host, which still has the potential to do you dirt anyway when you try to clear that session. Citrix, however, is slightly less vulnerable than other connection methods because the queries involved, if initiated, will have a chance to finish cleanly. It is just that if you have a session hanging with a form open or with a long-running process, a network drop can still hose your DB to tears. Less likely - but possible.

My best results with network problems came when I assured that all queries and forms used either Optimistic Locking or No Locks, and all operations on the Back End (where humanly possible) were done with SQL, which runs MUCH faster than VBA loops.

The REAL solution long-term might be something like SQL Server or SQL Express and then upconvert the Access BE to a standalone SQL engine BE. THOSE connections can die without corrupting the DB because they don't do anything until a complete command / query is presented. Further, Access works with an "active" SQL BE file and even has some tools to help with the conversion.

It's all about "windows of opportunity." If you take steps to narrow the window during which your queries are active, you reduce your risk. That is what you have to try to do.

 

[Pat Hartman]

Doc said it all. I'll summarize.
1. The network is the problem - Fix it. I've run many mission critical Access apps with Jet/ACE and only rarely encountered intermittent network issues. Estimate the cost of the daily down times. How does it impact users and for how long? How much time does it take the IT people to recover? How do you handle lost data. Extrapolate the cost for a year. Surely it adds up to enough to justify a competent analysis of the network to isolate the problem.
2. Convert the BE to SQL Server. Ultimately, this is the soundest solution. Depending on how cognizant of client/server techniques the app designer was, you may have little trouble with the conversion or have to re code a lot of processes and queries. This of course doesn't fix the outage problem. Access will still freeze up when the connection is lost but you won't have the data loss issues. The users will just close the app and reopen it to continue what they were doing.
3. The suggestion to change to unbound forms was essentially a suggestion to can Access as your FE and switch to something "better" like a web based solution. The whole point of Access is that it is a RAD (Rapid Application Development) tool and if you don't use the features that make it RAD, you are just wasting your time using Access as an FE. Although I occasionally create unbound search forms, I would never create unbound update forms and that is what you would have to do to avoid the connection drops.
4. I love Citrix. It is an excellent solution for distributing an Access application outside of your network. Even though you won't need to change the Access app to do this, there is an ongoing per user cost to implement this solution. In one of my clients, the people in Farmington, Connecticut use the app via the LAN. However there are other users that span half the globe. San Francisco to Paris and other cities in Europe. The Citrix users sometimes get better response than the LAN users because the FE and BE are both located on the Citrix server so there is no network latency for the FE to communicate to the BE as there is with the LAN users where the FE is on their c: drive and the BE is on the Citrix server.

 

[CJ_London]

We're running a split database (front/back-end).

Not clear from your post - does each user have their own copy of the front end on their machine? or do they all share the same front end?

When a network interruption occurs

are all users hardwired to the network, our using wireless connection - the latter is not advised and introduces a further opportunity for interruption

- Have excel run as data storage instead of a split database?

Excel is a) single user and b) does not use indexing so really cannot see how you would consider this an option.

 

[AccessBlaster]

Every network goes down from time to time. But not every database will become corrupt. Re-linking not corruption is the biggie for me.

IT support for Microsoft Access, Sharepoint or anything else YOU decide to create is on YOU not on your IT dept. For the most part they want nothing to do with your creations.