How do I save jpg photos in sql server using microsoft access 2003?

[mondo3a]

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?

 

[Remou]

Something on these lines may suit:

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

strCN = "ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=MyServer\Instance;Trusted_Connection=Yes;DATABASE=test"

cn.Open strCN

strSQL = "SELECT ImageCol FROM dbo.ImageTable"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\docs\image.jpg" 'FileNameToLoadWithFullPath

rs.AddNew
rs.Fields("ImageCol").Value = mstream.Read
rs.Update

rs.Close

Or possibly a pass-through query, but I have not tried this from Access:

INSERT INTO ImageTable(ImageCol) 
SELECT * 
FROM OPENROWSET(BULK N'C:\docs\image.jpg', SINGLE_BLOB) as ImageCol

http://lessthandot.com

 

[Remou]

I should add that the data type is varbinary(max), which I understand to be the preferred type for images etc.

http://lessthandot.com

 

[mondo3a]

Thanks for the help.
I tried the code (modified a bit), but i get an error.
Here's the code:

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"  'FileNameToLoadWithFullPath
    
    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?

 

[Remou]

Any chance of trying varbinary max?

http://lessthandot.com

 

[mondo3a]

I just realized that we're using sql server standard edition (2000?) and not 2005 like I said above. discovered this when i unsuccessfully tried to change field from 8000 to varbinary(max). Is there a different field type I can/should be using?

 

[Remou]

AFAIK, there is an image data type in 2000, but you would update it in a different way. Image data type is now deprecated.

http://lessthandot.com

 

[mondo3a]

ok...I changed the field type to Image, and it seemed to save it. Hate to ask, but what would be the code to retrieve it and place it into an image box?

 

[Remou]

I did not research placing it in an image box in Access, but you can write the file to disk, whence it is easy enough to display.

Dim strSQL As String
Dim strCN As String
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim mstream As New ADODB.Stream
 
strCN = "ODBC;Description=Description;DRIVER=SQL Server;" _
& "SERVER=Server\Instance;Trusted_Connection=Yes;DATABASE=DB"
cn.Open strCN
 
strSQL = "SELECT ImageCol FROM ImageTable"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
 
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs!ImageCol
mstream.SaveToFile "c:\docs\imageout.jpg", adSaveCreateOverWrite

http://lessthandot.com

 

[mondo3a]

Thank...that seemed to work.
I appreciate all of your help!!