ACCDE Error 29045 when deleting or saving report

[gnawoncents]

Greetings,

I have a report (Report1) that I create and save using VBA coding when the user presses a button. In order to preserve the same report name without having to prompt the user, I first delete the report. Once I had the delete, create and save steps all functioning fine, I converted to an ACCDE file and get the following error when I run the code:

"Error 29045: You can't import, export, create, modify, or rename any forms, reports, pages or modules in an ACCDE, MDE or ADE database."

Here's the weird bit -- even though I get the error, it still lets me delete, create and save Report1. In fact, the entire code SEEMS to run just fine. I know I can simply trap the error in VBA and ignore it with a resume next so the user never sees it, but here are my concerns:

1) How do I figure out what portion of my code is causing the error? (Is there any way to debug an ACCDE?)

2) If I hide/ignore the error what unanticipated negative consequences am I likely to experience?

Any thoughts? Thanks!

[MMcCarthy]

Can you post the relevant code. It would help to figure this out.

[gnawoncents]

Since I can't debug an ACCDE (or at least don't know how), below is the code that runs when I press the button in question. Likely, the error occurs on line 126 (delete report), 295 (open/create report), and/or 298 (save report). Sorry for the length, and thanks for the help.

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click

'Setup recordset
Call Create_Report_Recordset

'Test for no records
If strSQLreport = "No records" Then Exit Sub

'Delete all records currently in the TBLReportCount table
DoCmd.SetWarnings False
DoCmd.OpenQuery "QRYdeleteReportCountALL"
DoCmd.SetWarnings True

'Update TBLReportCount with number of each type of answer for each question
Dim dbQuestions As Database
Dim rstQuestions As Recordset
Dim fldQuestions As Field
Dim sql3 As String

Set dbQuestions = CurrentDb()
Set rstQuestions = dbQuestions.OpenRecordset(strSQLreport)

'Add the required fields to the TBLReportCount Table
Dim dbs2 As Database
Set dbs2 = CurrentDb()
rstQuestions.MoveLast
rstQuestions.MoveFirst
Do While Not rstQuestions.EOF
For Each fldQuestions In rstQuestions.Fields
    If fldQuestions.Name Like "*Answer" Then
       If Not IsNull(fldQuestions) Then
        dbs2.Execute " INSERT INTO TBLReportCount " _
        & "(AnswerNumber, AnswerName) VALUES " _
        & "(" & Mid(Replace(fldQuestions.Name, "Answer", ""), 2) & " , '" & fldQuestions.Name & "');"
       End If
    End If
Next fldQuestions
rstQuestions.MoveNext
Loop
     
Dim strComments As String
Dim strLOOKUP As String
strComments = ""
Dim varNUM As Long
Dim intCount As Integer
     
        'Count the answers
        rstQuestions.MoveLast
        rstQuestions.MoveFirst
        Do While Not rstQuestions.EOF
        For Each fldQuestions In rstQuestions.Fields
               If Not IsNull(fldQuestions) Then
               DoCmd.SetWarnings False
                 If fldQuestions.Name Like "*Answer" Then
                    strLOOKUP = Replace(fldQuestions.Name, "Answer", "Comments")
                    If Not IsNull(rstQuestions.Fields(strLOOKUP)) Then
                      strComments = Nz(DLookup("[Comments]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), "") & "Survey ID: " & rstQuestions!SurveyID & "  Survey Response: " & fldQuestions & "  Comment: " & rstQuestions.Fields(strLOOKUP) & vbCr & vbLf
                      sql3 = "UPDATE TBLReportCount SET Comments = '" & strComments & "'" & _
                      " WHERE AnswerName = '" & fldQuestions.Name & "';"
                      DoCmd.RunSQL sql3
                    End If
                    varNUM = Nz(DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & fldQuestions & "'"), 0)
                    Select Case varNUM
                     Case 1        'varNum = 1
                        intCount = Nz(DLookup("[Count1]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count1 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 2        'varNum = 2
                        intCount = Nz(DLookup("[Count2]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count2 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 3        'varNum = 3
                        intCount = Nz(DLookup("[Count3]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count3 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 4        'varNum = 4
                        intCount = Nz(DLookup("[Count4]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count4 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 5        'varNum = 5
                        intCount = Nz(DLookup("[Count5]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count5 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 6        'varNum = 6
                        intCount = Nz(DLookup("[Count6]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count6 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 7        'varNum = 7
                        intCount = Nz(DLookup("[Count7]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count7 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case 8        'varNum = 8
                        intCount = Nz(DLookup("[Count8]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
                        sql3 = "UPDATE TBLReportCount SET Count8 = " & intCount & _
                        " WHERE AnswerName = '" & fldQuestions.Name & "';"
                        DoCmd.RunSQL sql3
                     Case Else
                    End Select
                    strComments = ""
                 End If
                 DoCmd.SetWarnings True
               End If
        Next fldQuestions
        rstQuestions.MoveNext
        Loop
         
'Close what was opened
rstQuestions.Close
dbQuestions.Close

'Allow the "Please Wait" message to close
YourVar = ""

Forms!FRMsurveyReports.Visible = False
DoCmd.OpenForm ("FRMDynamicReport")

DoCmd.SetWarnings False
DoCmd.DeleteObject acReport, "Report1"
CreateDynamicReport "SELECT * FROM QRYReportCount"
DoCmd.SetWarnings True

Exit_Command36_Click:
        Set rstQuestions = Nothing
        Set dbQuestions = Nothing
        Exit Sub

Err_Command36_Click:
        'Allow the "Please Wait" message to close
        YourVar = ""
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Command36_Click

End Sub 

Public Function CreateDynamicReport(strSQL As String)

Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim CmdBtnNew As Access.CommandButton ' command button control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String ' holds title of report
Dim lngWidth As Long  ' holds the width of the longest label in twips
  
     ' set the title
     title = "Snapshot Report"
     
     ' set initial width to zero
     lngWidth = 0
  
     ' initialise position variables
     lngLeft = 100
     lngTop = 0
  
     ' Create the report
     Set rpt = CreateReport
  
     ' set properties of the Report
     With rpt
         .Width = 8500
         .RecordSource = strSQL
         .Caption = title
     End With
  
     ' Open SQL query as a recordset
     Set db = CurrentDb
     Set rs = db.OpenRecordset(strSQL)
  
     ' Create Label Title
     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
     acPageHeader, , title, 0, 0)
     lblNew.FontBold = True
     lblNew.FontSize = 12
     lblNew.SizeToFit
     
     ' Create close command button
     Set CmdBtnNew = CreateReportControl(rpt.Name, acCommandButton, acPageHeader, , , rpt.Width - 500, 0, 780, 360)
     CmdBtnNew.Name = "BtnClose"
     CmdBtnNew.Caption = "Close"
     CmdBtnNew.DisplayWhen = 2
  
     ' Create corresponding label and text box controls for each field.
     Dim strStoreValue As String
     For Each fld In rs.Fields
     If fld.Name Like "Count*" Then
     strStoreValue = Nz(DLookup("[StoreValue]", "TBLSurveySelections", "FieldValue = " & Right(fld.Name, 1)), "!@#$")
       If Not strStoreValue = "!@#$" Then ' Do not list controls for empty choices
         ' Create new text box control and size to fit data.
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
         acDetail, , fld.Name, lngLeft + 1500, lngTop)
         txtNew.TextAlign = 1
         txtNew.CanGrow = True
         txtNew.CanShrink = True
         txtNew.SizeToFit
         txtNew.Name = fld.Name
         
         ' Create new label control and size to fit data.
         Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
         txtNew.Name, strStoreValue, lngLeft, lngTop, 1400, txtNew.Height)
         lblNew.SizeToFit
         lblNew.Name = fld.Name & "_Label"
         
         ' Capture the longest label width
         If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
  
         ' Increment top value for next control
         lngTop = lngTop + txtNew.Height + 25
       End If
     Else
         ' Create new text box control and size to fit data.
         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
         acDetail, , fld.Name, lngLeft + 1500, lngTop)
         txtNew.TextAlign = 1
         txtNew.CanGrow = True
         txtNew.CanShrink = True
         txtNew.SizeToFit
         txtNew.Name = fld.Name
         
         ' Create new label control and size to fit data.
         Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
         txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
         lblNew.SizeToFit
         lblNew.Name = fld.Name & "_Label"
         
         ' Capture the longest label width
         If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
  
         ' Increment top value for next control
         lngTop = lngTop + txtNew.Height + 25
     End If
     Next
     
     ' Move text boxes into position according to length of labels
     Dim ctl As Control
     Dim ctlName As String
     For Each ctl In rpt.Controls
         If ctl.ControlType = acTextBox Then
           ctlName = ctl.Name
           rpt(ctlName).Left = lngLeft + lngWidth + 100
           rpt(ctlName).Width = rpt.Width - lngLeft - lngWidth - 100
         End If
     Next ctl
     
     ' Create datestamp in Footer
     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
     acPageFooter, , Now(), 0, 0)
  
     ' Create page numbering on footer
     Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
     acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
     txtNew.SizeToFit
  
     ' Set OnClose event of created report
     Dim mdl As Module
     Dim lngReturn As Long
     Dim strCode As String
  
     ' Format and add an 'on close' event
     strCode = vbCrLf & "   On Error Resume Next" & _
               vbCrLf & "   Forms!FRMDynamicReport.Visible = True"
  
     Set rpt = Reports(rpt.Name)
     Set mdl = rpt.Module
     ' Add event procedure.
     lngReturn = mdl.CreateEventProc("Close", "Report")
     ' Insert text into body of procedure.
     mdl.InsertLines lngReturn + 1, strCode
     
     ' Format and add an 'on open' event
     strCode = vbCrLf & "   On Error Resume Next" & _
               vbCrLf & "   Forms!FRMDynamicReport.Visible = False"
               
     ' Add event procedure.
     lngReturn = mdl.CreateEventProc("Open", "Report")
     ' Insert text into body of procedure.
     mdl.InsertLines lngReturn + 1, strCode
     
     
     
     ' HOW DO I GET THE VB EDITOR WINDOW TO CLOSE?
     
     ' Open new report.
     DoCmd.OpenReport rpt.Name, acViewPreview
     
     DoCmd.SetWarnings False
     DoCmd.Save acReport, "Report1"
     DoCmd.SetWarnings True
     
     'reset all objects
     rs.Close
     Set rs = Nothing
     Set rpt = Nothing
     Set db = Nothing
     
End Function

[NeoPa]

I've had a chat with Mary on this and it's going to be all but impossible to work on this without info as to what is going wrong where.

Can you handle the error and MsgBox the details - including the number, message and line number. Remember when you post this that the line numbers will be different so make sure any changes made are clear and can be worked on.

[gnawoncents]

Thanks for the reminder of using MsgBoxes. I inserted about 20 different ones, and identified the areas causing problems. As anticipated, I get the following:

Error 29045 ("You can't import, export, create, modify, or rename any forms, reports, pages or modules in an ACCDE, MDE or ADE database") when I try to delete a report (report1).

Error 7802 ("The command you specified is not available in an .mde, .accde, or .ade database) when I try to create/open the report on the code line below. (listed as 295 in the previous code block)

DoCmd.OpenReport rpt.Name, acViewPreview

And, of course, line 298 causes an error when it tries to save the report that never opened.

I would really like to convert to an ACCDE, but it looks like I may not be able to for this database. That said, I have a few issues to resolve to secure the database. Would it be more appropriate to start a new thread for these, or continue here?

Thanks for all your help.

[MMcCarthy]

OK I've been doing some research on this. It seems you can't create dynamic reports in design view in an executable file like mde or accde. The only solution is to create a template report with unassigned controls and then assign a record source to the report at runtime. You can then assign controls to the various fields.

[NeoPa]

That sounds like a good solution if it works. GnawOnCents (which is a strange concept to start with - very historical - but may just be phonetic, and self-deprecating), let us know if this approach works for you. As saving and making permanent changes to the design of the database is all that is restricted within an executable (AccDE or MDE), I expect it should. A pretty neat idea really :-)

If you prefer the other approach we can also help you there I expect.

[gnawoncents]

I've given up on the idea of being able to convert to ACCDE and will simply have to lock down the database as best I can. The template report does sound tempting, but I don't think it will work in my situation. When the report is run it might have 60 controls one time and 120 the next, all with different spacing, and needing to be saved with the new format/data each time.

I'm going to go ahead and mark this thread closed with Mary's suggestion in case it can help others. Thanks for all the help, I really appreciate it!

[MMcCarthy]

Something you could look at for the future is Excel Automation. It's what I use when I need these kind of dynamic reports. The reports can then be designed in Excel and automation used to populate the reports.

Mary