Link to home
Create AccountLog in
Avatar of CountryGirlMD
CountryGirlMDFlag for United States of America

asked on

Saving files jpg & pdf to SQL 2008 via Access 2007

I have an Access 2007 front end with the data stored in SQL 2008

I have an image field used to store files – primarily jpg & pdf - and yes I know all the arguments for saving for saving links vs files but for this project the customer wants the files in the DB

Neither the customer or I like the built in interface of right click / insert object that access provides

I want to create my own interface for saving the file to the database where the client clicks a button that takes him to a file dialog box, he selects the file and it is saved to the current record.

I’ve done some searching on EE & Microsoft and found the following solutions but have been unable to get them to work.  

http://support.microsoft.com/?kbid=258038

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22661404.html?sfQueryTermInfo=1+10+2005+binari+file+import+server+sql

The table & fields are bound to an access form, if I use the right click/insert image option I can save & view the image.

The following is the code my code based on the examples above, there are no errors when it ‘runs’
But when I run the “SaveFileToField” the image field on the form does not show the image – the message boxes in my code do indicate that something the size of the file selected was saved but if I double click on the image field I get this error message:
A problem occurred while CAMS was communicating with the OLE server or Active X Control
Close the OLE server and restart it outside of CAMS. Then try the original operation again in CAMS.
 (CAMS is the name of the app)

When I run the “SaveFieldToFile” the file is created on the c: drive with the appropriate files size but I can’t view it

Any insight in why this is not working or an alternate way to do this????

Option Compare Database
Option Explicit

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim mstream As ADODB.Stream
    Dim cmd1 As ADODB.Command
    Dim strConnectionString As String
   
   Const BLOCKSIZE As Long = 4096
   
Private Sub Form_Load()
    strConnectionString = "Provider='SQLNCLI10'; Data Source='CEGSQLD-OMF-22'; Initial Catalog='Contractor_Assessment'; User Id='CAMS_User'; Password='xxxxxx'"
End Sub

Private Sub Command13_Click()
    Dim strFileName As String
    strFileName = fGetFile2      'calls office file dialog function and returns selected file with full path
    Call SaveFileToField("RatingPicture", strFileName, strConnectionString)
End Sub

Private Sub SaveFileToField(strFieldName As String, strFileName As String, strConnectionString As String)
    Set cn = New ADODB.Connection
    cn.Open strConnectionString
    Set rs = New ADODB.Recordset
    rs.Open "SELECT RatingsPics.* FROM RatingsPics WHERE [RatingsPics].[PicID]=" & Me.PicID & ";", cn, adOpenKeyset, adLockOptimistic
   
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open

    mstream.LoadFromFile strFileName
    MsgBox "Size " & mstream.Size
    rs.Fields(strFieldName).Value = mstream.Read
    rs.Update
    MsgBox rs.Fields(strFieldName).ActualSize
   
    mstream.Close
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub


Private Sub Command15_Click()
    Dim strFileName As String
    strFileName = "C:\Data\pictest\2.jpg"

    Call SaveFieldToFile("RatingPicture", strFileName, strConnectionString)
End Sub

Public Sub SaveFieldToFile(FieldName As String, sFileName As String, ConnectionString As String)
    Set cn = New ADODB.Connection
    cn.Open ConnectionString

    Set rs = New ADODB.Recordset
    rs.Open "SELECT RatingPicture FROM RatingsPics WHERE [RatingsPics].[PicID]= " & Me.PicID & “;", cn, adOpenKeyset, adLockOptimistic
   
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open

    mstream.Write rs.Fields(FieldName).Value
    mstream.SaveToFile sFileName, adSaveCreateOverwrite
   
    mstream.Close
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of CountryGirlMD

ASKER

we are using the accdb format for the front end but the data is in SQL 2008 not Access - there is no corresponding data type in SQL 2008

There is something simular that's new to SQL 2008 called filestream that is a simular concept - but it's not currently enabled on our SQL 2008 servers - and due to corporate policy / redtape I'll have to jump through major hoops to get it enabled and I have a deadline
<we are using the accdb format for the front end but the data is in SQL 2008 not Access - there is no corresponding data type in SQL 2008
>
oops, you are correct, I did read that, ...but I got excited about the Attachment option...
;-)

Sorry about that...

;-)

Jeff
Update:
Enabling the SQL server file stream option was vetoed by our DBA team.  
The decision was made to move just the picture data to a separate access database using the attachment data type, and as mentioned by Jeff the interface with the attachment data type is clean & easy to use.
oK

Glad I could help.

;-)