Dan Waters
df.waters at outlook.com
Fri Feb 20 16:46:49 CST 2015
Hi Janet, What I do is add a checkbox column (named ErrorSent) to the error log table. When the error is logged, that column is left = False. When the app starts up, a procedure will run looking for any rows where ErrorSent = False. You'll need an error log report which uses a query as it's recordsource. That query will only select any rows where ErrorSent = False. Send an email with that report attached. When that's done, set any unchecked ErrorSent columns = True. You could also set this to run immediately after the data transactions instead of on startup to be notified immediately. Good Luck! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Janet Erbach Sent: Friday, February 20, 2015 16:24 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Backend database corruption Dan - How do you look for an unsent error? I do have a pretty good error logging routine, but I may be missing something. My error logging does show connections dropping on a variety of machines - which is pretty common out on the shop floor. Janet On Fri, Feb 20, 2015 at 1:38 PM, Dan Waters <df.waters at outlook.com> wrote: > Hi Janet, > > One more thing I was thinking of is to set up error trapping and > recording in the FE apps in the procedures where the data transfer is > happening. You can set up an procedure that looks for an unsent error > and sends that to you by email so you get a timely notice of when an > error happened, along with the error code and especially the > description, and the specific PC's name. > You can search on error descriptions to get a fuller meaning, and that > can help prove/disprove an idea of what's going wrong. If this all > happens on one or two problem PC's, you'll know! > > Good Luck! > Dan > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Janet > Erbach > Sent: Friday, February 20, 2015 13:01 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Backend database corruption > > THANK YOU ALL for your responses - this is all very helpful. I'm > going to push for hard wiring all of the connections as soon as > possible; I also like the idea of logging when the write operations > are happening to see how much overlapping traffic there is. > > I think the CSV approach is very interesting too, and will bring that > up in a meeting next week along with presenting the SQL backend > option. I think we would try the CSV approach first. It would be > difficult to convert to a SQL backend, I think, on the 20 hours a week > that they've alotted me...especially since more than half of that time is via remote connection. > > Again - thank you all. I am much relieved to have a few options to pursue! > > > > On Thu, Feb 19, 2015 at 4:25 PM, Darryl Collins < > darryl at whittleconsulting.com.au> wrote: > > > Yes. John is spot on. These would be my primary solutions to this > > issue as well. > > > > Cheers > > Darryl. > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com [mailto: > > accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby > > Sent: Friday, 20 February 2015 8:06 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Backend database corruption > > > > Loss of connection while writing to an Access DB is a known issue, > > never fixed, and probably unfixable. > > > > Don't use Wifi / WAN with an Access BE. > > > > The best option is to move the BE to a SQL Server BE. That will > > absolutely solve this issue. If you must continue to use Access as > > the BE, then write CSVs to a directory on the server and have an > > Access app RUNNING ON THE SERVER watch for these CSVs and import > > them into the table. At least if the write to the CSV file is > > interrupted, it does not corrupt the BE. > > > > John W. Colby > > > > On 2/19/2015 3:01 PM, Janet Erbach wrote: > > > Hello! > > > > > > It's been years since I've addressed this group, so please be > > > patient with me while I get back into the swing of this. > > > > > > I've been an Access developer for the last 15 years or so. Until > > > recently I created straightforward apps used on a small group of > > > hardwired networked computers that had 5 or 6 users in the app at > > > the > > same time. > > > > > > Last year I took a job with a large manufacturing plant, and just > > > deployed a very complex app that I co-wrote with one of the > > > access-fluent production supervisors. It is supposed to run > > > non-stop on 20+ machines, all with WIFI connections. It writes > > > machine production data to a set of front-end tables; every 15 > > > minutes the app checks to see if there is network connectivity - > > > if there is, the front-end table data is posted to the back-end > > > tables on the network, the front-end tables are emptied, and the > > > loop begins > again. > > > > > > The app worked pretty well when it was running on one or two machines. > > > Now that it's up on 20 machines, the back end is corrupting > > > multiple times during the day - which, of course, brings the whole > > > show to a halt. The error log seems to indicate that loss of a > > > network connection during the back-end write operation proceeds the corruption. > > > > > > I have two questions. Will hard wiring the network connection to > > > these machines go a long way towards stopping the corruption? Is > > > there anything else that could be contributing to this that I need > > > to be > > aware of? > > > > > > Thank you for your help. > > > > > > Janet Erbach > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com