Save and Retrieve From Access Backend as BLOB

[Pac-Man]

Hello,

I want to use the following code taken from this website (which is for SQL Server) for my access database which have access backend. How can I do so? What change do I have to make in the code. The code is to save an image file as BLOB and then retrieve the image from BLOB. Code is as below.

TIA

Best Regards,
Abdullah

'To save a file in a table as binary
Sub SaveAsBinary()

    Dim adoStream               As Object
    Dim adoCmd                  As Object
    Dim strFilePath             As String
    Dim adoCon                  As Object
    Const strDB                 As String =  ""  'Database name
    Const strServerName         As String =  ""  'Server Name
   
    Set adoCon = CreateObject( "ADODB.Connection")
    Set adoStream = CreateObject( "ADODB.Stream")
    Set adoCmd = CreateObject( "ADODB.Command")
   
    '--Open Connection to SQL server
    adoCon.CursorLocation = adUseClient
    adoCon.Open  "Provider=SQLOLEDB;Data Source=" & strServerName &  ";Initial Catalog = " & strDB &  ";Integrated Security=SSPI;"
    '----
   
    strFilePath =  "C:\1.JPG" ' File to upload
   
    adoStream.Type = adTypeBinary
    adoStream.Open
    adoStream.LoadFromFile strFilePath  'It fails if file is open
       
    With adoCmd
        .CommandText =  "INSERT INTO Employee VALUES (?,?)" ' Query
        .CommandType = adCmdText
       
        '---adding parameters
        .Parameters.Append .CreateParameter( "@Id",adInteger,adParamInput,0,1)
        .Parameters.Append .CreateParameter( "@Image",adVarBinary,adParamInput,adoStream.Size,adoStream.Read)
        '---
    End With
   
    adoCmd.ActiveConnection = adoCon
    adoCmd.Execute
       
    adoCon.Close
   
End Sub

'To read binary stream from DB and save the same on system drive
Sub ReadBinary()
   
     Dim adoRs                  As Object
     Dim adoStream              As Object
     Dim adoCon                 As Object
     Const strDB                As String =  ""  'Database name
     Const strServerName        As String =  ""  'Server Name
     
   
     Set adoCon = CreateObject( "ADODB.Connection")
     Set adoRs = CreateObject( "ADODB.Recordset")
     Set adoStream = CreateObject( "ADODB.Stream")
       
     '--Open Connection to SQL server
     adoCon.CursorLocation = adUseClient
     adoCon.Open  "Provider=SQLOLEDB;Data Source=" & strServerName &  ";Initial Catalog = " & strDB &  ";Integrated Security=SSPI;"
     '--
   
     adoRs.Open  "SELECT ID,Image FROM Employee WHERE ID = 1",adoCon,adOpenStatic,adLockOptimistic
       
     adoStream.Type = adTypeBinary
     adoStream.Open
     adoStream.Write adoRs.Fields( "Image").Value  ' FieldName that contains binary data
   
     adoStream.SaveToFile  "C:\" & adoRs.Fields( "ID").Value &  ".jpg",adSaveCreateOverWrite
   
     adoRs.Close
     adoCon.Close

End Sub

 

[arnelgp]

use "external reference", meaning save the Location of the blob to your field.
you can also use attachment datatype, but will rapidly increase your db size.

 

[Pac-Man]

use "external reference", meaning save the Location of the blob to your field.
you can also use attachment datatype, but will rapidly increase your db size.

Thanks for reply @arnelgp, can you please tell which part of the code do I have to change and change to what value.

Thanks again.
Best Regards

 

[kentgorrell]

arenpg is correct. If you can, keep the file external to the db and only store the file name (or full path) in the table.
But you will need to save the file to a location that all users can access.
storing the image as an attachment in the db bloats the db
and the attachment data type is not compatible with SQL Server and visa v.

The Access image control is a wonderful thing. you simply set its control source to the field that holds the path\filename to display the image. you can even use code to tell the image control where the file is. since 2007 it works really well with continuous forms.

The Accesss attachment data type is great for storing a few often used images, like the company logo, but not for hundreds of images associated with records.

The only argument for using BLOBs or attachments is portability. ie if you want to move the db to another network, you don't need to copy the files as well. but the cost is db bloat.

 

[Mike Krailo]

I don't know why there is always this desire to bash attatchmet fields. If the intended use is for png images that are only 30K in size and you only expect 2K records or so, the size of the database will not really be that large. It's a design decision based on needs and how it will be used. Now if there are a huge amount of images expected to be used, then of course that would make it impractical for its use. There are many DB's that will never have the need to move to sql server.

 

[kentgorrell]

@Jike Krailo "There are many DB's that will never have the need to move to sql server."
very true, I was attempting to provide Pros as well as Cons

but will admit I'm a bit biases against attachments, or any other non conforming data type, because I spend much of my time migrating the databases that do need to move to SQL Server. And non conforming data types make this process so much harder than it needs to be.