Solved

access 2007 converting accdb to accde

Posted on 2012-04-12
18
785 Views
Last Modified: 2012-04-17
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.
0
Comment
Question by:centralmike
  • 8
  • 7
  • 3
18 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37837790
this seems to work

i changed this line in Function ApptoPDF()

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

with

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


saved, then do a debug >compile


then create an .accde
0
 

Author Comment

by:centralmike
ID: 37838376
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.
access-module.txt
0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37841337
Mike,

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

http://www.microsoft.com/download/en/details.aspx?id=7

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.

Danny
0
 

Author Comment

by:centralmike
ID: 37842419
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?

Thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37842559
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..
0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37842703
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.

Danny
0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37842734
Haha ... After submitting my answer I just noticed the comment capricorn added. You are right about that capricorn :-)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37842735
@Danny

did you see my post at http:#a37842559 ?
0
 

Author Comment

by:centralmike
ID: 37842936
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37843973
Mike,

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, _
                               key:=vKey
  
SetParameter_Exit:
  Exit Sub
  
SetParameter_Err:

  '
  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
  Resume
  
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))
  
GetParameter_Exit:
  ' Return the value.
  GetParameter = varPar
  Exit Function
  
GetParameter_Err:
  
  ' 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 :-)

Danny
0
 

Author Comment

by:centralmike
ID: 37844527
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.
0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37845730
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.

Danny
AD-PIPELINE.accdb
0
 

Author Comment

by:centralmike
ID: 37851270
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.
0
 

Author Comment

by:centralmike
ID: 37851294
Danny, Should the Getparamert(KEYid) point to the applicationID on the form?  That way you only get the pdf for the current record?
0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37852444
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 :-)

Danny
AD-PIPELINE.accdb
0
 

Author Comment

by:centralmike
ID: 37856138
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.
0
 
LVL 2

Accepted Solution

by:
DSTECH_ADMIN_01 earned 500 total points
ID: 37856179
Hi Mike,

I hope it brought you to a better solution. One good book is: WROX - Access 2007 VBA Programmer's Reference:
http://www.wrox.com/WileyCDA/WroxTitle/Access-2007-VBA-Programmer-s-Reference.productCd-0470047038.html

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

Hope to see you more on Experts Exchange.

Danny
0
 

Author Closing Comment

by:centralmike
ID: 37856409
I great experience work with Danny. He gave me a very good advice an examples on coding solutions.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now