are you using Attachment field to save your pdf?
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
'//
'// 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 Sub
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.
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 Sub
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.
And if you end up with a lot of large PDFs in the database you could reach the 2 gigabyte limit.