Attach PDF to Form

[azalea]

Please Help with Attaching a PDF to Form

Dear All,

I am still very new to any type of programming. The company I work is in education and I have been tasked with setting up a certificate request database.

I have an Access 2032 FE & SQL 2012 BE. I need to store PDF documents from all our branches.

I have been working on this for the past 3 working days and I have seen a great deal about saving the file path to the database vs saving the actual document in the DB. The documents would be located on each users computer.

I have a Form with all the information that is required by the certification department. On the form is a button (Called InsertID(ID in this case is the persons Identity Docuement)) and the button is linked to a table called dbo_CertificationSupportingDocuments. The table is referenced to the Main Certificate Request table.

The dbo_CertificationSupportingDocuments table is structured as follows:

	[CertSupportingDocID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CertificateRequestNumber] [numeric](18, 0) NOT NULL,
	[RequestBlob] [varbinary](max) NULL,
	[RequestFileName] [nvarchar](50) NULL,
	[RequestFileExtension] [nchar](10) NULL,
	[RequestFileMimeType] [nvarchar](50) NULL

When I click the button I get a Run Time error 3075 "Syntax error (Missing operator) in query operator.

The code I am using is:

Dim fd As FileDialog
Dim strFileWPath As String
Dim FileNoExt As String
Dim FileExt As String
Dim StrSQL As String
Dim db As Database
Dim CertFileName As String
Dim MFile As String


DoCmd.SetWarnings (WarningsOff)
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Please Select the ID"
fd.Filters.Clear
fd.Filters.Add "Scan files", "*.pdf"


If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        'CertFileName = fd.SelectedItems(1)
        Me.FileName = fd.SelectedItems.Item(1)
    End If
Else
    'Exit code if no file is selected
    End
End If
    
MFile = fd.SelectedItems(1)

CertFileName = Mid$(Me.FileName, InStrRev(Me.FileName, "\") + 1)
FileNoExt = Left$(CertFileName, InStrRev(CertFileName, ".") - 1)
FileExt = Right(Me.FileName, Len(Me.FileName) - InStrRev(Me.FileName, "."))

StrSQL = "INSERT INTO [dbo_CertificationSupportingDocuments]" & _
           "([CertificateRequestNumber],[RequestBlob],[RequestFileName],[RequestFileExtension],[RequestFileMimeType])  VALUES " & _
           "('" & (Me.RequestID) & "'," & MFile & ",'" & FileNoExt & "','" & FileExt & "', 'application/pdf')"

StrSQL = StrSQL

Debug.Print StrSQL
DoCmd.RunSQL (StrSQL)

I have been looking at this code for so long that I cannot see what is wrong. Please Please help

Regards
Azalea:banghead:

 

[Ranman256]

I dont see the need for all this code.
Have a field with Text 255. (if you need more use MEMO)
Then run an append query that posts the ID, docPath from a form.
docmd.openquery "qaApdDoc"

1 line of code.

 

[arnelgp]

sorry i am not using ms sql, maybe this link will help you:
http://www.experts-exchange.com/Database/MS_Access/Q_26923910.html

 

[Ranman256]

This is not ms Sql code, this is access code.

 

[GinaWhipp]

Before running the code, open the Module then open the Immediate Window (Ctrl +G). Run the code then copy/paste it here.

 

[azalea]

Before running the code, open the Module then open the Immediate Window (Ctrl +G). Run the code then copy/paste it here.

Thanks so much for the help.

I am definitely moving in the right direction. I have used the code pretty much as was given. Now I keep getting a 3265 Run time error (Item cannot be found in the colection corresponding to the requested name or ordinal.). The piece of code that I get it on is

 rs!Description = strDescription

. The full code is

Sub AddFile(strDescription As String, strFilename As String)
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim objStream As New ADODB.Stream
    
    Debug.Print strDescription
    Debug.Print strFilename
    
    'create the stream
    objStream.Type = adTypeBinary
    objStream.Open
    objStream.LoadFromFile strFilename
    
    con.Open "DRIVER=SQL Server;SERVER=septu;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=TEST_Order;LANGUAGE=British;TABLE=dbo.CertificationSupportingDocuments"
    rs.Open "Select * from CertificationSupportingDocuments", con, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs!Description = strDescription
    rs!FS = objStream.Read
    rs.Update
    rs.Close
    objStream.Close
    con.Close
End Sub

.

I am using CertFileName, CertFile as the Parameters. The Print Debug is showing the correct data, namely the file that is to be uploaded and its full path.

I am not sure why I keep getting the error?

Please help. i am really pulling my hair out with this (and I don't have a lot of hair to begin with)

 

[arnelgp]

This is not ms Sql code, this is access code.

have you visited the link, they have the same problem.

 

[GinaWhipp]

Is *Description* a field in your Recordset?

 

[azalea]

Is *Description* a field in your Recordset?

I think that I am getting there. I have created a blank form with a button, and a test table in my DB called BLOBTest. The table has 3 fields:
TestID
BLOBName
BLOBData

I then set the On Click event runs the following code

Private Sub Command0_Click()
Dim str1 As String
Dim str2 As String

str1 = "Stock Order 23.pdf"
str2 = "C:\testdoc\Stock Order 23.pdf"


AddFile str1, str2


End Sub

Sub AddFile(strDescription As String, strFilename As String)
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim objStream As New ADODB.Stream
    
    Debug.Print strDescription
    Debug.Print strFilename
    
    'create the stream
    objStream.Type = adTypeBinary
    objStream.Open
    objStream.LoadFromFile strFilename
    
    con.Open "DRIVER=SQL Server;SERVER=septu;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=TEST_Order;LANGUAGE=British;TABLE=dbo.BLOBTest"
    rs.Open "Select * from BLOBTest", con, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs!BLOBData = strDescription
    rs!FS = objStream.Read
    rs.Update
    rs.Close
    objStream.Close
    con.Close
End Sub

When i run the code i get a the following run time error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." The error is on

rs!BLOBData = strDescription

I suspect that the error relates to the PDF file. I have tried all sorts of different solutions but don't seem to solve it.

I kinda feel like that kid in school who asks the teacher for each line of a worksheet. I am soooo new to any type of prgramming

Thanks
Aza:banghead:

 

[GinaWhipp]

Okay, you keep changing things which is making it difficult to troubleshoot and now you've changed to ADO. So, in sticking with the original code... is Description a part of the Recordsource?

 

[azalea]

The table that the recordsource calls has the following fields:
CertSupportingDocID
CertificateRequestNumber
RequestBlob
RequestFileName
RequestFileExtension
RequestFileMimeType

There is no "Description". Am I correct in assuming that RecordSet uses the table manes that it is being referenced?

Thanks
Aza

 

[GinaWhipp]

That is correct and that is why it errors out because there is no field named Description. Fix that and part one will be resolved.

 

[azalea]

I had changed the Description to
RS!RequestBlob = strDescription

I noticed that when I put the cursor over the statement in debug the following appeared:
RS!RequestBlob = strDescription = False (Not sure of the relevance)

I am still getting "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." error

 

[GinaWhipp]

What is the Data Type of RequestBlob?

 

[azalea]

RequestBlob is varbinary(max) on the SQL server. It is the field to store the PDF documents.

 

[GinaWhipp]

You are just trying to store the documents in this field or print them? If you are trying to upload, have you confirmed that you actually can place the document in the field?

Side note, would be much better to copy the file to a shared location on the Server.

 

[azalea]

The documents will primarily be stored, they will be viewed by student records department.

The reason that we have to store them in the database is due to the very strict privacy & personal information laws in South Africa. Most of the documents are confidential and contain information that could contravene the law.

 

[GinaWhipp]

Okay, well have you actually gotten a document in that field? I have never tried so that should be confirmed that you can actually do that.

And I don't see how just putting them in that field would make them *safe*. If you have access to the SQL Server you have access to the document.

 

[azalea]

Our document sever has a lot more users, and the documents would be more else available to anyone. The SQL server has more security on the database, according to legal that's what's required.

I have solved it. It was a stupid error, i was trying to load the document into the wrong field.

I can slap my self for making this kind of error

Thanks to all for the help, now comes the fun part making the form visible to student records.

 

[GinaWhipp]

Live and learn, good thing you found it because I was running out of things to try!