access 2007 converting accdb to accde

AD-PIPELINE.accdbI am trying to convert a access database to accde file.  When I run the accdb file everything executes with no problem.  when converted the database one of the command buttons returns the following meassage.  "The command you specified is not available in in a accde database". I have the database in a trusted location. So I don't understand the message.  The command button seems to have a problem producing a pdf file in an accde database.  I am attaching a copy of the database if some can take a look and see if you can notice the error. It happens when select produce pdf app and process.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
this seems to work

i changed this line in Function ApptoPDF()

'        myoutput = "y:\My Documents\IFS\" & RTrim(myrs![ApplicationID]) & ".pdf"


        myoutput = CurrentProject.Path & "\" & RTrim(myrs![ApplicationID]) & ".pdf"

saved, then do a debug >compile

then create an .accde
centralmikeAuthor Commented:
I change the line of code above.  The Accdb file works fine.  But when you make it a accde file I get the same error above.  I have included a copy of the module with the change code.

You can change the accdb to accde, by only renaming the extension.
Did you install the Add-in SaveAsPDF for Microsoft Access?

When I run your application the accdb works fine. It creates the pdf file.
When I run your application after renaming the extension to accde, it works fine.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

centralmikeAuthor Commented:
Thanks Danny for the update.  I did what you said and that works fine.   But renaming the file extension to accde does not make it an executible file.  The file is still and accdb file because you change queries and code modules.  I made sure the "savesasPDF" file was downloaded.  Do you have to set a reference to that file?  I didn't see in the reference library.  I would like to make the accde file from the accdb through the product.   Did you try that?

Rey Obrero (Capricorn1)Commented:
i think your problem is on this line

        DoCmd.OpenReport "Report1_PDF", acViewDesign

you can not open a report or a form in design view in an .ACCDE

what is your intention of having that line..
Hi Mike,

Well I just did. What you are trying to do is open a Report in design mode while you have converted the file into runtime. When in complete runtime you cannot open reports in design mode anymore.

DoCmd.OpenReport "Report1_PDF", acViewDesign <--- This is the issue.

Try this:
        myoutput = CurrentProject.Path & "\" & RTrim(myrs![ApplicationID]) & ".pdf"
        DoCmd.OpenReport "Report1_PDF", acViewNormal, "[ApplicationID]=" & "" & RTrim(myrs![ApplicationID]) & ""
        DoCmd.OutputTo acOutputReport, "Report1_PDF", "PDFFormat(*.pdf)", myoutput, False, "", 0, acExportQualityPrint
        DoCmd.Close acReport, "Report1_PDF"

Open in new window

The only disadvantage is that it also prints the saved report to the printer directly. Try changing the way it opens the report. Stead of acViewNormal try acViewPreview.

Haha ... After submitting my answer I just noticed the comment capricorn added. You are right about that capricorn :-)
Rey Obrero (Capricorn1)Commented:

did you see my post at http:#a37842559 ?
centralmikeAuthor Commented:
Thanks Danny and Capricorn that fixed my problem with the accde file.  But caused another issue.  The report prints to pdf file ok but also prints to my LAN printer.  I was looking for something in the page setup/printer setup that would allow to my to set the report to pdf only.

Yes that's the problem with current code. Because the report is opened it will use the default printer to automatically output to.

I have had the same problem. So I developed this approach:

Create a new module: modGlobal.
Add a new collection variable: mcolParameters

Public mcolParameters           As Collection

Open in new window

Initialize the collection somewhere in your code so it is a available at start up.

Create a new module: modHelpers
In your module put these methods:

Public Sub SetParameter(ByVal vKey As String, ByVal vValue As Variant)

' Parameter collection will be set. If the item exists in the collection, an error will be
' raised. The item then will be deleted so it can be added.

On Error GoTo SetParameter_Err
  If (modGlobal.mcolParameters Is Nothing) Then
    Set modGlobal.mcolParameters = New Collection
  End If
  modGlobal.mcolParameters.Add Item:=vValue, _
  Exit Sub

  Select Case Err.Number
    Case 457
      ' Key already exists in collection.
      Call modGlobal.mcolParameters.Remove(vKey)
    Case Else
      Resume SetParameter_Exit
  End Select
  ' Reset de error and continue function.
  Err.Number = 0
End Sub

Public Function GetParameter(ByVal vKey As String) As Variant
' This method returns the value in the collection if set using the SetParameter method.

On Error GoTo GetParameter_Err

  Dim varPar  As Variant

  varPar = CVar(modGlobal.mcolParameters(vKey))
  ' Return the value.
  GetParameter = varPar
  Exit Function
  ' Item not found.
  varPar = Empty
  Resume GetParameter_Exit
End Function

Open in new window

Build a query using the normal select statement. Put a WHERE clause as follows (Yours will have a different Id field):
WHERE [Id] = GetParameter("KeyId")

The [Id] is the identifier field or search field. Because you have defined the GetParameter method as public, the Query calls the method. The argument is the Key which you have defined in the SetParameter method.

SELECT Id, Name FROM Parent WHERE Id = GetParameter("KeyId")

Open in new window

You have to change the select statement using yours in your application.

When you want to output the report. The report is bound to the Query having the WHERE clause.

Now you need to set the Key Value after clicking the button. So put in your Click event the code before outputing the report.

Call modHelpers.SetParameter("KeyId", lngId)

Open in new window

The lngId variable is set with the Id value to select the record.

When using this approach it will add a new item in the collection with key: KeyId, having the value in variable: lngId.

By outputing the report it will open the report. The WHERE clause has the GetParameter("KeyId") value. It assigns the correct Id value to the id field.

If it is all too difficult or doesn't make any sence let me know. I have used this approach in several projects and it all outputs nicely. You can use public methods in your Query for more functionallity. It is really cool :-)

centralmikeAuthor Commented:
Danny, You are alot more advance than me with this access coding.  Can you tell me what you mean by initializing the collection? I would like to initialize when the form is first open. I receive a complie error on the lngID variable.  I can attach my database again if want put this code in it.  The main query is call "qryMain".Then I can look through the modules where you make the code changes.
Hello Mike,

I have changed your database with the code i have described in the section. Everything works fine. Even after converting to ACCDE.

Okay this is what I have done.
- Added two modules: modGlobals which contains the public collection, modHelpers which contains the two modules: GetParameter and SetParameter.

- Made a copy of qryMain to qryReport_PDF
- Changed the WHERE clause in the qryReport_PDF. The query looks like this now:
SELECT tblMain.Applicationid, tblMain.FormNumber
FROM tblMain
WHERE tblMain.Applicationid=GetParameter("ApplicationId");

Open in new window

The "ApplicationId" is the Key to look up in the collection

- Assigned the query: qryReport_PDF to the Report_PDF.

- Changed the code in your module. Now looks like:
        lngId = myrs![ApplicationID]
        myoutput = CurrentProject.Path & "\" & CStr(lngId) & ".pdf"
        Call modHelpers.SetParameter("ApplicationId", lngId)
        DoCmd.OutputTo acOutputReport, "Report1_PDF", "PDFFormat(*.pdf)", myoutput, False, "", 0, acExportQualityPrint

Open in new window

The variable lngId is declared as datatype: long.

Because the SetParameter checks if the collection is set, you don't have to initiate the collection. Otherwise you would initiate like this:
Set modGlobal.mcolParameters = New Collection

Open in new window

You don't have to do that now

- Tested. The report outputs correctly to PDF only.

I only have added the ACCDB. I have tested after converted to ACCDE.

centralmikeAuthor Commented:
Good Morning Danny, I have tested all of the changes you made to the database.  How do Iu get the "GetParameter("KeyId") " to change.  When I add a new record to the database to print a new pdf application the KeyID remains the same.  The same pdf file prints over an over again no matter how many records you.  Does the GetParameter(KeyID) need to be added to every query in the report?  Thanks again for all of your help.
centralmikeAuthor Commented:
Danny, Should the Getparamert(KEYid) point to the applicationID on the form?  That way you only get the pdf for the current record?
Hi Mike,

Sorry for not replying earlier. I didn't check your application more than only adding the methods and changing the way you can output your report.

I have checked your application and there are several issues the application came up with.
- Because you want to print the data referencing the ApplicationID, you have to pass the ApplicationID to your function.
- You are looping in your method: ApptoPDF. Why? You don't have to loop through a recordset. Just use a filter while opening the report.
- You are using subforms in your report which are not bound to the ApplicationID.

I have done the following:
When calling the AddToPDF; using the txtApplicationID as an argument. If the field is empty or null, pass Empty as argument.

ApptoPDF Nz(Me.txtApplicationID, Empty)

Open in new window

Changed the AddToPDF method:
Added an argument to the method:

Public Function ApptoPDF(ByVal Id As Long) ... 

Open in new window

Remarked the looping code. Added the If statement which checks if the Id argument is empty or not.

Added the field ApplicationID on the report and bound the using the properties of the subform: Link Master Fields and Link Child Fields, with the fields: ApplicationID of both Main report and Sub reports.

I have added the Accdb file again with these changes so you can study them.

Now when you print the data you use the ApplicationID as parameter. This parameter is set for global use. When the report is opened it will trigger the Query and the GetParameter is fired. The applicationID field in your report has the correct ID value the bound subreports will show the data referencing the ApplicationID.

I have noticed that you have to fill in every data in your form to show up the data in report. You have to develop some more in your application. But this will do for this topic i guess :-)

centralmikeAuthor Commented:
I have learned a lot from your code that you put into the application.  I have figure out additional ways of doing things from your samples.  Performance is the next item I would like to tackle.  The form takes several seconds to open with only three records in the entire database.  If you know of any great access manuals you could recommend I would greatly appreciate it?  I am just learning by asking questions.  Thanks again for all your help and great suggestions.
Hi Mike,

I hope it brought you to a better solution. One good book is: WROX - Access 2007 VBA Programmer's Reference:

I suggest to close this topic now so it won't remain open.

Hope to see you more on Experts Exchange.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
centralmikeAuthor Commented:
I great experience work with Danny. He gave me a very good advice an examples on coding solutions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.