UtterAccess.com
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
> Force Access To Close When Connection Is Lost?, Access 2010    
 
   
Gab-Hop
post Feb 28 2018, 03:24 AM
Post#1



Posts: 150
Joined: 8-March 16
From: New Zealand


Hello All
I have a db that is used by many users each with there own FE and the BE resides on a server
some users have tablets ect and connect through Wifi , so connection is easily lost
The problem I have is that if the user looses connection to the server the FE comes up with errors but I cant seem to close it , the errors just keep popping up
The only way I seem to be able to close it properly is through the windows task manager
this will close the FE regardless of any errors.
What I would like, is to have the db automatically close when the connection is lost
Is there some code that will force the db to close with which I can trigger from the first error I get?
Application.Quite will not do it properly there is still a lock file in the directory and when I try to open the db again I just get all the errors all over again
Any help would be much appreciated
Go to the top of the page
 
moke123
post Feb 28 2018, 05:48 AM
Post#2



Posts: 1,279
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Trying to run access over wifi is not recommended.
you will encounter corruption issues especially if your encountering connection problems.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 07:10 AM
Post#3



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Yep, Moke's got it. That really is the bottom line for now. Maybe some day Microsoft will make access more tolerant but not yet.

One idea, though.....Access does allow you to adjust the timeout values....so it would be a bit more persistent in waiting for the connection to come back before tossing it's cookies. That is somewhere in the file->options.....I run across it all the time when I'm not looking for it, but can't find it now...somebody will chime in with where to find it though....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
DanielPineault
post Feb 28 2018, 07:14 AM
Post#4


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



It's simply not possible. This has been mentioned and suggested in the past and sadly there still remain no way to handle it. This is why following best practices is so important to ensure you don't corrupt your database. Access should only ever ben run over a wired LAN connection. No WAN and/or No WiFi connections! If that is a requirement then you need to implement proper workarounds or switch technologies altogether. See: https://www.devhut.net/2016/09/24/access-ba...edrive-dropbox/ (applies to WiFi connections as well). Access is a network/bandwidth hog and need the speed and stability of a wired LAN connection, 2 things WAN and WiFi connections do not offer.



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
zaxbat
post Feb 28 2018, 09:33 AM
Post#5



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you design your app with full error checking (with persistent check and retry logic) at every data access point, and if you make sure that your queries are all quick and simple, and if you boost the ODBC timeout value....that will certainly help. But the truth is inescapable, Access has never recommended or promoted use in a wireless environment.

Has anyone ever tried running the app exclusively against the dataset clone and then only commit back to the actual database when all ran smoothly with no errors? That would be a lot of work...not sure it's worth that much trouble or even possible.

P.S.- I did find the ODBC timeout property......from design mode on any query right click to pull up the property sheet for that query and it will show ODBC timeout. Try to boost it higher. Oh, and that is not a system value it is there for each and every query...need to boost the timeout value for each query that seems to be causing problems.
This post has been edited by zaxbat: Feb 28 2018, 09:44 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
LPurvis
post Feb 28 2018, 11:52 AM
Post#6


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I know you've got a few replies here already, but I just want to check your situation due to some uncertainties...

Is this an Access ACCDB/MDB file that you're connecting to on the server as your datasource? Or is it actually an ODBC connection to a server, such as SQL Server?
If the latter, then you're OK to continue with your wireless methodology. If the former, the advice to move then becomes vital.

Connection recovery using an ODBC source is an improvement made in the later updates of Access 2016. So I'd suggest considering that move.
If you're not using an ODBC connection, your connection handling will change and the ODBC settings won't be relevant.

Yes, you can error handle code connection errors, but your bound forms would need to be handled at the Data Error level. (Things like DataErr = 3024 being Network failure if that was what you're encountering.)
That could become quite arduous and you have to ask if it's worth it, and since you shouldn't be using that platform anyway for a file server, time to move on to SQL Server for example?

You could poll the MDB reasonably frequently in code and look for a connection failure, and exit the application before your UI object fail, but again, that's just a band aid on a larger problem.

Cheers

--------------------
Go to the top of the page
 
Gab-Hop
post Feb 28 2018, 03:45 PM
Post#7



Posts: 150
Joined: 8-March 16
From: New Zealand


Thankyou all for your suggestions
QUOTE
Connection recovery using an ODBC source is an improvement made in the later updates of Access 2016. So I'd suggest considering that move.
this is very interesting
And I am considering moving the BE to SQL server but that is a long term plan and with a DB of this scale it will take some time and a bit more training for me.
LPurvis
QUOTE
You could poll the MDB reasonably frequently in code and look for a connection failure, and exit the application before your UI object fail, but again, that's just a band aid on a larger problem.
This is exactly what I was trying to acheve
But currently if I pull the network plug and immediately try to close the DB it crunches for a minute and then starts coming up with all sorts of network related errors (I cant seem to close it before it runs into problems)
I will try and adjust the timeout values as this may help , bty what other things will the timout values affect?
I hoped to use code that I would put on a timer to check the connection and if BE is not found then FE would close automatically
But Currently the only way to close the DB if the connection is lost is through end task in the task manager
Could I call or create an external script via VBA which could use something like power shell to end the task?
Can anyone point me in the right direction here?
thanks again

Go to the top of the page
 
zaxbat
post Feb 28 2018, 04:56 PM
Post#8



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Access has a .quit command that will end it....but that is assuming you have control at that point. If you do a thorough job of polling and updating a flag (probably a public variable at global scope) that you can monitor as part of every loop in every critical task so that you can bail out that will help a lot. However, in many cases you will not be able to catch it (maybe because the app is in the middle of a huge update query). At times like that you have no control....you do not even have enough control to call out to a shell command....

I'm guessing that you need an external monitor program that you start prior to your main app that can end the access task if the connection is interrupted. Just thinking out loud on that part.....never tried anything like that before. But even if you can make such a monitoring program....that does not guarantee that the data will be safe.
This post has been edited by zaxbat: Feb 28 2018, 04:58 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Gab-Hop
post Mar 1 2018, 05:01 PM
Post#9



Posts: 150
Joined: 8-March 16
From: New Zealand


Hello again
Just working through the timouts
zaxbat,
QUOTE
P.S.- I did find the ODBC timeout property......from design mode on any query right click to pull up the property sheet for that query and it will show ODBC timeout. Try to boost it higher. Oh, and that is not a system value it is there for each and every query...need to boost the timeout value for each query that seems to be causing problems.
how does this apply to comb/list boxes. With these even if I change the ODBC timout it reverts back to zero after I close it , and do these effect things the same as saved query's do
Go to the top of the page
 
zaxbat
post Mar 1 2018, 07:48 PM
Post#10



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Did you see the comment from LPurvis above? Unless you are using ODBC connection then changing the timeout value should not help. Can't think of anything that will help. It is possible that the problem is in your coding and/or SQL techniques. Perhaps you need some better table indexing to speed everything up. Maybe you have some inefficient loops. I recall one really big report that was just a hog and took forever. I found by putting a new index in it cut the report build time by over 90%....but.... Just throwing ideas out there. Hard to say without a full code review...and I don't think I have the time for that any time soon.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 08:19 PM