Splitting one large PDF report into separate PDF by Doctor name

[BP2E]

Hello,
I currently run a monthly report which details productivity by doctor. The Access report separates each doctor but prints as one 300+page PDF, which then is manually split into individual PDF's. How can this task be automated? I've scoured google looking for an answer but to no avail.
As you can tell I am quite a beginner when it comes to excel. Thanks!

[June7]

Review https://www.accessforums.net/import-...ges-25042.html

[ItsMe]

Review https://www.accessforums.net/import-...ges-25042.html

If you are going to create an ADODB recordset object and you are using newer versions of Access (I am guessing 2013 and newer), you will need to create a reference to the 'Microsoft ActiveX Data Objects X.X Library'. Perhaps it would be easier to use DAO.

[BP2E]

Thank you for the quick answer. When I copy and paste the link provided it doesn't work.
What parts of the code shown below would need to be modified: The query field name which dictates where to separate is "Doctor Name"
In the meantime I will do a basic VBA 101 course. ie what to do after clicking on database tools-- VB-- and removing the "Option Compare Database"
Thanks again.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "SELECT LabNum FROM Submit;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
DoCmd.OpenReport "LabReport", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "drive:\path\" & rs!LABNUM & ".pdf", True
DoCmd.Close acReport, "LabReport", acSaveNo
'kill Adobe Acrobat so I don't have thousands of open pdf documents
Dim objWMIService As Object, objProcList As Object, objProcess As Object
Set objWMIService = GetObject("winmgmts:")
Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
For Each objProcess In objProcList
objProcess.Terminate (0)
Next
rs.MoveNext

[June7]

Copy/paste what link? Apparently you have seen the sample code. But did you read the part about not really needing the "kill" code?

Do not remove "Option Compare Database".

Should add below that line "Option Compare".

Substitute where needed your actual names for report, table, field, and filepath.

[HiTechCoach]

Hello,
I currently run a monthly report which details productivity by doctor. The Access report separates each doctor but prints as one 300+page PDF, which then is manually split into individual PDF's. How can this task be automated? I've scoured google looking for an answer but to no avail.
As you can tell I am quite a beginner when it comes to excel. Thanks!

Ys it is possible to do. It will require a recordset that has a unique list of each Dr that needs a report.

I have created this working example that might help: Batch Printing and Save As PDF