Making a Runtime Database

[thudson]

I have created a database that I want to make a Runtime version from.

I have gone to the Save and Publish and clicked on Make ACCDE file but when I save as it reports:
That it is unable to create an accde, .mde or ade file.
Looking at the help it suggests there might be too many tables.
I have only 4 tables, but it also suggests that VBA modules create tables of which I have several modules. However I can't have 2048 tables in the database.
How can overcome this problem?

 

[pbaldy]

The "usual suspect" is code that won't compile. In the VBA editor, click on Debug/Compile. If an error is found, fix it and repeat until no errors are found.

 

[thudson]

Your spot on! Have 64 bit PC and forgot to add Ptr onto all of the long number types.

I really want to package the database and according to the web under Save & Publish there should be an Icon for package and distribute, but it is missing on my copy. There is a button Package and Sign, but all that does is ask which certificate to use and then saves it as an accdc. I have used the make ACCDE, the only other buttons are Backup and Sharepoint.
Is this only available on certain versions? I have the Enterprise version.

 

[pbaldy]

I haven't used that myself. I have Access 2010 on this PC, and I have the same 4 options you describe.

 

[thudson]

I found that by default the wizards ar not installed.
Had to go to installed Programs, Office 2010 and chooose Change.
Then in the Add-Ins tick against load onto local drive.

Have another problem and I am not sure this is the place to air it!

Having packaged the Access Database and installed onto my PC to test it, I found that the report that was displayed in Print Preview Mode, when I tryed to print it, the list of verses which was chosen in the form previous to the report started to print out 24 pages of all the filtered verses, as shown on the form.
If I went back and run the version that I packaged it printed the report only.
Is this a bug in the packager code or is it that it corrupts the database when it compiles it for packaging?

Has anybody come across this before?

 

[pbaldy]

I haven't used the package wizard, but I can't imagine why it would do that.

 

[thudson]

Hi Paul or fellow Access users

I have just experimented with the working copy (suffixed: accdb) and made a copy and changed it to a Runtime version by changing the suffix to accdr.

Before the change it worked OK, but when I changed it to Runtime version it printed the form instead of the report.

My conclusion is that the Runtime software is causing the problem.

Has anybody else experienced this problem?

 

[pbaldy]

95% of my users only have the runtime version of Access, and I've not had this problem. What exactly is your code?

 

[thudson]

I was editing the post, but took a long time. I carried out further tests as you will see below.

Tried to zip the files but each one is 2.3Mb, so can't send via forum.
Is there any other way of sending them to you?

Hi Paul or fellow Access users

I have just experimented with the working copy (suffixed: accdb) and made a copy and changed it to a Runtime version by changing the suffix to accdr.

Before the change it worked OK, but when I changed it to Runtime version it printed the form instead of the report.

I then renamed the database back to a accdb type, but hide the navigation pane and the ribbon. Again it did not print the report?

I thought perhaps it was damaged some way by opening it as a runtime DB, so I made another copy and hid the nav pane and ribbon again it did not print correctly.

Next I just made a copy and tried to use the form. I then found that not only was the DB not printing correctly, but I was not able to use the combo boxes.

Found this was due to Macros being disabled, so though is this the reason, but alas no it still printed the form and not the report!!

Can't understand what is going on. Why should just copying the database cause this problem?

I want to publish this database and want to preserve my interlectual rights.
I'm really annoyed as I have spent hours developing this DB and am now stumped at the last hurdle!

Has anybody else experienced this problem?

 

[sneuberg]

Hi again,

If are trying to use that database where you modified the reports in design view I don't think that will run with runtime Access. I believe if the code does that sort of modification you need full Access. I'm not 100% certain about that. You could take out the code that does the modifying which I believe was only in the following subroutine

Public Sub ChangeFontName(strReportName As String, strFontName As String, strFontSize As Long, strForeColor As Long)

DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
Reports.Item(strReportName)![Text1].FontName = strFontName
Reports.Item(strReportName)![Text1].FontSize = strFontSize
Reports.Item(strReportName)![Text1].ForeColor = strForeColor
DoCmd.Close acReport, strReportName, acSaveYes

End Sub

And see if that makes any difference.

 

[thudson]

Hi Sneuberg

I could not find where the code was called, so I just commented out the sub routine. I ran the code as accdb and it worked as normal. (Font size and color changed as expected). The report printed correctly.
I then changed the file extension to accdr. I then ran the test and it did not print the report but tried to print the form.

Upon further reasearch I found that the code you are refering to is actually called by the change font color button.

There is also code on a event Open in the report, which sets the position of the textbox, so I removed this event from the properties.

Private Sub Report_Open(Cancel As Integer)


'Debug.Print "In Land Report " & Me.Filter
  Me.FilterOn = True
  Me.Text1.Height = Forms.Item("Frm_Verses_Mstr")![Ht_Sz] * 56.6929133856
  Me.Text1.Width = Forms.Item("Frm_Verses_Mstr")![Wth_Sz] * 56.6929133856
  Me.Text1.Left = Forms.Item("Frm_Verses_Mstr")![Lft_Posn] * 56.6929133856
  Me.Text1.Top = Forms.Item("Frm_Verses_Mstr")![txt_Top] * 56.6929133856
  
End Sub

Next I removed the ChangeFont code completely and tryed it, firstly as a accdb, still printed out OK, then I changed the file to accdr, this did not print the report, but the form.

What ever is causing this is only triggered when it is run as a Runtime file.

Any further thoughts?

 

[sneuberg]

I tested my theory on the version of this I have and it seems my theory is wrong. It's working fine with the runtime file extension of accdr. So whatever the problem is, it's not that your code is modifying the report in design view. Sorry about running you down that dead end.

You must have added a lot to this since I was working with you on it as my version is about 1.14 MB and it zips to a size of 172 KB. I suggest doing a compact and repair on your current version. That might bring its size down so that you could upload it.

 

[sneuberg]

On the other hand unfortunately my theory seems to be true for accde files. When I converted my version to a accde I get the error, "The command you specified is not available in an .mde, .mde, .accde, or .ade database" I put an error handler in the subroutine that does the report modifications and this error is being raised in that subroutine.

This is not good as it doesn't look like you will be able to offer these features in a compiled version.

I wonder if this would be any different if you used Crystal Reports

 

[thudson]

Hi Sneuberg

Fistly, The best I can do with Zipping the files is 2.14Mb, and thats also splitting the database with that database splitter.

I have heard of Crystal Reports but never used it.

Have you any tutorial information?

How will that help?

If you want a copy of the database so far can I email it to you?

I await your reply.

 

[sneuberg]

I have heard of Crystal Reports but never used it.

Have you any tutorial information?

No. Suggest googling "MS Access and Crystal Reports"

How will that help?

I don't know if it will. I'm hoping some other forum member can comment on that.

If you want a copy of the database so far can I email it to you?

No. There's probably no point to that as I don't know of anyway around this last problem.

 

[thudson]

Thanks Sneuberg!

All that work you did and looks like I can't finish the problem in Access.

Do you think that some of the code would work in Visual Basic, with some corrections?

 

[sneuberg]

Do you think that some of the code would work in Visual Basic, with some corrections?

More than corrections, basically a rewrite.

I was thinking about what I would do if this were my project and I think I'd try creating a PDF in code. My choice would be C# and when I googled that I found http://csharp.net-informations.com/file/create-pdf.htm and I saw there's a library for it. Since Visual Basic (Not VBA), like C#, is a dot net thingy I suspect this library is available in Visual Basic. But all you'd have from what you have done so far is the Access tables.

I feel really bad about this and wish I would have thought about and warned you about this problem before you got this far.

 

[thudson]

I am really disappointed that there is no solution after spending hours of work and that of Sneuberg as well.

Could some of the code be used in a Visual Basic solution?
How can you produce reports in VB?
Or could the text be output as a PDF?

Any thoughts on these ideas?

 

[thudson]

I have though of a way to still use the database, but it's a tedious way around the problem, which I discounted when I started the database.

I could have a report set up for each of the card sizes!
The only disadvantage is that the user would not be able to alter the Top position, which I introduced in the final version.

Any ideas how I could choose the report from the combo box.

 

[sneuberg]

The sizes and positions are adjusting in the .accde version ok. That's not the problem. The problem is the modifications that were done in design view, i.e., the font name, size, and forecolor. If you could decide on a limited number of these choices then you could do it with reports (landscape & portrait) for each combination of font characteristics. You could make a table of these with report name and the title that would be displayed in the combo box. Then use that table as a row source for the combo box. The DoCmd.OpenReport would get the report name from the combo box, some like cboReports.Column(1).

You could use what you have now to manufacture these reports. Just run the report with the font characteristics you want, switch to design view, save the report, and then make a copy of it with a name that reflects its state. And just repeat this process for all of the reports you want.