2

The Problem

I'm using a scheduled task to open an MS Access database, run a macro to email a report in PDF format, and then close the database. The report is relatively basic and the macro simply uses the EmailDatabaseObject action to export & email the report in PDF format.

This has been running successfully on a daily basis for the past 4 years on a Windows 7 machine running the latest version of Office 365 compatible with Windows 7.

I've recently updated the machine to Windows 10 and I'm now encountering a No Current Record error when running the scheduled task. After clicking OK to the error several times, Windows reports that MS Access has stopped working and the application crashes.

If I manually open the database and manually run the macro, everything is successful.

Setup

  • Windows 10 Pro 20H2 (19042.964) 64-bit
  • Office 365 Apps for Business 2104 (13929.20296) 32-bit

My Research

  • I've found this MS KB article describing the issue, however, this reportedly applies to MS Access 2003 & 2007. My report has no grouping, but does contain summations in the report footer. However the suggested solution is not feasible since removing the summations would remove necessary information from the report.

  • I've found this related question, however, this attributes the cause to accessing the database from OneDrive as opposed to a mapped network drive, whereas I am accessing the database from a mapped network drive.

  • This looks to be the same issue, but I cannot access the 'solution' at Experts Exchange...

What I've Already Tried

  • Rolling back Office 365 to an earlier version (version 2101).

  • Running the Scheduled Task with Highest Privileges - but this results in no access to mapped network drives.

  • Running the macro from a batch file.

  • Opening the database from another PC and running the macro from a batch file.

  • Perform the operations using the AutoExec macro rather than invoking a specific macro by name.

  • Imported all objects from the database into a new local database.

  • Exporting the report as a PDF locally using the ExportWithFormatting action.

  • Using the DoCmd.SendObject method to email the report - this results in MS Access immediately crashing.

  • Changing the default Windows printer & default report printer to 'Microsoft Print to PDF' to rule out possible printer driver issues.

Current Workaround

I've found that this error only arises when the export format is set to PDF; as such, my current workaround is to export the report to RTF format - but this is obviously not ideal.

Update

I've now systematically removed items from the report until I was able to run the report successfully from the scheduled task.

I removed items until I was left with a single static label in the Report Header and a set of fields in the Report Detail referencing basic numeric & text data from a single table in the database.

Removing the Report Header & Footer resulted in the report running successfully from the scheduled task, and so I determined that the issue must be caused by a setting associated with the Report Header/Footer.

I isolated the issue to the 'Force New Page' setting in the Report Header:

enter image description here

With this set to 'After Section', the report crashes; but if this is set to 'None', the report is successful.

However, when setting this to 'None' in the original report, the report still crashes when run from the Task Scheduler and output to PDF.

Steps to Reproduce the Problem

I've uploaded an incredibly basic database here, containing a single table, a single report, and a single macro which executes the ExportWithFormatting action followed by the QuitAccess action.

To reproduce the problem, open a command prompt window and issue the following commands:

cd C:\Program Files (x86)\Microsoft Office\root\Office16
msaccess.exe C:\YourPath\test.accdb /x macro1

MS Access should then open and you may be presented with the 'No Current Record' error.

Has anyone else encountered this issue and knows of a solution?

Many thanks for your time.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Do you have a local install of Access under Windows10 that you can test with as opposed to Office365? Decompile and recompile the Access file? – June7 May 07 '21 at 23:47
  • Unfortunately I only have access to Office 365, though, this process was operating successfully under Office 365 on Windows 7, and indeed, the macro operates successfully when run manually - it's something related to when the macro is initiated via an automated means. – Lee Mac May 08 '21 at 15:23
  • Might post to a forum where you could attach file for analysis. – June7 May 08 '21 at 21:36
  • Not sure if you tried that: running the DB from a **local drive** via batch or task, not from a mapped network drive? – Andre May 10 '21 at 16:41
  • Many thanks for the suggestion @Andre, but I've tried that as part of bullet point (6) in my list above. – Lee Mac May 10 '21 at 18:07
  • *Exporting the report as a PDF locally using the ExportWithFormatting action.* -- Did this work? This works for me from a Batch file with Office 365 2104. -- I can't test `EmailDatabaseObject` because of Outlook security settings (I must manually confirm the send). – Andre May 11 '21 at 06:37
  • Also, have you tried with a very simple report? A local table, a tabular report, nothing else (no query, no functions or VBA or anything). – Andre May 11 '21 at 06:53
  • @Andre Sincerest apologies - work has gotten in the way recently and consequently I haven't had a chance to revisit this following your comments - I plan to run some tests over the weekend to see if I can pin down the source of the issue. I really appreciate your time and suggestions. – Lee Mac May 15 '21 at 00:18
  • @Andre I have updated my question with additional information - I have managed to narrow the problem down to the 'Force New Page' setting in the Report Header and can reproduce this error consistently even with the most basic of reports. Are you able to reproduce this behaviour? – Lee Mac May 15 '21 at 12:44
  • No - with the report header `Force New Page = After Section` I can still run `ExportWithFormatting` to PDF from a batch without problems. Can you create a sample database showing the problem for you, and upload to a file hoster? – Andre May 15 '21 at 13:41
  • Thanks for testing @Andre - I have now updated my question and have provided a sample database for testing. – Lee Mac May 15 '21 at 23:36
  • Sorry, I still can't reproduce it. -- My Windows version is slightly different: 20H2 (19042.906), the Office version and build is identical. – Andre May 16 '21 at 00:22
  • @Andre Thank you for your time testing this - I'm able to reproduce the issue on two different PCs, with the second PC running 20H2 (19042.928) with 2104 (13929.20372) 32-bit office. I realise that the issue becomes incredibly difficult to diagnose if not consistently reproducible, but thank you for your time & attention on this. – Lee Mac May 16 '21 at 12:24
  • [This](https://www.experts-exchange.com/questions/26489891/No-Current-Record-error-Exporting-report-from-macro-called-from-batch-file.html) looks to be the same issue, but I cannot access Experts Exchange... – Lee Mac May 16 '21 at 15:46
  • One additional suggestion: to rule out the printer driver as problem source, try setting "Microsoft Print to Pdf" as default printer on the PC. It's rather far fetched, but easy to try. – Andre May 16 '21 at 21:44
  • Regarding Experts Exchange, I'm not there either, but AFAIK Gustav is. You could ping him on an answer of his. Or do a free trial. – Andre May 16 '21 at 21:47
  • @Andre Good suggestion regarding the printer; however, unfortunately neither changing the default Windows printer nor changing the default report printer to "Microsoft Print to PDF" solved the problem - thank you for the suggestion however, I appreciate it. – Lee Mac May 16 '21 at 21:49
  • @Andre re Experts Exchange, I considered the free trial, but it requires supplying credit card details, which I wasn't keen on - I'll see if Gustav can kindly access the solution. – Lee Mac May 16 '21 at 21:51

0 Answers0