extremely slow when exporting PDF

[Stm]

Hi all,

I have an Access program that export report to PDF for distribution using the below command and the job should finish within 10 mins.

DoCmd.OutputTo acOutputReport, "rpt_xxx_Report", acFormatPDF

I've setup this as batch job in another machine and will auto run every day.
Two months ago, this program started to run extremely slow, I see every PDF generation is around 3 mins, and my program take 3 hours to run.

As the batch job machine usually is locked, when I login to that machine and see what happened, I see the PDF now generate at normal speed and the job completed as usual.

I've tried to search this problem and seems it is a bug in recent update of MSAccess.
As I've no control on the version update, may I know if there is any workaround to resolve this problem? The only thing I can do now is to login that batch machine every day and let it run faster.

Thanks!

 

[The_Doc_Man]

If you have machines in your office that work OK and yours doesn't, it is time to determine the differences.

For starters, which version of Access are you using on your balky machine which version is running on the machine that does it faster?

HINT: Just open Access directly from each machine, no need to actually have a .ACCDB or .MDB open. On the opening page that offers to create a blank DB, look at the left-hand vertical ribbon and find the "Accounts" link. Click that. You'll see a couple of big "buttons", one of which is labeled "About Access" - so click that. The box that pops up will show you the product name, marketing series, a version number in parentheses, and either 32-bit or 64-bit... all of that on the same line.

Compare what you see on your system to what you see on a machine that seems to be working OK.

Again, on the two machines, if you open the same app on both machines and see (or get to) the ribbon, then on each machine, from the ribbon click Database Tools >> Visual Basic >> Tools >> References and compare the checked items on both machines. Unchecked items DO NOT MATTER. You are not only interested in the name of each checked library module, but any number that looks like a version number. For instance, if you have Office 2021, you would see version 16.0 on many of the checked files. You are interested in knowing what is checked on both the working and non-working machine, AND the order in which they appear.

If you are able to do admin actions on your system, again log on your machine and on another machine, click the Windows START button (lower-left corner), then Settings >> Apps >> Installed Apps. DO NOT TRY TO MAKE CHANGES... but find out whether either machine is running a copy of Adobe Acrobat or another product. Compare version numbers on that software.

Remember, in each case you are looking for differences.

 

[arnelgp]

Remember, in each case you are looking for differences.

 

[GPGeorge]

Hi all,

I have an Access program that export report to PDF for distribution using the below command and the job should finish within 10 mins.

DoCmd.OutputTo acOutputReport, "rpt_xxx_Report", acFormatPDF

I've setup this as batch job in another machine and will auto run every day.
Two months ago, this program started to run extremely slow, I see every PDF generation is around 3 mins, and my program take 3 hours to run.

As the batch job machine usually is locked, when I login to that machine and see what happened, I see the PDF now generate at normal speed and the job completed as usual.

I've tried to search this problem and seems it is a bug in recent update of MSAccess.
As I've no control on the version update, may I know if there is any workaround to resolve this problem? The only thing I can do now is to login that batch machine every day and let it run faster.

Thanks!

In addition to the important steps Doc Man outlined, compare the printers defined for each computer. Given that the process involves creating PDFs, which I assume come from reports, the default printer for each computer is an obvious suspect.

 

[Pat Hartman]

I'm sure there is more code than the single line you posted if you are outputting multiple .pdfs with one button click. Please post the entire procedure so we can review the loop that is running.

 

[spaLOGICng]

In addition to all other recommendations, here are a couple other things to check that may be affecting performance.

1. Indexes: I noticed that nobody mentioned indexes (through searching; I may have missed something). If you are applying filters, parameters, criteria, etc., to you report when running, assure that the respective columns are properly indexed.

2. Data Source: if your Data Source is SQL Server; you may want to create a Job to reorganize indexes on occasion to defragment them.

3. Record Source: I am assuming that the record source to the Report is a Query. Even though this has been disputed, set the Query Recordset Type to Snapshot. Reports should automatically set it to snapshot, but I have experienced benefit of doing so.

4. Criteria: Make sure your criteria is placed on the correct columns. In an multi-table query, placing the filter on the bottom most column is better. For instance, you may have an Order ID in the Parent Table and in a Child Table. Place the Criteria on the Child Table.

5. Name AutoCorrect Options: This feature is turned on by default when creating a new Access Database and is an often overlooked feature that puts a drag on performance in a production environment. Why? Because it is tracking everything you do even while running the application. To disable it, go to File > Options > Current Database.... and scroll down midway and you will see it.

Also, while you are there, disable "Enable Layout View" and "Enable Design Changes for Tables in Datasheet View" for the Production Application.

Let us know if any of this helps.

 

[isladogs]

When you check for version / build info, use the values next to the About Access button

Do NOT use the info seen when you click on the button as that is often different and refers to the mso.dll library rather than Access itself. The only info that you want from there is the Office bitness (32/64-bit)

 

[The_Doc_Man]

In addition to all other recommendations, here are a couple other things to check that may be affecting performance.

1. Indexes: I noticed that nobody mentioned indexes (through searching; I may have missed something). If you are applying filters, parameters, criteria, etc., to you report when running, assure that the respective columns are properly indexed.

2. Data Source: if your Data Source is SQL Server; you may want to create a Job to reorganize indexes on occasion to defragment them.

3. Record Source: I am assuming that the record source to the Report is a Query. Even though this has been disputed, set the Query Recordset Type to Snapshot. Reports should automatically set it to snapshot, but I have experienced benefit of doing so.

4. Criteria: Make sure your criteria is placed on the correct columns. In an multi-table query, placing the filter on the bottom most column is better. For instance, you may have an Order ID in the Parent Table and in a Child Table. Place the Criteria on the Child Table.

5. Name AutoCorrect Options: This feature is turned on by default when creating a new Access Database and is an often overlooked feature that puts a drag on performance in a production environment. Why? Because it is tracking everything you do even while running the application. To disable it, go to File > Options > Current Database.... and scroll down midway and you will see it.

Also, while you are there, disable "Enable Layout View" and "Enable Design Changes for Tables in Datasheet View" for the Production Application.

Let us know if any of this helps.

While I agree that those things are important, please note two factors: 1. Stm states that this started at a specific time. 2. Stm states that other machines in the same ship DON'T show this behavior.

Your suggestions make sense when someone is fiddling around with the FE or the BE and it affects everyone. When it only affects one machine, your #1, #2, #3, and #4 suggestions are far less likely. #5 IS something that might affect a single machine. The two "Enable" options you mentioned could also be appropriate for single-machine changes.

 

[spaLOGICng]

While I agree that those things are important, please note two factors: 1. Stm states that this started at a specific time. 2. Stm states that other machines in the same ship DON'T show this behavior.

Your suggestions make sense when someone is fiddling around with the FE or the BE and it affects everyone. When it only affects one machine, your #1, #2, #3, and #4 suggestions are far less likely. #5 IS something that might affect a single machine. The two "Enable" options you mentioned could also be appropriate for single-machine changes.

Hi Doc,

The Bug with the Update that the OP mentioned, which I am familiar with, did cause some of the issues that I mention, at least with some of the applications that a few clients have presented to me for resolution. It also affected some ODBC connections. This did not happen with all of my clients and some issues were different depending on where the data was being hosted, i.e., in the current Access Database, in an Access Backend Database, or on SQL Server, etc.

The Recent Access Fixes is not a concise list. It is only a list of reported issues. Resolving the issues also resolved unreported secondary or collateral issues. So, whether we feel #'s 1-4 are relevant, it is always worth the attempt review.

#5 is not machine specific. The two "enable" features that I mention are database specific, not client/machine specific. If you publish an access application for a production environment, those settings go with it and affects everyone that uses the application. The dead giveaway for the two enable features are their existence on the "Current Database" Panel of the Option Screen. These options are always worth reviewing before publishing a production application.

The five settings that I mention affect performance of an application, even in run mode. They are specific to the application and affect all people that use the application. The effect of these settings is not as noticeable in smaller applications. The larger the application is, the more noticeable they will become.

The only reason I responded to this post is because the OP has yet to respond favorably to any of the suggestions previously posted that proved helpful. So, whether my suggestions help or not, all the areas that I mentioned are certainly and always worth looking at. At last resort, perform an "Online Repair" to catch any corrective Updates, which did seem to correct for some but not all of my clients.


PS: I am like a Perry Mason Guy. I do not dwell in the educated 96% category; my niche is in the 4% very rare uneducated guess gut feeling trial and error category. I have found much more success in being lucky than being good. I am that guy everyone scratches their head asking themselves "How does he do it?!"