Record/table locking after export to PDF/Excel

[Brujah]

Hi All, <o></o>
<o> </o>
I’m having a bit of an issue with record locking and I really can’t work out what the problem is…<o></o>
I’ve created a split database, record locks are set to no locks, and default open mode set to shared. It’s built in ms access 2007, and until now its gone pretty swimmingly… it’s not the first db I’ve created, but I would not consider myself expert by any means…<o></o>
<o> </o>
The bit that seems to be causing an issue is exporting… it doesn’t seem to matter whether I export via vba or the inbuilt exportwithformatting macro, but the db is locking/connecting to the relevant tables in the backend and not releasing when the report is then closed… and this is happening constantly for exporting to excel and pdf on all of the reports. <o></o>
When I open the report itself, then close without exporting the connection to the back end closes correctly, yet when the same report is exported, the connection remains, which if enough exports are done will then produces the ‘can’t open any more databases’ error message…<o></o>.<o></o>
I’ve tried decompiling /compiling/creating a new db/ using snapshot queries/ creating very simple reports with no filters and little formatting/ removing the errorhanding in the vba/ trying different methods for exporting(macro and vba) but nothing seems to cure the issue, and i cant work out how to end these bogus connections manually without shutting and reopening the db.
At this point I’m tearing my hair out… (this code works correctly in other dbs I’ve produced!!!)

this is one of the codes i've tried if that helps any...

Button click to call PDF procedure….</SPAN>

Private Sub cmdPDF2_Click()</SPAN>
Dim myPath As String, strReportName1 As String, strReportName2 As String, strReportName3 As String</SPAN>
10     If gcfHandleErrors Then On Error GoTo PROC_ERR</SPAN>
20     PushCallStack "rep900AIRTRoles.cmdPDF2_Click"</SPAN>
30           strReportName1 = "Resourcing - " & Me.LabelHeader2.Caption & " - " & Format(Date, "dd mm yyyy")     ’ main title for pdf</SPAN></SPAN>
35           strReportName1 = Replace(strReportName1, ":", "")                      ‘ get rid of illegal characters</SPAN></SPAN>
40           strReportName2 = ""                                                                                     ’</SPAN> extra space incase needed for future addition to title</SPAN></SPAN>
50           strReportName3 = ".pdf"                                                                             ‘format required</SPAN></SPAN>
60            myPath = "\04 Resourcing Reports\01 Teams\04 Roles\"               ' Exact folder to save PDF in</SPAN></SPAN>
70          strMyPath = GetPath() & myPath & strReportName1 & strReportName2 & strReportName3       ’ full save to file path</SPAN></SPAN>
80           strRepName = Me.Name                                                                             ‘ Report name</SPAN></SPAN>
85           strOpenArgs = Me.OpenArgs                                                                      ’Report filter</SPAN></SPAN>
90     Create_PDF2</SPAN>
 
95  MsgBox "The report you requested has been saved in the '" & GetPath() & myPath & "' folder.", vbInformation</SPAN>
PROC_EXIT:</SPAN>
       PopCallStack</SPAN>
100     myPath = Empty: strMyPath = Empty: strReportName1 = Empty: strReportName2 = Empty: strReportName3 = Empty: strRepName = Empty</SPAN>
Exit Sub</SPAN>

 Sub Create_PDF2()</SPAN>
 
10           If gcfHandleErrors Then On Error GoTo PROC_ERR</SPAN>
20           PushCallStack "Create_PDF"</SPAN>
30            DoCmd.OpenReport strRepName, acViewReport, , , , strOpenArgs</SPAN>
40            DoCmd.OutputTo acOutputReport, "", acFormatPDF, strMyPath, False, , , acExportQualityPrint</SPAN>
50            DoCmd.Close acReport, strRepName</SPAN>
 
PROC_EXIT:</SPAN>
       PopCallStack</SPAN>
       strRepName = Empty</SPAN>
       strMyPath = Empty</SPAN>
       strOpenArgs = Empty</SPAN>
Exit Sub</SPAN>
 
PROC_ERR:</SPAN>
       GlobalErrHandler</SPAN>
       Resume PROC_EXIT</SPAN>
End Sub</SPAN>

[FONT=Calibri]

but as i said it doesnt matter how complicated or basic the query for the report, or if i use vba or built in macros.....
Any thoughts or ideas to try would be greatly appreciated!!!!

Regards

Brujah[/FONT]

 

[Micron]

You say it used to work now doesn't? What changed - OS, Office version, bit version? Or maybe just some code changes that are now rearing their ugly heads? If it works on a computer with those same parameters as when it did work, ignore the following suggestion I guess.
Since you don't show the code for the called procedures, I'm just guessing that maybe one of them creates a recordset that doesn't get set to Nothing (which would persist in memory at least), or where you have specified an attribute for LockEdit (though I'm not certain this would cause a persistent lock on records). You say there's no problem if you open the report directly, so it must be a code issue. Perhaps if you step through the code and use the immediate window to test some attribute of the recordset (assuming you have any) where you would not expect a response, you might find it's still open.

 

[Brujah]

Hi Micron,

Thank you for replying...

Unfortunately it doesnt seem to matter if the PDF is created via the macro for exporting that is inbuilt to access or via my VBA, the reports are all using standard queries as their record sources rather than using an SQL statment on load, so i'm not sure where to look for the open recordset if i'm not creating them in VBA, especially as it all goes smoothly until the report is PDF'd, without exporting, the connection to the backend closes as it should do, it's only when exported that the problem with open connections start.

I've even tried creating a whole new database, unrelated to the one thats misbehaving, added just 1 table with one field filled with the letters of the alphabet then split, created a new report of the alphabet table, added a button with the macro for exporting, (No VBA in the new database at all) and its still leaving the connection open after the PDF is created and report closed.... Im wondering if maybe i've changed a setting in Access without realizing, but i've no idea where to start looking....

so frustrating.....

Brujah

 

[Micron]

try outputting to a different format such as text file and see if it holds the connection. If not, the problem would seem to be on your pdf side of things. Perhaps the default handler for pdfs was changed to a Windows version after an OS upgrade. I've read that happened to some people, but the problem was related to displaying a pdf in an OLE frame. Don't know if that could be your issue, but you say it used to work so something behind the scenes must be different.

 

[JSS19]

Did you ever find a solution to this problem? I am having the same issue and I'm going nuts trying to figure out what the problem is.

Thanks,
JSS19