If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
I have been trying to diagnose this problem for weeks now. I have a
FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#2
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
My initial impression is 3000 Snapshots is a LOT!
I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#3
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Ron,
Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#4
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
If it were me I rework the logic to reduce the number to something really
low like 20. If that worked I would try 40, and so on. If you discover that your batch can process 500 reports I'd go with it. You have not shown us any of the logic or code you are using to deterring what to print when. Are you creating any objects or resources that you are not releasing? Also what does the Print spool have to do with creating a snapshot file? Am I missing something there? -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... Ron, Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#5
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Ron,
Let's say there are 1000 entities, and for each entity, I generate 4 reports. During the report generation/export process, I have a list of the entities that I compile, and then enumerate thru that list to generate the report "set" for each entity, as a series of snapshot files. Each snapshot has a file name prefix associated with the entity to make it easy to identify. Regarding your first comment about number of reports: On occasion, I can get all the way thru the entire process - no problem. Other times, this thing blows up during the export for the 20th entity, and sometimes for the 669th entity. Completely unpredictable. So, I know it can get thru it all. My problem is the inability to trigger the unhandled exception predictably, which would enable me to debug it. I have inserted procedures in every event of every part of the reports, and when an event triggers, I write an entry into a tracking table. This has been only mildly helpful, as I cannot get the error to repeat itself in the same place. Regarding your second comment about object creation: Yes - I create all kinds of objects, recordsets, etc. I am pretty familiar with the object model in VBA and Access, and use it extensively to build these reports. I understand that I should "release" all objects once I am done with them in every procedure (Set Object= Nothing). Recordsets are closed and then Set RecordSet= Nothing. I have build a small procedure that does just that, and that I call globally to close any recordset I open programatically. Regarding print spooler: To generate a snapshot file, I must still use a printer attached to the report. If I use a networked printer, then Windows uses the print spooler. So, to work around this, I have a printer driver installed on the computer, and attached to LPT1. By doing this, I don't use Print Spooler or any network related services (again, trying to eliminate the network as the cause of this problem). Even if I use Print Preview, I have to have a printer associated with the report just to preview it. All my reports use the "Default Printer" rather than a specific one, and this works fairly well. I can set the default printer external to my application, without changing any code in the Access DB. Ron Weiner wrote: If it were me I rework the logic to reduce the number to something really low like 20. If that worked I would try 40, and so on. If you discover that your batch can process 500 reports I'd go with it. You have not shown us any of the logic or code you are using to deterring what to print when. Are you creating any objects or resources that you are not releasing? Also what does the Print spool have to do with creating a snapshot file? Am I missing something there? -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... Ron, Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#6
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Hi Peter,
if any of your reports have images, here is a link that may help you by Stephan Leban: http://www.lebans.com/printfailures.htm Stephan also says, "A couple of observations regarding this issue: "If you are using Image controls, set the Picture property in the Print event NOT the Format event. Alos ensure you have turned off the Loading Image dialog via the Registry. "Do not use Lines or Borders that have a dot or dash style setting. In many cases, each dot or dash gets converted to an individual BltBit call." Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Peter B wrote: I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#7
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Hi Peter,
you wrote, "3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem." I consulted another MVP, Tom Wickerath, regarding your troubles and this is what he said: "Perhaps Peter has a defective component in his PC (memory, NIC card, etc.)." Tom goes on to talk about a previous experience someone else had when their "Windows 2000 based PC started experiencing frequent BSOD (Blue screen of death) episodes, about once every 20 ~ 30 minutes. Each time required a Ctrl - ALT - Del to restart." Tom tracked the problem down to a defective floppy diskette drive, "With the case open, you could see the floppy drive spinning very inconsistently, ie. fast --- slow and struggling --- fast --- slow and struggling, etc. I purchased a new floppy drive, installed it, and no more BSOD!" Peter: 1. can the problem can be replicated on a different PC without involving a network? 2. what brings you to the conclusion that your database is corrupting frequently? Is the FE or BE trashed after a failure? 3. Have you established relationships between the tables in his database? again, quoting Tom... "When one establishes a relationship, the foreign key field is automatically indexed, although it will not be displayed in the Indexes window in table design view. Indexes help prevent table scans (ie. reading all records in a table to solve a query). Given that the problem involves a report with four subreports, I wonder if the possible lack of indexes on the link child field(s) might be taxing his system a bit too much. 4. What kind of free space do you have on your hard drive? As an absolute minimum, you should have at least 10% and more is better. 5. Does your environment variable point to a valid Temp. folder on your system? Have you emptied out the temp folder recently? 6. Have you run chkdsk /F and defragged your hard drive recently? "He has a very I/O intensive process to be reading all that data from the hard drive. It would be nice to know that his hard drive is not overheating due to an excessively fragmented drive. Can he monitor the system temperature? And, are all the dust bunnies cleaned out from this PC, including vents?" I will add something to the importance of cooling fans... I lost 2 hard drives within a short period of time and my son suggested that the temperatures were getting too hot. We got a case with a bunch of fans in it and I haven't had any hard drive failures since! We did have some pretty powerful storms lately that had an effect on my computer. For a couple days, my computer was crashing right and left. I did a bit of housekeeping, ran some utilities, and also ran Scandisk with complete surface scan (took about 2o hours) and so far, no more problems! As for cleaning: dust interferes with electrical connections (as well as providing insulation to trap heat) so it is important to keep your system as dust-free as possible. We have a kit for your vacuum cleaner with small attachments that we use to vacuum PC's. We also use canned air (don't breathe it and keep it out of reach of children). To clean the fans, alcohol on a Q-tip works good. another thing to try is decompiling the database (back it up first): make an icon with this as its target: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\path\filename.mdb" /decompile if your Access program is not located in the directory specified, make the appropriate substitution after you decompile, compile it if you have any code and then do compact/repair If you STILL have problems, please post the code you are using -- another set of eyes might see something that you have missed. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Peter B wrote: Ron, Let's say there are 1000 entities, and for each entity, I generate 4 reports. During the report generation/export process, I have a list of the entities that I compile, and then enumerate thru that list to generate the report "set" for each entity, as a series of snapshot files. Each snapshot has a file name prefix associated with the entity to make it easy to identify. Regarding your first comment about number of reports: On occasion, I can get all the way thru the entire process - no problem. Other times, this thing blows up during the export for the 20th entity, and sometimes for the 669th entity. Completely unpredictable. So, I know it can get thru it all. My problem is the inability to trigger the unhandled exception predictably, which would enable me to debug it. I have inserted procedures in every event of every part of the reports, and when an event triggers, I write an entry into a tracking table. This has been only mildly helpful, as I cannot get the error to repeat itself in the same place. Regarding your second comment about object creation: Yes - I create all kinds of objects, recordsets, etc. I am pretty familiar with the object model in VBA and Access, and use it extensively to build these reports. I understand that I should "release" all objects once I am done with them in every procedure (Set Object= Nothing). Recordsets are closed and then Set RecordSet= Nothing. I have build a small procedure that does just that, and that I call globally to close any recordset I open programatically. Regarding print spooler: To generate a snapshot file, I must still use a printer attached to the report. If I use a networked printer, then Windows uses the print spooler. So, to work around this, I have a printer driver installed on the computer, and attached to LPT1. By doing this, I don't use Print Spooler or any network related services (again, trying to eliminate the network as the cause of this problem). Even if I use Print Preview, I have to have a printer associated with the report just to preview it. All my reports use the "Default Printer" rather than a specific one, and this works fairly well. I can set the default printer external to my application, without changing any code in the Access DB. Ron Weiner wrote: If it were me I rework the logic to reduce the number to something really low like 20. If that worked I would try 40, and so on. If you discover that your batch can process 500 reports I'd go with it. You have not shown us any of the logic or code you are using to deterring what to print when. Are you creating any objects or resources that you are not releasing? Also what does the Print spool have to do with creating a snapshot file? Am I missing something there? -- Ron W www.WorksRite.com "Peter B" wrote in message groups.com... Ron, Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message legroups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#8
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
ps
I want to add another point regarding indexes... how long is the field you are using to relate your tables? If you are using Autonumber/Long Integers, then that is efficient as Long Integers take 4 bytes to store. Recently, I troubleshooted a query someone was having problems with. It has worked fine when they built is but now that the database had lots of records, the query was failing -- the problem turned out to be caused by linking using a long text field. The link was changed to a short field and the query ran lickity split with no problems. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * strive4peace wrote: Hi Peter, you wrote, "3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem." I consulted another MVP, Tom Wickerath, regarding your troubles and this is what he said: "Perhaps Peter has a defective component in his PC (memory, NIC card, etc.)." Tom goes on to talk about a previous experience someone else had when their "Windows 2000 based PC started experiencing frequent BSOD (Blue screen of death) episodes, about once every 20 ~ 30 minutes. Each time required a Ctrl - ALT - Del to restart." Tom tracked the problem down to a defective floppy diskette drive, "With the case open, you could see the floppy drive spinning very inconsistently, ie. fast --- slow and struggling --- fast --- slow and struggling, etc. I purchased a new floppy drive, installed it, and no more BSOD!" Peter: 1. can the problem can be replicated on a different PC without involving a network? 2. what brings you to the conclusion that your database is corrupting frequently? Is the FE or BE trashed after a failure? 3. Have you established relationships between the tables in his database? again, quoting Tom... "When one establishes a relationship, the foreign key field is automatically indexed, although it will not be displayed in the Indexes window in table design view. Indexes help prevent table scans (ie. reading all records in a table to solve a query). Given that the problem involves a report with four subreports, I wonder if the possible lack of indexes on the link child field(s) might be taxing his system a bit too much. 4. What kind of free space do you have on your hard drive? As an absolute minimum, you should have at least 10% and more is better. 5. Does your environment variable point to a valid Temp. folder on your system? Have you emptied out the temp folder recently? 6. Have you run chkdsk /F and defragged your hard drive recently? "He has a very I/O intensive process to be reading all that data from the hard drive. It would be nice to know that his hard drive is not overheating due to an excessively fragmented drive. Can he monitor the system temperature? And, are all the dust bunnies cleaned out from this PC, including vents?" I will add something to the importance of cooling fans... I lost 2 hard drives within a short period of time and my son suggested that the temperatures were getting too hot. We got a case with a bunch of fans in it and I haven't had any hard drive failures since! We did have some pretty powerful storms lately that had an effect on my computer. For a couple days, my computer was crashing right and left. I did a bit of housekeeping, ran some utilities, and also ran Scandisk with complete surface scan (took about 2o hours) and so far, no more problems! As for cleaning: dust interferes with electrical connections (as well as providing insulation to trap heat) so it is important to keep your system as dust-free as possible. We have a kit for your vacuum cleaner with small attachments that we use to vacuum PC's. We also use canned air (don't breathe it and keep it out of reach of children). To clean the fans, alcohol on a Q-tip works good. another thing to try is decompiling the database (back it up first): make an icon with this as its target: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\path\filename.mdb" /decompile if your Access program is not located in the directory specified, make the appropriate substitution after you decompile, compile it if you have any code and then do compact/repair If you STILL have problems, please post the code you are using -- another set of eyes might see something that you have missed. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Peter B wrote: Ron, Let's say there are 1000 entities, and for each entity, I generate 4 reports. During the report generation/export process, I have a list of the entities that I compile, and then enumerate thru that list to generate the report "set" for each entity, as a series of snapshot files. Each snapshot has a file name prefix associated with the entity to make it easy to identify. Regarding your first comment about number of reports: On occasion, I can get all the way thru the entire process - no problem. Other times, this thing blows up during the export for the 20th entity, and sometimes for the 669th entity. Completely unpredictable. So, I know it can get thru it all. My problem is the inability to trigger the unhandled exception predictably, which would enable me to debug it. I have inserted procedures in every event of every part of the reports, and when an event triggers, I write an entry into a tracking table. This has been only mildly helpful, as I cannot get the error to repeat itself in the same place. Regarding your second comment about object creation: Yes - I create all kinds of objects, recordsets, etc. I am pretty familiar with the object model in VBA and Access, and use it extensively to build these reports. I understand that I should "release" all objects once I am done with them in every procedure (Set Object= Nothing). Recordsets are closed and then Set RecordSet= Nothing. I have build a small procedure that does just that, and that I call globally to close any recordset I open programatically. Regarding print spooler: To generate a snapshot file, I must still use a printer attached to the report. If I use a networked printer, then Windows uses the print spooler. So, to work around this, I have a printer driver installed on the computer, and attached to LPT1. By doing this, I don't use Print Spooler or any network related services (again, trying to eliminate the network as the cause of this problem). Even if I use Print Preview, I have to have a printer associated with the report just to preview it. All my reports use the "Default Printer" rather than a specific one, and this works fairly well. I can set the default printer external to my application, without changing any code in the Access DB. Ron Weiner wrote: If it were me I rework the logic to reduce the number to something really low like 20. If that worked I would try 40, and so on. If you discover that your batch can process 500 reports I'd go with it. You have not shown us any of the logic or code you are using to deterring what to print when. Are you creating any objects or resources that you are not releasing? Also what does the Print spool have to do with creating a snapshot file? Am I missing something there? -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... Ron, Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message oups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#9
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Hi Crystal,
Some quick comments... We did have some pretty powerful storms lately that had an effect on my computer. A good UPS (Uninterruptable Power Supply) that protects against voltage spikes and sags (brownouts) can be very helpful in preventing corruption to JET databases. I use a Smart-UPS manufactured by APC: http://www.apc.com There are other reputable manufacturers as well. You can get a decent UPS for a home office, for about $80 US (650 VA). I have a 1000 VA model, just to give a bit more time available, after a power failure. Any component that plugs into your PC should be protected by a spike protector, as a minimum. An example is a printer. You don't need to provide protection from loss of power, or temporary brownouts, for printers but you should definately provide spike protection. Again, I use an APC product for this as well. http://www.apc.com/products/family/index.cfm?id=21 and http://www.apc.com/products/category.cfm?id=12&subid=65 We have a kit for your vacuum cleaner with small attachments that we use to vacuum PC's. You have a kit for Peter's vacuum cleaner? G, D & R make an icon with this as its target: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\path\filename.mdb" /decompile You can make a generic shortcut that will work for the next database that you open, by not including the path to the .mdb file, ie.: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /decompile A generic shortcut will work as long as you have not implemented Access Security, assuming you are joined to the plain vanilla system.mdw file. Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "strive4peace" "strive4peace2006 at yaho" wrote: Hi Peter, you wrote, "3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem." I consulted another MVP, Tom Wickerath, regarding your troubles and this is what he said: "Perhaps Peter has a defective component in his PC (memory, NIC card, etc.)." Tom goes on to talk about a previous experience someone else had when their "Windows 2000 based PC started experiencing frequent BSOD (Blue screen of death) episodes, about once every 20 ~ 30 minutes. Each time required a Ctrl - ALT - Del to restart." Tom tracked the problem down to a defective floppy diskette drive, "With the case open, you could see the floppy drive spinning very inconsistently, ie. fast --- slow and struggling --- fast --- slow and struggling, etc. I purchased a new floppy drive, installed it, and no more BSOD!" Peter: 1. can the problem can be replicated on a different PC without involving a network? 2. what brings you to the conclusion that your database is corrupting frequently? Is the FE or BE trashed after a failure? 3. Have you established relationships between the tables in his database? again, quoting Tom... "When one establishes a relationship, the foreign key field is automatically indexed, although it will not be displayed in the Indexes window in table design view. Indexes help prevent table scans (ie. reading all records in a table to solve a query). Given that the problem involves a report with four subreports, I wonder if the possible lack of indexes on the link child field(s) might be taxing his system a bit too much. 4. What kind of free space do you have on your hard drive? As an absolute minimum, you should have at least 10% and more is better. 5. Does your environment variable point to a valid Temp. folder on your system? Have you emptied out the temp folder recently? 6. Have you run chkdsk /F and defragged your hard drive recently? "He has a very I/O intensive process to be reading all that data from the hard drive. It would be nice to know that his hard drive is not overheating due to an excessively fragmented drive. Can he monitor the system temperature? And, are all the dust bunnies cleaned out from this PC, including vents?" I will add something to the importance of cooling fans... I lost 2 hard drives within a short period of time and my son suggested that the temperatures were getting too hot. We got a case with a bunch of fans in it and I haven't had any hard drive failures since! We did have some pretty powerful storms lately that had an effect on my computer. For a couple days, my computer was crashing right and left. I did a bit of housekeeping, ran some utilities, and also ran Scandisk with complete surface scan (took about 2o hours) and so far, no more problems! As for cleaning: dust interferes with electrical connections (as well as providing insulation to trap heat) so it is important to keep your system as dust-free as possible. We have a kit for your vacuum cleaner with small attachments that we use to vacuum PC's. We also use canned air (don't breathe it and keep it out of reach of children). To clean the fans, alcohol on a Q-tip works good. another thing to try is decompiling the database (back it up first): make an icon with this as its target: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\path\filename.mdb" /decompile if your Access program is not located in the directory specified, make the appropriate substitution after you decompile, compile it if you have any code and then do compact/repair If you STILL have problems, please post the code you are using -- another set of eyes might see something that you have missed. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day remote programming and training strive4peace2006 at yahoo.com * Peter B wrote: Ron, Let's say there are 1000 entities, and for each entity, I generate 4 reports. During the report generation/export process, I have a list of the entities that I compile, and then enumerate thru that list to generate the report "set" for each entity, as a series of snapshot files. Each snapshot has a file name prefix associated with the entity to make it easy to identify. Regarding your first comment about number of reports: On occasion, I can get all the way thru the entire process - no problem. Other times, this thing blows up during the export for the 20th entity, and sometimes for the 669th entity. Completely unpredictable. So, I know it can get thru it all. My problem is the inability to trigger the unhandled exception predictably, which would enable me to debug it. I have inserted procedures in every event of every part of the reports, and when an event triggers, I write an entry into a tracking table. This has been only mildly helpful, as I cannot get the error to repeat itself in the same place. Regarding your second comment about object creation: Yes - I create all kinds of objects, recordsets, etc. I am pretty familiar with the object model in VBA and Access, and use it extensively to build these reports. I understand that I should "release" all objects once I am done with them in every procedure (Set Object= Nothing). Recordsets are closed and then Set RecordSet= Nothing. I have build a small procedure that does just that, and that I call globally to close any recordset I open programatically. Regarding print spooler: To generate a snapshot file, I must still use a printer attached to the report. If I use a networked printer, then Windows uses the print spooler. So, to work around this, I have a printer driver installed on the computer, and attached to LPT1. By doing this, I don't use Print Spooler or any network related services (again, trying to eliminate the network as the cause of this problem). Even if I use Print Preview, I have to have a printer associated with the report just to preview it. All my reports use the "Default Printer" rather than a specific one, and this works fairly well. I can set the default printer external to my application, without changing any code in the Access DB. Ron Weiner wrote: If it were me I rework the logic to reduce the number to something really low like 20. If that worked I would try 40, and so on. If you discover that your batch can process 500 reports I'd go with it. You have not shown us any of the logic or code you are using to deterring what to print when. Are you creating any objects or resources that you are not releasing? Also what does the Print spool have to do with creating a snapshot file? Am I missing something there? -- Ron W www.WorksRite.com "Peter B" wrote in message groups.com... Ron, Good suggestions. For generating the reports, I have selected a printer assigned directly to the LPT1 port, to avoid using the spoolsv.exe (there was an issue at one time with this). But, so you know, since I had isolated the problem to one of the reports, I reworked the code to only export the problematic report (reducing the number of snapshot files exported to around 1300). Even under this situation, I get the error. Also, I was monitoring memory usage with the TaskManager, and didn't see MSACCESS.EXE ballooning, or anything else out of the ordinary. Ron Weiner wrote: My initial impression is 3000 Snapshots is a LOT! I am suspecting some kind of a Memory leak somewhere either in the code you wrote, or in Access. First thing I'd try, would be to break the single batch down into two, three or more smaller ones, that are scheduled to run about an hour apart. I'd hope that if it was a memory problem, when access quits each batch all of the memory that was allocated during that run would be freed allowing the next batch to have a "Clean slate". Just my $.02 -- Ron W www.WorksRite.com "Peter B" wrote in message legroups.com... I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
#10
|
|||
|
|||
Unhandled exceptions during report outputs to snapshot files
Peter,
If there is anyway that you can send me a copy of your database, complete with data so that I can test it as you are, then I'll see if I can contribute to helping you find a resolution. I will run an analysis of your database using Total Access Analyzer as a start: http://www.fmsinc.com/products/analyzer/index.html If you are interested, send me a private e-mail message with a valid reply-to address. Compact the database first, and then zip it. My e-mail address is available at the bottom of the contributor's page indicated below. Please do not post your e-mail address (or mine) to a newsgroup reply. Doing so will only attract the unwanted attention of spammers. Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Peter B" wrote: I have been trying to diagnose this problem for weeks now. I have a FE/BE database system for which I want to automatically run reports each night. The reports are written to snapshot files. There is a series of reports that I generate, and in total, over 3000 snapshots are generated. I have created a small VB script that opens MS Access and runs a macro that runs code in VBA to gen the reports. I have created a Scheduled Task to run the VB script every nite. My problem is that Access ends with an unhandled exception, usually in ntdll.dll, before all the reports are generated. This is not repeatable. It happens on different reports every time. When I go into my database to preview the report corresponding to the one that Access bombed on, it works correctly. The other day, I tried to manually launch the process about 6 times, and the abend occurred at 6 different places along the way. I have managed to isolate the problem to just one of the reports, which is a "combination" report that is composed of sub-reports. The best I have managed to figure out is that there is a problem with one of the subreports in the combination report. That is, if I disable the combination report, then my processes and exports work fine. Access does not offer me a debug opportunity or anything, making this difficult to trace. I have searched the Internet looking for a solution, and so far, have tried the following: 1) Completely rebuild the "combination" report from scratch 2) Did a decompile on the FE database, then a C&R, then a recompile, and a C&R. 3) Followed Allen Browne's suggestions for fixing corruption, including re-importing the entire FE database into a clean, new database, with the Name AutoCorrect disabled. 4) I have created a VB script process that copies the FE/BE to a local harddrive, and uses a printer connected to LPT1 to avoid the use of any network resources, in case this was in issue with traffic. 5) I have changed printer drivers So far, none of this has helped, and I cannot find any solution on the internet. I have several suspicions, but no way to check them out: 1) My "combination" report is too complicated, and this creates a problem; 2) I am suffering from "DLL Hell", and there is something about some service pack, update, etc. etc. etc. on my PC that is conflicting with Access; 3) For whatever reason, my database is VERY sensitive to corruption, which may be the root cause of this problem. I do not have a lot of confidence that sending the error reports to MS will result in some quick turnaround solution from them, and I have no way to decipher the error report to get to the root of the problem. I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database itself is an Access 2000 format. Does anyone have any thoughts on what might be going on? |
Thread Tools | |
Display Modes | |
|
|