Generate pdf and save it to the database (1 Viewer)
- Thread starter TomSD
- Start date
Indeed, a report can be saved as a pdf. However I'm wondering whether a report can be saved as a pdf and then stored in the access database itself without the user interfering.
It's possible to save a report as a pdf, and then manually add the pdf as an attachment in a table. However I'm trying to achieve this without the user having to do anything else buth opening a form/the report.
It's possible to save a report as a pdf, and then manually add the pdf as an attachment in a table. However I'm trying to achieve this without the user having to do anything else buth opening a form/the report.
I'm not entirely sure what you mean? Currently, I've a report with an ExportWithFormatting macro bound to the on load event. I've a table as well and that indeed has a field "Report" with data type Attachment.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 10:54
- Joined
- May 7, 2009
- Messages
- 19,243
Docmd.OutputTo acOutputReport, "yourReport", acFormatPDF, "z:\yourReport.pdf",true,,,acExportQualityPrint
dim rsParent As DAO.RecordSet2
dim rsChild As DAO.RecordSet2
Dim db As Dao.Database
Dim bm As Bookmark
set db = CurrentDB
set rsParent=db.OpenRecordset("yourTable", dbOpenDynaset)
rsParent.AddNew
![desc]="anything"
rsParent.Update
set bm = rsParent.Lastmodified
rsParent.Bookmark = bm
rsParent.Edit
Set rsChild = rsParent.Fields("yourAttachmentFieldName").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("z:\yourReport.pdf")
rsChild.Update
rsParent.Update
rsChild.Close
rsParent.Close
Set rsChild=Nothing
Set rsParent=Nothing
dim rsParent As DAO.RecordSet2
dim rsChild As DAO.RecordSet2
Dim db As Dao.Database
Dim bm As Bookmark
set db = CurrentDB
set rsParent=db.OpenRecordset("yourTable", dbOpenDynaset)
rsParent.AddNew
![desc]="anything"
rsParent.Update
set bm = rsParent.Lastmodified
rsParent.Bookmark = bm
rsParent.Edit
Set rsChild = rsParent.Fields("yourAttachmentFieldName").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("z:\yourReport.pdf")
rsChild.Update
rsParent.Update
rsChild.Close
rsParent.Close
Set rsChild=Nothing
Set rsParent=Nothing
I get a "User-defined type not defined" error on the bm declaration. Any ideas? I'm using Access 2013.
speakers_86
Registered User.
- Local time
- Yesterday, 22:54
- Joined
- May 17, 2007
- Messages
- 1,919
There is no need to store it in the database. Just create a folder in the same place as the backend and store it there. It's super easy and IMO, more transparent. You can get the backend path easily by querying a system table, I think it is the MSysObjects table.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 10:54
- Joined
- May 7, 2009
- Messages
- 19,243
here i converted it to a sub, and corrected the errors.
Code:
'//
'// for Mr.TomSD
'//
'// arnelgp
'// 19Dec2015
'//
'// Usage: subGenPDFSaveToTable "ReportName", "Path and FileName to save pdf", "table", "attachmentfield name"
'//
'// Parameters:
'// StrReportName Name of report
'// strSAveToFilePath Full path + filename of pdf to generate, ie: "z:\NewFolder\MyReport.pdf"
'// strTable Name of table which holds attachment field
'// strAttachmentFieldName Name of attachment field in the above table.
'//
Public Sub subGenPDFSaveToTable( _
ByVal strReportName As String, _
ByVal strSaveToFilePath As String, _
ByVal strTable As String, _
ByVal strAttachmentFieldName As String)
Dim rsParent As Dao.Recordset2
Dim rsChild As Dao.Recordset2
Dim db As Dao.Database
Dim bm As Variant
'// test if pdf already exists, and delete it
If Dir(strSaveToFilePath) <> "" Then Kill (strSaveToFilePath)
'// save our report as PDF
DoCmd.OutputTo acOutputReport, _
strReportName, _
acFormatPDF, _
strSaveToFilePath, True, , , _
acExportQualityPrint
Set db = CurrentDb
' open table with attachment field
Set rsParent = db.OpenRecordset(strTable, dbOpenDynaset)
With rsParent
'// add new record to the table
.AddNew
'//
'// replace the below field with actual field in your table
'//
'![Description] = "anything you like"
.Update
'// save bookmark to the new record
bm = .LastModified
'// set recordset bookmark to new record
.Bookmark = bm
'// edit this record, then we can add the attachment
.Edit
'// set recordset to our attachment field
Set rsChild = rsParent.Fields(strAttachmentFieldName).Value
With rsChild
'// add new record to attachment
.AddNew
'// save report to attachment field
.Fields("FileData").LoadFromFile (strSaveToFilePath)
.Update
.Close
End With
.Update
.Close
End With
'// release all instance of our recordset object
Set rsChild = Nothing
Set rsParent = Nothing
End SubWhile this would be easier, it's not what I'm looking for. The reports would be stored on a network drive, and the risk that someone accidentally deletes a report without anyone noticing would be quite high.
Thanks, that works like a charm!
In case this code can help someone else: note that StrReportName should be the name of the report in your Access database, and not what you'd like the name to be. Which makes sense but still took me some minutes to figure out.
And if you end up with a lot of large PDFs in the database you could reach the 2 gigabyte limit.
That's indeed important to keep in mind. However, as nearly all reports are +/- 254 KB, the risk of reaching the 2 GB limit anytime soon is quite limited. I'm planning to implement a sub that'll email and delete pdf's older than X days. In my case, I believe this might be a good solution.
speakers_86
Registered User.
- Local time
- Yesterday, 22:54
- Joined
- May 17, 2007
- Messages
- 1,919
I don't think attachments increase the size of the backend, though I couldn't explain how that is possible. I did some testing on this sometime ago, and it seemed like that was what I saw.
