Solved

Add bmp into Access OLE Object field via VB

Posted on 1998-05-08
6
359 Views
Last Modified: 2013-11-26
If provided with the path to a .bmp file (e.g. c:\abc.bmp), how can I insert this into an OLE Object field in an Access DB table via my VB application.

Next, once this has been done, how can I display that picture in the DB table field in a picture box on a form (note: at this stage I will not have the path to the picture so I can't use the LoadPicture command.) That is, load the contents of the OLE Object field into an object on the form.
0
Comment
Question by:tad
  • 3
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
chris_a earned 50 total points
ID: 1444213
Use the appendchunk method!

You can find example code on the MS site, search for Access and BLOB.

If you only have a small amount of blob data you can do it in sql update commands but this gets a little messy.

Here is a bit of the code from MS, this should give you the gist of it.

====================
From MS site
====================

Private Sub cmdSave_Click()
  FormToFile DiskFile, picBlob
  FileToColumn rsset.Fields("picbin"), DiskFile, " where id = " & rsset.Fields("id")
End Sub


Public Sub ColumnToFile(Col As ADODB.Field, DiskFile As String)
  'Retrieves data from the database and puts it into a temp file on
  'the hard drive.
  'The size of the chunk is in the variable BLOCKSIZE (4096).
   
  Dim NumBlocks As Long  'Holds the number of chunks.
  Dim LeftOver As Long   '# of chars left over after last whole chunk.
  Dim FileData() As Byte
  Dim DestFileNum As Long
  Dim i As Long
  Dim ColSize As Long
   
  'Make sure that you aren't in an empty recordset.
  If Not rsset.EOF And Not rsset.BOF Then
    ColSize = Col.ActualSize
     
    'If filelength > 0, then it is soiled: throw away contents.
    If Len(Dir$(DiskFile)) > 0 Then
      Kill DiskFile
    End If
     
    DestFileNum = FreeFile
    Open DiskFile For Binary As DestFileNum
    NumBlocks = ColSize \ BLOCKSIZE
    LeftOver = ColSize Mod BLOCKSIZE
     
    'Now Write data to the file in chunks.
    ReDim FileData(1 To BLOCKSIZE) As Byte
    For i = 1 To NumBlocks
      FileData = Col.GetChunk(BLOCKSIZE)
      Put DestFileNum, , FileData
    Next i

     
    If LeftOver > 0 Then
      ReDim FileData(1 To LeftOver) As Byte
      FileData = Col.GetChunk(LeftOver)
      Put DestFileNum, , FileData
    End If
     
    Close DestFileNum
  End If

End Sub

Sub FileToColumn(Col As ADODB.Field, DiskFile As String, whereclause As String)
 'Takes data from the temp file and saves it to the database.
 
 Dim FileData() As Byte
 
 Dim NumBlocks As Long
 Dim FileLength As Long
 Dim LeftOver As Long
 Dim SourceFile As Long
 Dim i As Long
 
 Dim hexstring As String
 Dim looper As Long

  If Not rsset.EOF And Not rsset.BOF Then
    SourceFile = FreeFile
    Open DiskFile For Binary Access Read As SourceFile
    FileLength = LOF(SourceFile)
    If FileLength = 0 Then
     Close SourceFile
     MsgBox DiskFile & " Empty or Not Found."
    Else
     NumBlocks = FileLength \ SMBLOCKSIZE
     LeftOver = FileLength Mod SMBLOCKSIZE
     
     cn.BeginTrans
     cn.Execute "update pictures set picbin = null " & whereclause, , adCmdText
     
     ReDim FileData(1 To SMBLOCKSIZE) As Byte
     
     For i = 1 To NumBlocks
      Get SourceFile, , FileData()    'convert(varbinary,
'       cn.Execute _
'        "update pictures set picbin = convert(varbinary,picbin) + 0x" & _
'        HexEncodeBin(FileData()) & " where id = 1", , adCmdText
       
       cn.Execute _
        "DECLARE @@ptrval varbinary(20) " & _
        "SELECT @@ptrval = TEXTPTR(picbin) FROM pictures " & whereclause & _
        " UPDATETEXT pictures.picbin @@ptrval null 0 " & _
        "0x" & HexEncodeBin(FileData()), , adCmdText


       Form2!Text1.Text = i & "/" & NumBlocks
       'Debug.Print HexEncodeBin(FileData())
       DoEvents
     Next i
     
     If LeftOver > 0 Then
       ReDim FileData(1 To LeftOver) As Byte
       Get SourceFile, , FileData()
  '    cn.Execute _
  '      "update pictures set picbin = convert(varbinary,picbin) + 0x" & _
  '      HexEncodeBin(FileData()) & " where id = 1", , adCmdText
         
        cn.Execute _
         "DECLARE @@ptrval varbinary(20) " & _
         "SELECT @@ptrval = TEXTPTR(picbin) FROM pictures " & whereclause & _
         " UPDATETEXT pictures.picbin @@ptrval null 0 " & _
         "0x" & HexEncodeBin(FileData()), , adCmdText
       'Debug.Print HexEncodeBin(FileData())
     End If
     cn.CommitTrans
     
'     Col.AppendChunk Null
'
'     ReDim FileData(BLOCKSIZE) As Byte
'
'     For i = 1 To NumBlocks
'      Get SourceFile, , FileData()
'      Col.AppendChunk FileData()
'     Next i
'
'     ReDim FileData(LeftOver) As Byte
'     Get SourceFile, , FileData()
'     Col.AppendChunk FileData()
'
'     rsset.Update
     
     Close SourceFile
    End If
  Else
    MsgBox "Err: No current record"
  End If

End Sub
0
 
LVL 7

Expert Comment

by:kamall
ID: 1444214
tad,
The best way to include and show images that are associated with records in a database is to use a text filed in your DB and store the path/name of your image file. You can then put the images on the user's machine in a separate directory under your application's installation directory and access them using the App.Path method. This does not require the use of the 'problematic' OLE filed in the database.
Hope that helps.
Regards
0
 

Author Comment

by:tad
ID: 1444215
chris_a:
I'm trying to find some time to try your suggestion before I grade it.

kamall:
I tried that method at first but I'm trying to achive a method of "preventing" a second user/company from using the app by changing the company logo which, at present, is stored in the OLE field and is used in Crystal Reports to print as a header on each page. It's only a 70x70 pixel bitmap. If I place the file on the machine then they could just edit it. This image is also displayed on all screens/forms - hence the question.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:kamall
ID: 1444216
tad,
This is NOT a good protection method!
Since Crystal Reports files are separate files and are editable by anyone who has Crystal Reports editor on his machine, then it is very easy for him to change the link to this header image (your company logo).
Regarding displaying the logo on all your program forms/screens, you can just embeed the picture inside your project in one main picture box, and then set the picture property of the corresponding picture boxes in other forms to get the picture form the main picture box. This is very easy and will not enlarge your EXE file size too much since the logo picture size is very samll.
0
 

Author Comment

by:tad
ID: 1444217
kamall, that would work but I haven't told you everything. The app is being used by 3 different companies - that on it's own would not be a problem and your suggestion would work fine but the one particular company is in a transformation process and is currently switching between logo's. The app must have the function to allow a user to select the appropriate logo which will be used for both the screen and the reports. (all reports that were created before a certain date, if re-printed, must have the original logo and all those printed after that date must have the new logo. This part works fine - it's the displaying on the screen that is the problem for me.)
0
 
LVL 7

Expert Comment

by:kamall
ID: 1444218
"it's the displaying on the screen that is the problem for me"... What do you mean by that tad? Is it the displaying of the logo from inside your application, i.e on your forms?
If this is what you mean, then you can easily add both logos inside your EXE and choose the one to display depending on the date.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now