Results 1 to 7 of 7
  1. #1
    jayintexas is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    2

    Multiple DoCmd.Outputto PDF crashes Access

    I have some simple code that outputs a report to pdf around 300 times, but it crashes after about 160 iterations. I am using Office 365 ProPlus version 1708 on a Windows 10 64 bit machine. The code is below:

    DoCmd.OpenReport reportname:="XXX", view:=acViewReport, wherecondition:=sqlstr, OpenArgs:="Sample Text"
    For x = 1 to MaxClients
    DoCmd.OutputTo objecttype:=acOutputReport, outputformat:=acFormatPDF, outputfile:="Report Name-" & format(x,"000")
    Next

    Any suggestions appreciated.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It would be nice if you could post the entire subprocedure so we could get a bit of the context; also, maybe show the report_open event to see how you use the openargs.

    I use a different method to create PDFs: I define a public variable (lets say Public lngClientID as long), then I set a recordset that contains all the ClientIDs I want to include and loop in there making lngClientID =rst("ClientID") and docmd.outputto acOutputReport,"rptMyReport",acFormatPDF..... The report uses as recordsource a query that has a custom function that limits the records to just the current ID in the loop:

    Code:
    Public Function vcSelectedCliendID() as Long
    vcSelectedCliendID=lngClientID   'the public variable I change inside the loop
    End Function
    Cheers,
    Vlad

  3. #3
    jayintexas is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    2
    I may play around with that, but now the "why" is bugging me more than anything. The below errors out at the 254th iteration and gives me a 3014 error, no more tables can be opened. It is almost as though the openargs is causing a connection that is not closed with each docmd.

    Private Sub tester()
    Dim x As Integer

    DoCmd.OpenReport reportname:="zzzTest", view:=acViewReport, OpenArgs:=1
    For x = 1 To 500
    DoCmd.OutputTo objecttype:=acOutputReport, outputformat:=acFormatPDF, outputfile:="c:\temp\Test3-" & Format(x, "000")
    Next

    End Sub

    The report itself has the following on open:

    Private Sub Report_Open(Cancel As Integer)
    Dim Open_ID As Integer

    If IsNull(Me.OpenArgs) Then
    Open_ID = 1
    Else
    Open_ID = Me.OpenArgs
    End If
    Me.RecordSource = "Select * from Clients_Address Where [Clients_Address].[Inv No]=" & Open_ID
    End Sub

    I tested the same routine above on a blank report with fields and it ran fine.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at this related post https://stackoverflow.com/questions/...any-tables-ope

    The solution mentioned there is to do what I suggested in my initial reply (remove the docmd.openreport and use only docmd.outputto with a report based on a query that returns one record only).

    Cheers,
    Vlad

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah - I can help with the error 3014 - no more tables can be opened - issue

    Click the link & download the AvailableConnections utility written by Ben Sacharich & originally posted here by moke123
    https://www.accessforums.net/showthr...622#post395622

    It will allow you to identify what is happening & hopefully solve it.
    Almost certainly connections aren't being closed after use

    Are you remembering to use code like this after a recordset has been used
    Code:
    rst.Close
    Set rst=Nothing
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Colin, you've grown quite fond of that utility haven't you.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi moke
    It's very good for this kind of issue.
    The irony is I've never needed to use it in any of my own databases
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-09-2015, 09:29 AM
  2. Replies: 1
    Last Post: 07-30-2015, 12:56 PM
  3. DoCmd.OutputTo question
    By mkc80 in forum Access
    Replies: 5
    Last Post: 10-16-2012, 07:11 PM
  4. DoCmd.OutputTo
    By tylerg11 in forum Reports
    Replies: 2
    Last Post: 08-09-2012, 12:22 PM
  5. AcFormat in DoCmd.OutputTo
    By SIGMA248 in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 08:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums