Runtime error 3211

Question

Using Access 2016, I create a report from a table.  If I display or print the report, everything works fine.  However, if I create a PDF (using docmd.outputto),

the PDF is created fine; but, the Record Source table remains locked after the report is closed (Runtime error 3211).  This means that the Access application must be closed and re-opened to create the next report. 

I have tried to use the table directly and via a query with the same results.  I am using Windows 10 Home and I've also tried Office 365 with the same results.

Thank you

Answer 1

Thank you for the suggestions.  Unfortunately neither solved the problem.  The first suggestion was to delete the entries rather than dropping the table.  It resulted in a slightly different error; but, because the table is locked, I cannot delete entries.  The runtime error was 3008 and it said that the operation failed because the table was in exclusive use by another person (I don't have a split database so that is not the case) or another process.

The second suggestion resulted it the same 3211 error that I initially reported.  

It appears to me that the PDF handler is not properly releasing the table.  I tried doing a print preview and then a save to PDF from there and that does not have the problem.  However, I can't control the name assigned to the file with that method and it is difficult to explain to my user.  

know that the delete and drop don't work; but, it may be possible to add to the table and then use a query to pick up only the last entry with an order by desc.  Since the table is not locked when the program starts, I could clean out the entries or drop the table at start-up.  I don't know if that will work and I would still appreciate any other suggestions that you may have.

Answer 2

Thank you for the suggestions.

When I tried the first suggestion - delete the rows rather than the table.  This gave me a different but similar error.  Run-time error '3008':  "The table "tblTemp" is already open exclusively by another user or it is already open through the user interface and cannot be manipulated programmatically.

The second suggestion - separate procedure that closes the Report before the Outputto resulted in the same '3211' previously reported.

Answer 3

Ok, the first thing I would try is to not Drop the table. Instead, delete all records and use your query to repopulate it.

Dim strSQL As String

CurrentDB.Execute "DELETE * FROM tblTemp;"

strSQL = "INSERT INTO tblTemp…"

CurrentDB.Execute strSQL, DBFailOnError

The second thing I would do is run a separate procedure to export to PDF:

Public Sub pExportPDF(strReport As String)

DoCmd.Close acReport, strReport, NoSave

DoCmd.OutputTo acOutputReport, "", acFormatPDF, strReport

End Sub

Answer 4

In the frmMain, I have a button that creates a table and opens a report.  I won't show all code used to create and populate the table (unless you need to see it); but, here is the snippet that displays the report: 

        sReport = "Property Report"

        DoCmd.OpenReport sReport, acViewReport

The report uses the created table as the Record Source for the Report.  (I have also tried to use a query as the Record Source with the same results).

On the Report, there are 3 buttons one to Exit,  one to Print, and one to Save the Report to a PDF file.

Private Sub btnExit_Click()

    DoCmd.Close acReport, Me.Name, acSaveYes

    DoCmd.OpenForm ("frmMain")

End Sub

Private Sub btnPrint_Click()

    DoCmd.OpenReport "Property Report", acViewNormal

End Sub

Private Sub btnSave_Click()

    Dim fDialog As FileDialog    'In tools/references - Enable Microsoft Office 16.0 Object Library

    Dim result As Integer

    Dim sFile, sTemp As String

    Dim fileLocation As String

    Dim sPropName As String

    Dim i As Integer

    sPropName = Replace(gsCurrentPropertyName, " ", "_")

    fileLocation = Date

    fileLocation = Replace(fileLocation, "/", "-")

    fileLocation = "Property_Report_" & sPropName & "_" & fileLocation & ".pdf"

    Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

    'Optional: FileDialog properties

    fDialog.InitialFileName = fileLocation

    'Show the dialog. -1 means success!

    If fDialog.Show = -1 Then

        sFile = fDialog.SelectedItems(1)

        i = InStr(1, sFile, ".")

        If i = 0 Then

            sFile = sFile & ".pdf"

        Else

            sTemp = Right(sFile, Len(sFile) - i + 1)

            If LCase(sTemp) <> ".pdf" Then

                sFile = Left(sFile, i - 1) & "pdf"

            End If

        End If

        DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFile

    End If

End Sub

In all cases, neither the Print nor the Save button cause the Report to return to frmMain.  The Exit button must be used to end the Report. 

When the Report is ended, the frmMain is displayed without error.  However, when I attempt to create a new report, I Drop the table and then create and populate it.  If I simply looked at the Report or Printed the report, everything works fine.  But if I Save the Report to a PDF and then Exit, the table remains locked and I get the 3211 error when I attempt to delete the table.

Public Sub DropTempTable()

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='tblTemp'")) Then

        sSql = "DROP TABLE [tblTemp];"

        CurrentDb.Execute sSql

    End If

End Sub

I get a error message.  It says Run-Time error '3211':

"The database engine could not lock the table 'tblTemp' because it is already in use by another person or process."

P.S.  I have search the internet for this problem and have found other posting of this problem but no solution.

Thank You

Thank You

Answer 5

Can you please show us the code you are using? When asking for help involving a code snippet its always best to include the code in question. Also please include the text of the message, and if applicable, the code line throwing the error.