HELP PLEASE Saving Access Reports into multiple PDF's

[Kimberly2015]

Hi!

I'm running into a Run time error 3014 Cannot Open Any More Tables

I have a report in Access and I am using the below VBA to save the file by the content of the "File" column in the DOC_AP_MASTER table. It works wonderfully for about 60-120 (it ranges and I don't know why) but then I get the lovely error above.

What in the world am I doing wrong? My report closes after each save... what tables am I opening without knowing? Thanks!
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\Testing\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT[File] FROM [DOC_AP_MASTER]", dbOpenSnapshot)
Do While Not rs.EOF
temp = rs("File")
'temp1 = rs3("Fac ID")
'temp2 = rs2("CORPORATION")
MyFileName = rs("File") & ".PDF"
DoCmd.OpenReport "Physician Summary", acViewReport, , "[File]= '" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Physician Summary"
DoEvents
rs.MoveNext
Loop
rs.Close
Set rs = Nothing


Set db = Nothing
End Sub

[ItsMe]

Not sure what the issue is but I would start by changing
temp = rs("File")
to
temp = rs![File]

and I would comment out DoEvents.



EDIT:
I would also change

MyFileName = rs("File") & ".PDF"
to
MyFileName = temp & ".PDF"

[Kimberly2015]

Thank you for the response. I just tried it an I received the same error -- Run time Error '3014' Cannot open any more tables.
Before I loops should I close all?
:/ I've been looking at the same code for 2 weeks... I've tried everything. I feel like it's an easy solution but I just don't know what it is...

[ItsMe]

As I mentioned, I am not sure what is wrong. However, what I suspect is that you are having issues because of the use of reserved words. Also, I made a mistake earlier when I suggest to change MyFileName = rs("File") & ".PDF"

Below you will find some code that addresses the MyFileName issue as well as a couple others. If this does not work, I would suggest renaming the fields in your table.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim strTemp As String
Dim strFileName As String

mypath = "C:\Testing\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [DOC_AP_MASTER].[File] FROM [DOC_AP_MASTER]", dbOpenSnapshot)

While Not rs.EOF
    strTemp = rs![File]
    'temp1 = rs3("Fac ID")
    'temp2 = rs2("CORPORATION")
    
    
    strFileName = rs![File]
    MyFileName = strFileName & ".PDF"
    
    DoCmd.OpenReport "Physician Summary", acViewReport, , "[File]= '" & strTemp & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
    DoCmd.Close acReport, "Physician Summary"
    'DoEvents
    rs.MoveNext

'Loop
Wend

rs.Close
Set rs = Nothing
Set db = Nothing

[Kimberly2015]

Thank you so much for responding. I am still receiving the error. Does Access have a limit on how many times you can open and close a report?

If you have any other suggestions please let me know. I even renamed my fields. No luck.

[ItsMe]

Then the issue would have to be the machine's memory. The DoEvents line can help in some instances. I am not convinced it will help here. Can you post the (last) code you tried here?

[Kimberly2015]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrFileName As String
Dim mypath As String
Dim temp As String

mypath = "C:\Antipsychotics Testing\PC"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT[ALTIDFILE] FROM [DOC_AP_MASTER]", dbOpenSnapshot)
While Not rs.EOF
temp = rs![ALTIDFILE]

StrFileName = temp & ".PDF"
DoCmd.OpenReport "Physician Summary", acViewReport, , "[ALTIDFILE]= '" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & StrFileName
DoCmd.Close acReport, "Physician Summary"
'DoEvents
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[ItsMe]

I am not seeing a way to be more efficient with memory. The only thing I can think of is to have the code pause. At least, this will help us to determine if it is a memory issue.

Add a procedure to your module. Paste all of the following code as a stand-alone private sub.

Private Sub pleaseWait(intDelay As Integer)

      Dim dblDelayTill As Double
      dblDelayTill = DateAdd("s", intDelay, Now)
      While DateDiff("s", Now, dblDelayTill) > 0: Wend

End Sub

Then you can call the sub from within your code. Here, I am using 2 seconds. I suggest using 2 seconds and then revisit how we can do better at a later time.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\Antipsychotics Testing\PC"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT[ALTIDFILE] FROM [DOC_AP_MASTER]", dbOpenSnapshot)
While Not rs.EOF
temp = rs![ALTIDFILE]
StrFileName = temp & ".PDF"
DoCmd.OpenReport "Physician Summary", acViewReport, , "[ALTIDFILE]= '" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & StrFileName
Call pleaseWait(2)
DoCmd.Close acReport, "Physician Summary"
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing

[ssanfu]

This is a long shot. A lot of times the error messages are very obtuse and the error is different that than the error message.

You open an unordered recordset, so the records can be in different places in the recordset. This would account for the differing number of records when it errors.
Is it possible that the field [ALTIDFILE] could have an empty string or NULL in one or more records?
Do you have code in the "On no data" event of the report?

[ItsMe]

Not so far fetched because no tables are actually being opened. So the error is ambiguous, at best. Although, I would expect something different because of the string declaration.

[NTC]

not exactly sure what the problem is... I see a couple things that I'm not sure about..

* is dbOpenSnapshot definition really needed? snapshot is deprecated in 2013 so it would seem problematic in the long run in any case...have never used it myself
* are you explicitly naming the file as .pdf - and then implementing an report output also to a pdf format - doesn't that automatically append the .pdf ? actually I'm not sure on that but it strikes me as you would have: filename.pdf.pdf ............again not 100% sure

what I am sure about is to debug one must segregate things down to its component parts - you need to temporarily deactivate the loop and set it up so a single record/report exports out to a pdf file okay....get that going and then work on the loop method.

[ItsMe]

@NTC
I agree that debugging requires isolation of statements in small code blocks. Actually, this should be a goal when building code. I have been working here on the basis that the loop is functioning for the first few iterations. Although there is, still, a chance there is an issue with the first iteration, it is not obvious that this is the case looking at the code and the user states they have traveled down that road.

Where did you hear about the deprecation of dbOpenSnapshot? I ask because I use it often. I looked in Access 2013 Inside Out and it is mentioned as a valid argument. I also booted up Access 2016 and was able to get a return of 4.

[Kimberly2015]

Hi,

It's still giving me the same error. I even tried with a 5 and 10 second delay.
Also, Before I import my table from Excel I make sure all blanks are filled so there are not blanks in the table.
Thank you all for your input. If you think of anything else please post. I'll still be working on it.

Thanks!

[Kimberly2015]

@ NTC

Yes, it successfully saves about 65 reports right now out of 475+ reports I am hoping it will eventually save, before it errors.

The code works without the loop.

Thanks!

[ItsMe]

What is the recordsource of your Report? Is it a Query Object? Post the name of the query and the query's SQL here.