How do I save jpg photos in SQL Server 2005 using access 2003 as front end?

[mondo3]

I'm using access 2003 as the front end, linked to SQL Server 2005 for the back end. I would like to store scanned photos (jpg's); I do not want to just store the path name in the record, as the scanned photos are confidential (I dont want residing in any network folder). Has anyone got a code sample of this?

 

[mondo3]

I tried this code but i get an error:

Dim strSQL As String
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim mstream As ADODB.Stream

Set cn = CurrentProject.Connection
strSQL = "SELECT testid, photo, notes FROM tbltest where testid=1"

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseServer
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

If rs.EOF = False Then    
   Set mstream = New ADODB.Stream    
   mstream.Type = adTypeBinary    
   mstream.Open    
   mstream.LoadFromFile "c:\photo2.jpg"   

   rs.Fields("photo").Value = mstream.Read    
   rs.Fields("Notes").Value = "Test"        
   rs.Update
Else    
   MsgBox "No records"
End If
rs.Close

If I remove the line:

rs.Fields("photo").Value = mstream.Read

the code runs ok, but when it's in place, i get an error:
odbc -call failed.
I have set the "photo" field in sql server as varbinary 8000.

any ideas?