DoCmd.SendObject acReport intermittant database crashing

[Indigo]

Hello!

I need some direction / advice. I inherited an Access 2003 database from someone who left my company many years ago. Three years ago we moved from Office 2003 to Office 2010 and have been slowly moving our databases from MDB to ACCDB.

This is one of the last databases that I have been working on. It was originally built as Access 2003 and I have saved it in Access 2010.

Originally, users would send out a safety report as a SNP attachment as Access 2003 did not support PDF. When I saved the database as ACCDB, I changed the lines of code from:

Sub EmailIncidentReport()
    Dim HoldDept As String
    Dim HoldGroup As String
 
        HoldDept = Forms![HoldingInfo].txtDept.Value
        HoldGroup = Forms![HoldingInfo].txtHoldArea.Value
 
            DoCmd.SendObject acSendReport, "IncidentInvestigationEmailrpt", acFormatSNP, "Accident Investigation", , , "" & HoldDept & " " & HoldGroup & " Incident Investigation", "Please review the attached Incident Investigation Report." 
End Sub

to:

Sub EmailIncidentReport()
    Dim HoldDept As String
    Dim HoldGroup As String
 
        HoldDept = Forms![HoldingInfo].txtDept.Value
        HoldGroup = Forms![HoldingInfo].txtHoldArea.Value
 
         Const acFormatPDF = "PDF Format (*.pdf)"
            DoCmd.SendObject acSendReport, "IncidentInvestigationEmailrpt", acFormatPDF, "Accident Investigation", , , "" & HoldDept & " " & HoldGroup & " Incident Investigation", "Please review the attached Incident Investigation Report."             
End Sub

As Access 2010 does not support SNP and my company wanted the report to be sent as PDF anyway.

However, here is my issue: Sometimes it works fine, no errors, no problem. And sometimes it crashes the database and does not sent the email.

Has anyone ever run into this? Is it because the database was originally built in Access 2003? Any advice? Suggestions? Thank you in advance!

 

[vbaInet]

When you say it crashes do you actually mean it crashes and doesn't report any errors? Or it just stops responding?

How much data is this report churning? And do you see any pattern in the way it crashes, i.e. what could cause it to crash?

 

[Indigo]

I get a pop-up message that states:

Microsoft Access has stopped working

Windows is collecting more information about the problem.
this might take several minutes....


I see no pattern in the way it crashes.... the email size when sent is 67KB

I can send the same report 4 times in a row and it doesn't crash and then it will crash on the 5 time. Or I can close / open the database and send once and one the second attempt it will crash.

 

[vbaInet]

So how much data is this report churning, and I mean in terms of number of records in the queries/tables linked to the Recordsource? And how many users are using this db?

 

[Indigo]

There are two of us in the database right now.... sometimes there is only one, sometimes 3 or 4.

The report is returning one record with 34 fields from one table and up to 4 records in a related table with 4 fields.

 

[vbaInet]

Is the database split into a Front End and Back End? And are you using one FE in a shared folder?

How big are the tables and queries that the report is pulling from?

Sorry, need to ask all these questions

 

[Indigo]

Yes, the database is split and yes there is one front end in a shared folder.

There are almost 5000 records in the main table and over 12,000 in the related table.

 

[vbaInet]

yes there is one front end in a shared folder.

This is your problem Indigo. The way Access works is you need to have one Back End but each user must have their own Front End installed on their machine, not in a shared folder. Do this and your problem will most likely go away.

 

[Indigo]

Unfortunately, I can't..... even though I said there is only 3 or 4 users in there at a time, I have over 200 users set up to access this database.

Should I rejoin it into one (i.e. no front end / back end split)?

I split it out originally when it was still an Access 2003 database because there were compatibility issues and it would crash many times a month. It was easier to split it and restore a front end when this happened than risk losing the back end data.

 

[vbaInet]

Did you post a question on any forum regarding your compatibility issues? From having multiple front ends to now having one shared front end is actually going backwards. Unfortunately, with your current setup the risk of losing data and corrupting the front end is even higher.

 

[Indigo]

Actually, for the last 3 years there has only ever been one front end and it never crashed when emailing the SNP attachment in Access 2003. I knew what the compatibility issue was, but didn't have the time to focus on making the changes to the database until recently.

 

[vbaInet]

There are always going to be very few instances where a not so ideal setup just keeps holding up, but just because it's held up for 3 years doesn't mean that it's working to its full capacity because Access is struggling to keep holding up. And I'm sure some of your users have experienced snags here and there.

Well, you obviously know how things should be setup and now know the root cause of your problem, so the ball is in your court.

If you want a workaround with your current setup then look into Outlook automation but then you'll probably have problems outputting the report to PDF using the OutputTo command so you will need to use a command line tool for that.