Access 2016 Application Closing/Crashing Unexpectedly

I still run Access 2003 for development, and seeing as how we aren't running Office 365, I know of no one running Office 2016.

But you've moved a long way with a fair few deprecations along the way.
Here are two links
http://allenbrowne.com/ser-48.html
http://allenbrowne.com/ser-48.html
It does sound like you managed to avoid most landmines.

Now, an ACCDE isn't going to tolerate something getting corrupt, and certainly
We run this code monthly and it generates a CSV and PDF version of each clients billing.
suggests that an object getting open/closed repeatedly -- which can be a cause of grief.

I assume there is a loop of code in there someplace, repeatedly doing a block of commands.
Post it and let's have a look.

personally I would go from Access 97 to Access 2007 and then up to 2016 ... better chances for less unwanted extra stuff getting added that may cause problems with versions above 2007.

Does everything run ok in the accdb version?

Perhaps there is a timing issue and you need to put some pauses in.  What code is being used to create the files?  Are object variables being re-used and passed instead of created and destroyed in loops?

I'd create a new .accdb file in my target environment and import everything from there. Moving from 97, you'd have to go at least to 2003 or 2007 before 2016/2016 would be able to read it.

It isn't the same place each time and my error handling code isn't being hit as it would display error messages if it did.

Do you mean it does not error out on the same line of code or that it errors out when creating different output files?

How are you creating the PDF? Access has allowed native PDF creation since 2007, and that's the process you should be using.

Moving from 97, you'd have to go at least to 2003 or 2007 before 2016/2016 would be able to read it.

That is not so. I have converted some old Access 2.0 application by converting them with Access 2000 to 2000-format. This format can be read perfectly well by Access 2007-2016.

Two steps to span 10 versions. Not bad.

/gustav

There were some heavy duty gotchas going from 97 to 2000, but I cannot recall them now, only that I did support for clients -- in my pre-Access days -- that passed on the upgrade because of compatibility issues.  

But you have appear to have surmounted any of those.  

And contrary to any rumor -- and here I concur completely with @gustav -- there is presently no reason to move beyond a .mdb (Access 2000) file type.  You can if you wish, but neither the accdb or later mdb formats are required or offer anything of substance over the long term (where you should be keeping SQL Server Express Edition as a backend firmly in mind) and all versions of Access 2000+ support and can open and use the .mdb (Access 2000) file type.  Given how widely used that mdb format was/is for things like ASP and third-party programs that use a file-based database, it isn't going anywhere.

That's neither here nor there.

We still await a code block showing the loop you are executing to create your PDF/CSV files

I have read about issues others had when stepping first to another mdb from Access 97 before going to an accdb, which is why I recommend going straight to an accdb, which would be Access 2007.

Personally I see benefit to moving forward.  At least to 2013.  I have not yet used 2016 to have an opinion.

There is moving forward in Office version
2.0 -->95-->97-->2000-->2002/XP-->2003-->2007-->2010-->2013-->2016

and there is moving forward in file types
.mdb(Access 2.0)-->.mdb(Access 95)-->.mdb(Access 97)-->.mdb(Access 2000)-->.mdb(Access 2002 -2003)-->.accdb(Access 2007)

Moving forward in Office versions, given that updates stop, may be a necessity and depending upon your needs, even useful.  Very little since 2003 (except native PDF support and image controls taking .Picture as a path) has been useful to me.
YMMV

Moving forward in file types is a different story.
Nothing in the accdb format is useful.
Everything added breaks compatibility with past versions AND with SQL Server.
.accdb is not required to use or leverage Access 2007+
I see absolutely no case where moving the front end of a database to an accdb file type makes sense.

PDF and image support are big, especially showing images on records of a continuous form natively.  There are also so many more libraries that can be used in code now.  With those you can interact with web pages, use newer features in other applications through automation, and the list goes on.  I try to load the new versions early and learn what they do, and don't.  I see no reason to not consider taking advantage of new features that might be useful.

When you create a database container to put the conversion into, before any tables go in:

Turn off AutoCorrect

Microsoft Access Flaws - Failures caused by Name Auto-Correct, by Allen Browne
http://allenbrowne.com/bug-03.html

if you haven't turned it off, then it is on

in 2007:

1. Click the Microsoft Office Button Button in the upper left corner
2. choose --> Access Options (lower right corner)

in 2010, 2013:

1. File ribbon menu
2. Options (near bottom)

~~~

then:

3. Current Database from left sidebar


in Access 2000 - 2003:
1,2,3. from the menu --> Tools >> Options >> General

-----------------------------------
THEN:
4. Under Name AutoCorrect Options:
uncheck --> Perform Name AutoCorrect
5. Compact/Repair
6. then go back and uncheck --> Track Name AutoCorrect info
7. Compact/Repair again

There are also so many more libraries that can be used in code now.
Please list useful things that can be done in Access 2007+ that were not possible in Access 2003.
With those you can interact with web pages
The WebBrowser control remains finicky, and usually IE dependent.
use newer features in other applications through automation
The killer new app feature being...?
and the list goes on.
Do tell, throw out some concrete examples of things new and vastly improved.

I try to load the new versions early and learn what they do, and don't.  
I see no reason to not consider taking advantage of new features that might be useful.
Excellent.
I also look for things that become broken.
Try Me.Detail.Height = 0 in a report.
The crash is quite spectacular.
Introduced along with Split Forms in Access 2007.
First reported in October 2007.
Never fixed.
I have a lot of 0.0007 high subreport controls as a result.
FilterOnLoad and its default to TRUE
Deprecation of MSCal.ocx
Loss of the Properties Window in FormView
Loss of the Database Window and its replacement by the Nav Pain
Inability to keep the Nav Pain hidden until the second time a front-end is opened
The Nav Pain flying open anytime you refresh TableDefs

I named the two things that might be worth the pain.
I really am interested if their are others.

And the post isn't off-topic either.
@Greg_L_WER

You cannot shrink whitespace out Report sections anymore by putting Me.[WhateverSection].Height = 0 in the Format() event anymore.  It will crash or hang Access.

I did this frequently in reports that contained subreports that may or may not have data.
I used to be able to have the subreport control have some visible height for use in DesignView -- but now you can't get rid of the whitespace the control occupied.
So you have to make your subreport controls tiny and allow them to grow
This makes them indistinguishable from lines, so YOU have to keep track of them.
PITA.

Such is progress.
bugged.mdb

Thanks all... sorry for the delayed response... So much good input to reply to :)

###################################################

Nick67 - "I assume there is a loop of code in there someplace, repeatedly doing a block of commands.
Post it and let's have a look."

Fortunately I haven't had to look at this code for likely over 10 years by now... I'm sure there are much better ways of coding some of it but at the time it was built I was still very green and was happy to have it work at all ;)  In a code block prior to this it generates rows in new tables and inserts all items to appear on the current statements.  I did this so that it wasn't accessing our active tables during the whole generation process.  Once it's populated the tables it loops through the list of accounts and creates the PDF's.  I've included the record sources for one of the statement reports at the end.

-----------------------------------------------------------------------------------------

Public Sub StatementPrintToIndPDFs(YYMM As String) '*********** STATEMENT PRINT **********

Dim db As Database, MainRS As Recordset, MainRSCk As Recordset, strSQL As String, ReportName As String, SaveLocation As String, BaseFileName As String, ReturnMethod As String
Dim CurMonthTXs As Boolean: Dim qdf As QueryDef

On Error GoTo StatementPrintPDFFileName_Err

' *********************************************************
' *********************************************************
' CREATE CSV'S FOR ALL ITEMS ON CURRENT STATEMENTS
' *********************************************************
' *********************************************************

Dim Temp1, Temp2 As String
Call MakeFolder("X:\Statement Files_16")
Temp1 = "X:\Statement Files_16\"
Temp2 = Format(Forms![f_STATEMENT PRODUCTION]!IncludeDate, "YYYY-MM")
Call MakeFolder(Temp1 & Temp2)
Forms!Main!CSVSavePath = (Temp1 & Temp2)
DoCmd.RunMacro "S_ClientStatementDataToCSV_ALL"

' *********************************************************
' *********************************************************
' *********************************************************



strSQL = "SELECT [Client List].ReturnMethod, Statements.[Client Name], Statements.[Client Number], Statements.Outstanding, Statements.UseSortID, Statements.NewActivity, Statements.StatementTitle, Statements.StatementPrintByTX, [Client List].CreditCardAcct, [Client List].[Fax No] AS Fax1, [Client List].[Fax No_Pay] AS Fax2, [Client List].EMailAddress, [Client List].[Client Contact], [Client List].AcctPayableContact, [Client List].StmtPrintType, [Client List].CSVIncl, [Client List].TXSummaryReport, [Client List].MonthlyInvoiceReport, [Client List].AllInvoicesForMonth, [Client List].[Phone No] AS Phone1, [Client List].[Phone No_Pay] AS Phone2, [Client List].eSearchClient FROM Statements INNER JOIN [Client List] ON Statements.[Client Number] = [Client List].[Client Number] WHERE (((Statements.NewActivity) = True)) ORDER BY [Client List].ReturnMethod, Statements.[Client Name]; "

Set db = CurrentDb()
Set MainRS = db.OpenRecordset(strSQL)
strSQL = ""

MainRS.MoveLast
MainRS.MoveFirst

Do Until MainRS.EOF

    CurMonthTXs = False
    Forms![Main]![ClientToPrint] = MainRS![Client Number]
    Forms!Main.Refresh
    [Forms]![f_STATEMENT PRODUCTION]![Client] = MainRS![Client Number]
    [Forms]![f_STATEMENT PRODUCTION].Refresh
    
    '**************************************************************************************************************************
    ' CHECKING IF THERE ARE CURRENT MONTH TRANSACTIONS (NOT PAYMENTS) FOR TXSummaryReport & MonthlyInvoiceReport Printing (So they don't print an enpty report
    strSQL = "PARAMETERS [CurClient] Text, [StartDate] Date, [EndDate] Date; " & _
             "SELECT Service_Main.[Transaction Key] From Service_Main WHERE (((Service_Main.Date) Between [StartDate] And [EndDate]) AND ((Service_Main.Client)=[CurClient]) AND ((Service_Main.[Record Type])<>'PAY')); "
    Set qdf = db.CreateQueryDef("", strSQL)
    qdf.Parameters("CurClient") = Forms![Main]![ClientToPrint]
    qdf.Parameters("StartDate") = Forms!Main!StartDate
    qdf.Parameters("EndDate") = Forms!Main!EndDate
    Set MainRSCk = qdf.OpenRecordset()
    
    If MainRSCk.recordCount > 0 Then
        CurMonthTXs = True
    Else
        CurMonthTXs = False
    End If
    '**************************************************************************************************************************
    
    
    
    Forms![f_STATEMENT PRODUCTION]!BaseFileName = Forms![f_STATEMENT PRODUCTION]!YYMM & "-" & MainRS![Client Number]
    
    
    SaveLocation = Forms!Main!CSVSavePath & "\"
    
    DoCmd.RunMacro "S_ClientStatementDataToCSV"
    
    Forms![f_STATEMENT PRODUCTION]!TitleIsStatement = MainRS!StatementTitle
    Forms![f_STATEMENT PRODUCTION]!PrintByTX = MainRS!StatementPrintByTX
    Forms![f_STATEMENT PRODUCTION]!CreditCardAcct = MainRS!CreditCardAcct
    If MainRS!UseSortID = True Then
        ReportName = "r_Statement_Portrait_SortID"
    Else
        ReportName = "r_Statement_Portrait"
    End If

    
    BaseFileName = YYMM & "-" & MainRS![Client Number]
    
    Call PrintToPDF(ReportName, SaveLocation, BaseFileName & "-Statement", False)
    
    If CurMonthTXs = True Then
        If MainRS!TXSummaryReport = True Then
            If MainRS![Client Number] = "A833" Then
                Call PrintToPDF("r_Transaction Charge Summary Report_ByMonth", SaveLocation, BaseFileName & "-TXSummaryReport", False)
            Else
                Call PrintToPDF("r_Transaction Charge Summary Report", SaveLocation, BaseFileName & "-TXSummaryReport", False)
            End If
        End If
        
        If MainRS!MonthlyInvoiceReport = True Then
            Call PrintToPDF("r_MonthlyChargesInvoice", SaveLocation, BaseFileName & "-MonthlyInvoiceReport", False)
        End If
        
        If MainRS!AllInvoicesForMonth = True Then
            Call PrintToPDF("r_Invoice_AllByClientStatementDate", SaveLocation, BaseFileName & "-AllInvoicesForMonth", False)
        End If
    End If

MainRS.MoveNext
Loop

DoCmd.Hourglass False
MsgBox "Individual PDF's & Other Reports Have Been Created In: " & vbCrLf & SaveLocation & vbCrLf & vbCrLf & "Please Confirm Files Exist Then Click 'Complete Statements'", vbCritical

Exit Sub

StatementPrintPDFFileName_Err:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    If Err.Number = 3021 Then
        MsgBox "There Are NO Statements To Print.", vbInformation
    Else
        MsgBox Error$, vbCritical
        MsgBox Err.Number, vbCritical
'        Resume
    End If

End Sub

-----------------------------------------------------------------------------------------


Public Sub PrintToPDF(ReportName As String, FolderName As String, FileName As String, Optional Preview As Boolean = False)

    On Error GoTo PrintToPDF_Err:
    
    ' CREATES THE PDF AS A TEMP FILE - Needed to be done due to server folder permissions
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, "D:\Books\Temp\" & FileName & ".pdf", False, "", 0
    
    
    ' MOVE FILE TO DESTINATION FOLDER
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileInFromFolder As Object
     
    FromPath = "D:\Books\Temp\"
    ToPath = FolderName

    Set FSO = CreateObject("scripting.filesystemobject")
    For Each FileInFromFolder In FSO.getfolder(FromPath).Files
        FileInFromFolder.Move ToPath
    Next FileInFromFolder
    
    Exit Sub

PrintToPDF_Err:
    MsgBox Err.Number & " - PrintToPDF_Err"
    MsgBox Err.Description
    Resume


End Sub

-----------------------------------------------------------------------------------------

THIS IS THE RECORD SOURCE FOR THE "r_Statement_Portrait" Report

SELECT Statements.[Client Name], Statements.[Client Number], Statements.UseSortID, Statements.Outstanding, Statements.LastCurrentCredit, [Client List].CreditCardAcct, [Client List].ReceivableClassification FROM Statements LEFT JOIN [Client List] ON Statements.[Client Number] = [Client List].[Client Number] WHERE (((Statements.[Client Number]) Like [Forms]![Main]![ClientToPrint])) ORDER BY Statements.[Client Name];

------------------

Within the statement are 4 embeded forms with record sources as follows


SELECT z_Statement_NewItems.[Client Name], z_Statement_NewItems.Client, z_Statement_NewItems.ReqDate, z_Statement_NewItems.[Transaction Key], z_Statement_NewItems.ID, z_Statement_NewItems.Qty, z_Statement_NewItems.ServDesc, z_Statement_NewItems.Total, z_Statement_NewItems.GST, z_Statement_NewItems.Paid, z_Statement_NewItems.Invoiced, z_Statement_NewItems.StatementDate, z_Statement_NewItems.SortIDName, z_Statement_NewItems.Details, z_Statement_NewItems.MainDetails, z_Statement_NewItems.CheckNo, z_Statement_NewItems.[Payment Amount], z_Statement_NewItems.[Record Type], z_Statement_NewItems.Date FROM z_Statement_NewItems WHERE (((z_Statement_NewItems.[Record Type])="PAY")) ORDER BY z_Statement_NewItems.ReqDate, z_Statement_NewItems.[Transaction Key], z_Statement_NewItems.ID;

------------------

SELECT z_Statement_NewItems.ReqDate, z_Statement_NewItems.[Transaction Key], z_Statement_NewItems.[Record Type], z_Statement_NewItems.Paid, z_Statement_NewItems.PdDate, z_Statement_NewItems.[Client Name], z_Statement_NewItems.Client, z_Statement_NewItems.ID, z_Statement_NewItems.Qty, z_Statement_NewItems.ServDesc, z_Statement_NewItems.Total, z_Statement_NewItems.GST, z_Statement_NewItems.Invoiced, z_Statement_NewItems.StatementDate, z_Statement_NewItems.SortIDName, z_Statement_NewItems.Details, z_Statement_NewItems.MainDetails, z_Statement_NewItems.CheckNo, z_Statement_NewItems.[Payment Amount], z_Statement_NewItems.Date, IIf([SortIDName]<>"~ NO SORT ID",[Details] & " - " & [SortIDName],[Details]) & " (" & [Jurisdiction] & ") " & [PartialPmtText] AS Details1, IIf([PdDate]>[Forms]![f_Statement Production]![IncludeDate] Or [Paid]=False,[Total],0) AS UnPaidTotal, z_Statement_NewItems.[Service Key], IIf([PartialPmt] Is Not Null,"     ***** NOTE: PARTIAL PAYMENT OF $" & Format([PartialPmt],"0.00") & " WAS APPLIED TO THIS TRANSACTION *****") AS PartialPmtText FROM z_Statement_NewItems WHERE (((z_Statement_NewItems.[Record Type])<>"PAY")) ORDER BY z_Statement_NewItems.ReqDate, z_Statement_NewItems.[Transaction Key], z_Statement_NewItems.ID;

------------------

SELECT z_Statement_Outstanding.ReqDate, z_Statement_Outstanding.[Transaction Key], z_Statement_Outstanding.[Client Name], z_Statement_Outstanding.Client, z_Statement_Outstanding.ID, z_Statement_Outstanding.Qty, z_Statement_Outstanding.ServDesc, z_Statement_Outstanding.Total, z_Statement_Outstanding.GST, z_Statement_Outstanding.Paid, z_Statement_Outstanding.Invoiced, z_Statement_Outstanding.StatementDate, z_Statement_Outstanding.SortIDName, IIf([SortIDName]<>"~ NO SORT ID",[Details] & " - " & [SortIDName],[Details]) & "  " & [PartialPmtText] AS Details1, z_Statement_Outstanding.MainDetails, z_Statement_Outstanding.CheckNo, z_Statement_Outstanding.[Payment Amount], z_Statement_Outstanding.[Record Type], z_Statement_Outstanding.Date, z_Statement_Outstanding.[Service Key], z_Statement_Outstanding.PartialPmt, IIf([PartialPmt] Is Not Null,"     ***** NOTE: PARTIAL PAYMENT OF $" & Format([PartialPmt],"0.00") & " WAS APPLIED TO THIS TRANSACTION *****") AS PartialPmtText, z_Statement_Outstanding.Jurisdiction FROM z_Statement_Outstanding ORDER BY z_Statement_Outstanding.ReqDate, z_Statement_Outstanding.[Transaction Key], z_Statement_Outstanding.ID;

------------------

SELECT z_Statement_NewItems.*, IIf([Paid]=False,[Total],0) AS UnPaidTotal, IIf([Paid]=False,[GST],0) AS UnPaidGSTTotal, IIf([SortIDName]<>"~ NO SORT ID",[Details] & " - " & [SortIDName],[Details]) & " (" & [Jurisdiction] & ") " & [PartialPmtText] AS Details1, IIf([PartialPmt] Is Not Null,"     ***** NOTE: PARTIAL PAYMENT OF $" & Format([PartialPmt],"0.00") & " WAS APPLIED TO THIS TRANSACTION *****") AS PartialPmtText, z_Statement_NewItems.Jurisdiction FROM z_Statement_NewItems WHERE (((z_Statement_NewItems.[Record Type])<>"PAY"));

------------------

Select allOpen in new window

###################################################

Crystal

"Does everything run ok in the accdb version?"
So far all my testing has gone very well until I hit this issue.  I did have to make a few tweaks for a few of the functionality to work the same as it does in 97.  The weird thing for me is that the statement code runs end to end properly (multiple times) on my development VM with the ACCDB version and I was even successful running statements with the ACCDE version on that same PC a couple of times.  It makes me wonder about differences in PC's though the 2 VM's that I've been testing on are clones of each other beyond IP's and PC Names so I'd initially discounted this as a possibility.  The only difference I can think of is that the development VM has Office 365 installed and the other VM has the run time version of Access 2016 installed.  I'd like to think that it wouldn't be the issue but can't be certain.  Are there other files that are included in the full install that the run time wouldn't contain?  If the runtime is the issue then why would it generate the statements ok to a point?


"What code is being used to create the files?  Are object variables being re-used and passed instead of created and destroyed in loops?"

Hi Crystal... honestly not sure about the destroying part.  I've moved into C# the last 8 or so years so I've been loosing my VB knowledge somewhat.  Are you referring to adding in some form of "dispose"? at the end of the loop?  The crash is occurring within the " Do Until MainRS.EOF" loop.


###################################################

Scott McDaniel

"Do you mean it does not error out on the same line of code or that it errors out when creating different output files?"
Hi Scott... as far as I can tell it isn't hitting any of my error code.  It's not likely the best idea for message boxes to be included here but when it crashes there isn't any message box appearing in advance of the crash.  The " It isn't the same place each time" refers to it not crashing at the same quantity of successfully created files or on the same client number

"How are you creating the PDF? Access has allowed native PDF creation since 2007, and that's the process you should be using."
I was using (http://www.lebans.com/reporttopdf.htm) in the 97 version.  It semi worked in 2016 but not properly.  I've switched to using:
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, "D:\Books\Temp\" & FileName & ".pdf", False, "", 0


###################################################

Gutav

"Try to write protect the file (mark as Read-Only)."
Do you mean the actual application file (ACCDE)?

###################################################

Nick67

I'm sure that I tried to convert it back in the day to 2000? and had issues.  I only tried it again now due to other issues that have been causing us issues for some time.  Not sure how I lucked out and had it convert through 2000 to 2016 fairly seamlessly... other than this that is ;)


###################################################

Crystal

"I have read about issues others had when stepping first to another mdb from Access 97 before going to an accdb, which is why I recommend going straight to an accdb, which would be Access 2007."
Unfortunately I don't have access to Access 2007 to try the conversion process.



###################################################

Nick

"Nothing in the accdb format is useful. Everything added breaks compatibility with past versions AND with SQL Server. .accdb is not required to use or leverage Access 2007+ I see absolutely no case where moving the front end of a database to an accdb file type makes sense. "
Back to my thoughts about the runtime being the issue (See Crystal comments above).  Are you suggesting that I'd be better to leave it as a MDB format?  I don't have access to an Access 2007 runtime so I'm not sure how I could distribute it to all my PC's.  I see in 2016 that I have the option to save the 2000 version as a 2002-2003 MDB version.  It looks like the 2016 runtime will open the 2003 version... I've converted it from 97-2000-2003 and am now trying to figure out how to get the workgroup file to be accessible... sadly it isn't working as it did in 97 though does appear to still be something that exists...


Thanks,
Greg

Hi Nick,

> "Deprecation of MSCal.ocx"

that was quite sad as so many of my programs depended on it -- so I changed my popup form, which is shared here:

Popup Calendar for Access
http://www.rogersaccesslibrary.com/forum/popup-calendar-for-access-2007-and-above_topic597.html

> "throw out some concrete examples of things new and vastly improved"

I have been developing with 2007 and above for many years.  I also still use lower versions because I connect to others and help them on their computer.  It would take me a few hours to really do this topic justice.  Aside from biggies already mentioned, being able to make things look nicer on forms and reports, especially with colors, is greatly improved.

Intellisense is also in many more places.  I miss toolbars, even still.  My QAT (Quick Access Toolbar) is below the ribbon and has icons all the way across.  Most of the time, my ribbon is not displayed because most of what I use is on the QAT or a right-click away ~

I have come to like the Navigation Pane, now that I consider it when I name things.  I really like the Search Bar and have it on most of the time.  I also like selecting multiple objects in the Navigation Pane and dragging them (like to the Relationships Window or a Query) -- or pasting into the Navigation Pane to create a new table with data from the clipboard.

As for the quirky things and things that cause issues like split forms, multi-value fields, attachment fields, AutoCorrect -- I don't use them.

I use the web browser control in a few of my applications to do simple things like play videos.  This is VERY nice since I make videos :)  For kicks, I also figured out how to play audio files with Access.  Most of the interaction I do with web pages is using VBA and reference libraries.

> "things that become broken ... Try Me.Detail.Height = 0 in a report."

thanks, I have not come across that ... but when I see an opportunity and have time to crash Access, I'll try it :)  Now, however, there are also Can Shrink properties.

There are many more properties. For instance, Repeat Sections and Hide Duplicates for reports, and Alternating Colors (also reports) and Themes for forms.  There are new icons such as Make Horizontal Spacing Equal and Make Vertical Spacing Equal.

Someone somewhere must have made a list.  I'd love to see toolbars come back but I'm happy with my QAT customizations (which I also share with anyone who asks)

get the workgroup file to be accessible
Not happening.
Security has been removed.

Now we get into it.
You said it 'crashes'
That is different from 'silently fails to complete the task'
FileSystemObject is very handy, but notorious for silent failure if something goes awry.
Describe your crash!

Sorry Nick... maybe crash wasn't the right term.  I guess 'silently fails to complete the task' is a more accurate description of my problem.  It's running fine and then the program just closes.  Any logic to my thought on runtime version vs full version of Access 2016?

"Security has been removed." - Assuming you're referring to 2007?  I did manage to get the MDW file to work in 2003 using the 2016 runtime.  Am I missing something?

Thanks,
Greg

In here

    Set FSO = CreateObject("scripting.filesystemobject")
    For Each FileInFromFolder In FSO.getfolder(FromPath).Files
        FileInFromFolder.Move ToPath
    Next FileInFromFolder

Select allOpen in new window

You'll run into silent FSO failures if the .Move cannot be completed.
There isn't a lot of error handling there.
The move will fail if the PDF isn't quite closed.
It will also fail if the destination file already exists.

So before issuing the Move, you need to check that the destination DOES NOT exist and that the source is closed.
FSO.FileExists(TheFileInQuestionsFullPathAndName). for the first and

Function FileLocked(strFileName As String) As Boolean
    On Error Resume Next
    ' If the file is already opened by another process,
    ' and the specified type of access is not allowed,
    ' the Open operation fails and an error occurs.
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    ' If an error occurs, the document is currently open.
    If ERR.Number <> 0 Then
         FileLocked = True
       ERR.Clear
    End If
    On Error GoTo 0
 End Function 

Select allOpen in new window

for the second.

Workgroup security was removed from the .accdb file format
https://support.office.com/en-us/article/Set-or-change-Access-2003-user-level-security-in-Access-2010-0c6a10e7-966f-44f4-864e-5d2ef79439fa

Hi Greg,

thanks for all the information.

What does "S_ClientStatementDataToCSV_ALL" do?

to get something you can paste, customize the QAT and add the Convert Macros to Visual Basic icon.  Then, when you are in design view of the macro, you can click the icon and then paste the resulting VBA code here ~

instead of creating a CreateQueryDef, it would be better to construct the parameter values into the SQL and open the recordset using the SQL.

there is a lot of writing to open forms done during the procedure.  Is this all necessary?  Or can all the form writing be consolidated into one section so those objects aren't referenced so many times?  Perhaps just one form for the progress messages?

I also prefer to use variables and read everything from forms into variables before I start any automation process.  During the process, there may be only one form I write to with progress updates -- and it is an unbound popup form that stays up until the process is done.  

For instance, the value of MainRS!TXSummaryReport is not going to change with each iteration of the loop.  Read that value into a variable before the loop starts.  There are many places you could do this.

In PrintToPDF, a filesystemobject is being created.  This should be done in the calling routine and passed.  It should not be created all the time like this.  Anyway, it is not being released so code to release object variables should be added to your exit code.

Here is a short video on error handling.  It also explains exit code and cleaning up object variables.

1. basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

> "Unfortunately I don't have access to Access 2007 to try the conversion process."

do be sure AutoCorrect is completely off BEFORE importing anything into a new container ~ and most definitely AutoCorrect Perform.  This means instead of opening and doing a SaveAs, open the blank db and import objects.  Check Reference Libraries and compile the code.  Although now it is done ... so fyi for the future.

@Crystal
The CreateQueryDef is generally not how that gets done anymore, but it certainly is a six-of-one, half-dozen of another scenario.  You are opening a parameterized sql statement.
Take your pick.
Build a string
Build a querydef
Open a querydef
Create a querydef in memory
There are more ways to create a recordset than you can shake a stick at.
Nothing bad there -- just old-school
there is a lot of writing to open forms done during the procedure.  Is this all necessary?
Of course it is.
The form is a report parameter source form.
The reports are taking form controls as parameters.
So, the loop changes values on unbound controls, and then runs the report.
That's actually best-practices in regard to how you get lots of values to a report.

In PrintToPDF, a filesystemobject is being created.  This should be done in the calling routine and passed.  It should not be created all the time like this.
You could do that, but unless you have a performance issue, there's little point.
Anyway, it is not being released so code to release object variables should be added to your exit code.
You could put
Set FSO = Nothing
at the end of the routine if you don't trust VBA garbage collection, but when the routine quits, FSO gets destroyed.
There's no grief there.

Dollars to donuts there an issue with the FSO.MoveFile (or File.Move)
Now, having been there, done that with moving PDFs that aren't quite closed, I would probably separate the creation and the move.
It look like all the files are being moved to SaveLocation = Forms!Main!CSVSavePath & "\"
and that doesn't change dynamically, correct?

So I'd move this out of PrintToPDF

    ' MOVE FILE TO DESTINATION FOLDER
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileInFromFolder As Object
     
    FromPath = "D:\Books\Temp\"
    ToPath = FolderName

    Set FSO = CreateObject("scripting.filesystemobject")
    For Each FileInFromFolder In FSO.getfolder(FromPath).Files
        FileInFromFolder.Move ToPath
    Next FileInFromFolder

Select allOpen in new window

And replace it with

Private Sub MoveToFinalLocation(FolderName as String)
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim TheFolder as Object
     
    FromPath = "D:\Books\Temp\*.pdf"
    ToPath = FolderName

    Set FSO = CreateObject("scripting.filesystemobject")
    Set TheFolder = FSO.GetFolder(ToPath)
    If TheFolder.Files.Count = 0 'it is empty and won't make the next statement fail
        FSO.MoveFile FromPath, ToPath
    End If
    Set FSO = Nothing

Select allOpen in new window

and call MoveToFinalLocation after the loop creating the PDFs is complete.

Crystal,

"What does "S_ClientStatementDataToCSV_ALL" do?"
This is a macro that creates a CSV file (Export Delimited) for all new and outstanding items for all accounts from 2 tables populated in an earlier section.  This is being created ok every test so I think I'm ok to leave this as is.  As for your other improvement ideas... I think they are all great ideas... unfortunately my end game for this whole conversion process is just a temporary bandage to give me time to build a completely new system in C# (WPF or Windows Forms - which is my day to day language these days and one I'm much more familiar with now) so I'm hoping to do a few changes as necessary to get it back to stable again to give me some breathing room.  Plus I really love all the functionality that Visual Studio has :)


Nick,

I've pulled out the move code and created the new Sub as you suggested.  I also temporarily commented out all moving code for now to exclude it from the possible culprits.  I've run it twice now and it did silently close during the PDF creation both times (Both on the VM with the Access Runtime installed)  I think I'm safe to assume that the PDF creation is the culprit?  The move might still be an issue as well but thought it best to solve this part first :)

Back to the run time vs the full version... any possibility that this is related?  I have yet to have an issue when running the statements on the VM that Office365 is installed on... running either the ACCDB or ACCDE versions...?  On the other hand I've never had a successful run on a VM running the run time Access.  I'd hope that they would work the same but...


Thanks,
Greg

Back to the run time vs the full version... any possibility that this is related?
The run-time has no support for changing objects, so anything that might be causing changes to the project will bring the run-time to its knees.
I've run it twice now and it did silently close during the PDF creation both times (Both on the VM with the Access Runtime installed)  I think I'm safe to assume that the PDF creation is the culprit?

Pretty much, since that's really all this code is doing

You are still using a mdb, right?
Make a back up
Rename the mdb to .mdr
That tells full-blown Access to run the file as if it were just the Access run-time (emulates the run-time).
Can the machine that runs it successfully as full-blown Access run it as an emulated run-time?

Nick,

It's the ACCDB.  I'd tried going back to a 2003 MDB but it was causing other issues that I'd solved already in the ACCDB.  Is there such a process (MDB to MDR) in the ACCDB extension? I'll try ACCDR and see how it goes.

Thanks,
Greg

Yup
Accdb --> Accdr for run-time emulation

If you need to create the PDF and then copy it, why not just use FileCopy or Name instead of mucking around with the FileSystemObject.

FileCopy "Source", "Destination"

Or

Name "Source", "Destination"

@Scott
Error checking.
Wildcards.

The whole shebang should be

FileSystemObject is useful for everything except checking locked status and makes for nice, clean code.
Dir, Name, Kill and FileCopy do many of the same things.

<grin> I am definitely on the record as liking FSO </grin>
And Kill makes me nervous, Name is a property (and if you screwed up using a reserved word, a fieldname in table(s))
The recursive nature of Dir() and it's ins-and-outs are a bit opaque compared to setting up a nice visible,  human-readable For Each myFile in MyFolder that you get with FSO.
And FileCopy doesn't do wildcards, does it?

Your mileage definitely varies, because I know FSO is not your goto thing.
I like being able to CTRL-F
"Dim FSO as Object"
 in the VBE and know I am getting to code blocks dealing with the file system.

Everyone's milage may vary.

Nick67

Thanks Nick...

Renaming it as the ACCDR did cause it to stop and close as it does on the other VM running the runtime version.  It's starting to feel like my best option will be to have a dedicated VM with Office365 installed on it to use for statement production only... or do you still think that there might be a way around this?

Thanks Scott...
The move code was the first one that I found when I started looking and seemed to work :)

.. ACCDR did cause it to stop and close as it does on the other VM running the runtime version.

If I recall correctly, the FileSystem Object will not run in a runtime environment.

Luckily, in most cases you really don't need it, as you can substitute the methods with the native disk and file functions of VBA (inherited from Access Basic and even QBASIC).

/gustav

Nick:

I'll repeat:

"If you need to create the PDF and then copy it,"

If the author needs advanced file handling processes the FSO would likely be a better choice. But if all you need to do is copy a file, then using FSO isn't really necessary. Plus, it's obviously not working in this environment. FSO can be stopped cold by anti-virus applications, depending on how they're setup (I've run into this several times).

FileCopy or Name (both of which are Functions) perform simple tasks, but they do a good job of them, and they're very simple to use. If you need to check for file existence, then use Dir, and then remove it with Kill (or rename it with Name). I don't see where the author needs to use wildcards, or anything of that nature, but perhaps I missed something and they would benefit from figuring out why FSO doesn't work in their environment. If not, the FileCopy or Name is a viable alternative,

Duly noted, but since the code runs perfectly as .accdb and not as .accdr on the same machine, that cannot be the issue here.

Sure it can, depending on the way the antivirus is setup. If the antivirus is setup to whitelist .accdb but NOT .accdr (or some other extension), then the antivirus engine will stop it. Many secure corporate environment do not allow the use of the scripting runtime.

do a bulk move operation of the created PDF's at the end of the routine

Which can also be accomplished with a simple Dir loop.

Again, not saying that FSO is the wrong tool (or the right one), just saying that there are valid reasons to not use it, and apparently the author is having issues with the code.

I agree that simplifying the code by using functions built-in to Access is a good thing to try. If nothing else uses FSO, the reference can be removed too.

I'll take your word for it, but it still seems remarkably strange.
A/Vs I have worked with will whitelist file types and directories that will not be subject to scanning  -- and not files that will be permitted to execute certain actions.
But heuristic scanning may do weird stuff.
It also seems counter-intuitive that Access using native FileCopy could do the same file system activity as FSO and be permitted while FSO would be blocked -- after all, it is Access, running in a user context that is doing the same low-level activity regardless of the library invoked.

You have seen it, I will defer to your experience that A/V and FSO may come into conflict in certain corporate environments.  Banning .vbs files, now that I've seen, but that's not the mode with FSO.

@Crystal
1.  It's late-bound as declared
2.  There's other goodness in the Windows Script Host Object model beside FSO, and that the Asker may have in use elsewhere.

A/Vs I have worked with will whitelist file types and directories that will not be subject to scanning  -- and not files that will be permitted to execute certain actions.

A/Vs can whitelist the .accdb extension but NOT the .accdr extension (not the file itself, or specific actions in the file). That's what I'm referring to.

I have no idea what the FileCopy and such do under the hood of Access, but it does not seem to use the same mechanisms as FSO (since FileCopy will work where FSO methods will not). I have seen it happen more than once with my high security clients, and long ago gave up with FSO unless I have no other way of doing the process.

Good enough.
Maybe cscript.exe and wscript.exe get embargoed and Access calls them and fails in such environments?
Who knows?

@Nick, I like FSO! ... just that if all you have is a pin to push, you don't need a rocket ;) ~

adding to what Scott said ... FileCopy will even work on the open database you are in :) ~

Thanks guys... I really appreciate everyone's help on this!

Nick

"When run as an .accdr, does the code now create a varying number of PDFs and leave them all stranded in their original location (grief with the report not liking being open/closed so many times repeatedly) or does it create all the PDFs and fail to move some of them (grief with FSO)"

All testing done using my development VM (with the full Office365 installation)
Note: All file move code has been commented out for now so it should complete with all PDF's still in my Temp folder.

ACCDR Version - Copy of ACCDB renamed to ACCDR
I've run it twice.  Both times it silently stops leaving a random number of PDF's in my Temp folder (90 & 73 respectively)

As for the thought on anti-virus being a potential reason.  I disabled Windows Defender on this VM... no other AV software has been installed.  Running it again... silently stopped at 92 files.

ACCDE Version - Defender still disabled
It did run end to end properly - 317 PDF's in the Temp folder as expected.

ACCDE  renamed to ACCDR Version - Defender still disabled
Silently stopped at 89 files


Thanks,
Greg

Well...
I would have expected the .accde renamed to .accdr to run to the end.
Apparently not :(
Damn.
So the accde is not a solution

But, it is the report that is the culprit.
I've seen that before, though.

Right now you are cranking out the PDFs via
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, "D:\Books\Temp\" & FileName & ".pdf", False, "", 0

It may be that the run-time version is too fast for itself, that it is heading to the next iteration of the loop before the report is fully released from the PDFing process of the previous iteration.

Let's try a little delay first
Here's our friend Sleep

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Select allOpen in new window

Put it in a code module (not a form or report one)
To call for a half-second delay throw this line in
Sleep 500

Throw a half-second delay after the DoCmd.OutputTo command and test it.
Will it now run as an accdr?

Test and post the results.
Play with the delay length.
Nothing more than 2 seconds.

Question: does the unit(s) have a PDF printer (ie full-blown Abobe Acrobat) on the system?

Hi Nick,

ACCDE renamed to ACCDR
500ms Delay - It stopped after creating 84 PDF's

2000 ms Delay - Ran it 3 times... It stopped after creating 65 / 61 / 60 PDF's... Sigh... :(

"Question: does the unit(s) have a PDF printer (ie full-blown Abobe Acrobat) on the system?"
The VM I'm testing on does not but most PC's that would run the software later do.

It stopped after creating 84 PDF's

Just for fun, try it with a smaller subset of data and see if it still crashes.

I didn't see this mentioned anywhere, but if so I apologize for restating: be sure your installation of Office and Windows is fully up to date, and that any libraries (like your PDF software, for example) are fully up to date as well.

Ok, if pausing doesn't help, then let's preview and close without saving changes
Try the loop in accdr with the PDF outputting commented out.
If that works ( x number of previews) then try it full-blown with PDF creation.

<grin>I think by now you know the drill</grin>

DoCmd.OpenReport ReportName, acViewPreview
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, "D:\Books\Temp\" & FileName & ".pdf", False, "", 0
DoCmd.Close acReport, ReportName, acSaveNo

Select allOpen in new window

This is it, though.
If previewing and explicit closing don't solve the grief, then it is time to either give it up, or start looking at the structure of the report itself.

Thanks Scott... I did run it with the ACCDR version and a smaller set of data... it did complete properly (24 PDF's in this case).  I'm also going to check that all Windows updates are done and try the full set of data again if there are any updates missing.

Nick... I'm trying your preview suggestion now and will let you know how it goes.

Thanks,
Greg

Nick... sadly it made it to 42 PDF using the whole dataset and the preview/create/close process.  I'm going to try removing 3/4 of my subforms in the report and see if I have any better luck.  Really appreciate that you guys have stuck it out on this issue for so long :)

Thanks,
Greg

As noted above, MS broke things with the introduction of Split Forms.
An invisibly short subform rarely makes much sense, while an invisibly short subreport (when it has no data) makes a great deal of sense.

But the code in the bug .mdb I posted above shows that MS doesn't differentiate between form and report sub-objects -- and broke them.

Does your report try to shrink the size of subreports?
That can be troublesome.
Even resizing can open a can of worms

If the report has any code that tries to alter the size of your subreports, disable that and see if your loop will complete.

Thanks guys!  Unfortunately I've just uncovered some data corruption that I've got to deal with :(  Will get back to this as soon as I can.

Hi Scott/Nick,

I've tried the DoEvents process and I've also created a new empty DB and imported all the objects into it.  Unfortunately both tests ended with the same results :(  The import objects into a new DB did fix one thing that I'd not noticed earlier... where I couldn't delete an object due to a permissions issue.  The import into a new DB did solve this issue :)  Small victory but I'll take it.

I've also tried creating a new report and importing all the objects off the other report as well as the decompile/compile idea...

Sadly all these great ideas haven't solved the issue.  I'm thinking that I'm going to just have to bite the bullet and pickup another copy of Access that can be used on a VM for statement production only... It appears that MS's run time version of Access just doesn't run the same as the full version.  I really appreciate all the ideas and time everyone spent on my question.

Thanks,
Greg

Adding points to all that helped... all the effort has to be worth something even if the problem remains unsolved :)

Sorry that it has turned out to be insoluble.

I don't know if your report has subreports
Mine that crashed when running in loops has 10 of them.
The solution for that one was to create group headers and footers to put them in isolation from each other, and still 0.0007" with CanGrow =  True and code in each Group Header/Footer that toggled visibility based on whether there would be data in the subreport.

We haven't looked at the report that crashes for any fixes that may have been needed.

Or it may be intractable.

Np :)  Sometimes things unfortunately just go that way.  The report does have 4 sub forms but I had the same issue with a completely blank new report so I suspect it's something else in my case.  C'est la vie ;)

This problem persist since Office 2016/Access 2016 has been deployed

Output to PDF/XPS from Access 2016 (.accdb) is no problem.
Output PDF/XPS  from (.accde or .accdr) crashes after 8 minutes.

Command: DoCmd.OutputTo  acOutputReport, "myReport", acFormatPDF ...

At first the DoCmd.OutputTo method works fine.
But if you wait more then 8 minutes and then try to run DoCmd.OutputTo again, Access crashes!

i would appreciate any help in this matter

@Member_2_7970163
Who are you?
Not the Asker, Greg_L_WER, correct?

If you have a question, please go to your profile first, and give yourself a proper membername, and then Ask a new question.

Hi Nick67... it's definitely not me.  I can confirm that it is only occurring when NOT running on a PC with the full version of Access on it thought the 8 minute timing is not what we've experienced here.  It has occurred pretty much as soon as the code starts running.  Sadly my only work around here is to run our statement production on a machine with the full version of Access on it though I'd love to find a proper fix for this issue.

Thanks,
Greg