Solved

Saving OLE Control contents to the Access table ole field.

Posted on 1998-12-04
4
286 Views
Last Modified: 2008-03-04
Hi everybody,

I have designed a table with an OLE field in the Access 97
database.

I wanted to save the Graphics,Video, Picture, Document in this field using VB 6.0.

On the form I am using a OLE control which holds this avi,wav, bmp etc.

Can anyone pl. guide me how to assign this ole to the access field.

Thank you.

UK.
0
Comment
Question by:umeshkhoje
  • 2
4 Comments
 

Author Comment

by:umeshkhoje
ID: 1448294
Adjusted points to 200
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1448295
I have one shred of advice, DON'T DO IT!!!!  Here is the problem with it.  It makes your database huge, and you will take a major performance hit.  I have a friend who runs a small toy store.  He was storing images of toys in his database.  With a few hundred records he maxed out on the size Access will allow databases to be.  We did find a solution for his problem, and this is my recommendation.  Store the objects as files.  Then store the file location in the database.  For better versatility, create a share on a machine and store the object files under that share (if in a Novell scenario, have a volume and directory to store the object files).  Now store the location of the object in UNC format in the database.  Then anyone who has access to that share can access the object, without having to map a network drive, etc.  You could also store other information about the object so you can determine how to open it.  You will find this solution much more managable and durable.  Trust me, you will have trouble storing the objects in the database, we have tried and failed.  If you need any more help, let me know.

Later,
Martin
0
 
LVL 1

Accepted Solution

by:
kacklehorn earned 200 total points
ID: 1448296
Don't use the ole control. Use the getchunk and appendchunk methods. Here's a sample.

Sub Command1_Click ()
   Dim db As database
   Dim ds As dynaset
   Dim td As New tabledef
   Dim fd As New field
   Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
   Const DB_LONGBINARY = 11

   On Error Resume Next
   Kill App.Path & "\PICTURE.MDB"
   On Error GoTo 0

   Set db = CreateDatabase(App.Path & "\Picture.MDB", DB_LANG_GENERAL)
   td.Name = "Pictures"
   fd.Name = "Picture"
   fd.Type = DB_LONGBINARY
   td.Fields.Append fd
   db.TableDefs.Append td
   
   Set ds = db.CreateDynaset("Pictures")

   Picture1.Picture = LoadPicture("C:\WINDOWS\WINLOGO.BMP")
   ds.AddNew
   Call CopyPicToField(Picture1, ds("Picture"))
   ds.Update
   ds.Bookmark = ds.LastModified
   Call CopyFieldToPic(Picture2, ds("Picture"))

   ds.Close
   db.Close

End Sub

Sub CopyFieldToPic (Pic As PictureBox, fdObject As Field)
   Dim sChunkHolder As String
   Dim lChunkCount As Long
   Dim lChunkRemainder As Long
   Dim i As Long
   Dim iFile As Integer
   Const CHUNK_SIZE = 32000

   On Error Resume Next
   Kill App.Path & "\PICTURE.TMP"
   On Error GoTo 0
   iFile = FreeFile
   Open App.Path & "\PICTURE.TMP" For Binary As iFile
   
   lChunkCount = fdObject.FieldSize() \ CHUNK_SIZE
   lChunkRemainder = fdObject.FieldSize() Mod CHUNK_SIZE
   
   For i = 0 To lChunkCount - 1
      sChunkHolder = fdObject.GetChunk(i * CHUNK_SIZE, CHUNK_SIZE)
      Put iFile, , sChunkHolder
   Next
   If lChunkRemainder > 0 Then
      sChunkHolder = fdObject.GetChunk(lChunkCount * CHUNK_SIZE,
 lChunkRemainder) 'Get remaining data
      Put iFile, , sChunkHolder
   End If
   
   Close iFile
   Pic.Picture = LoadPicture(App.Path & "\PICTURE.TMP")
   On Error Resume Next
   Kill App.Path & "\PICTURE.TMP"
   On Error GoTo 0

End Sub

Sub CopyPicToField (Pic As PictureBox, fdObject As Field)
   
   Dim sChunkHolder As String
   Dim lChunkCount As Long
   Dim lChunkRemainder As Long
   Dim i As Long
   Dim iFile As Integer
   Const CHUNK_SIZE = 32000
   
   On Error Resume Next
   Kill App.Path & "\PICTURE.TMP"
   On Error GoTo 0
   SavePicture Pic.Picture, App.Path & "\PICTURE.TMP"
   iFile = FreeFile
   Open App.Path & "\PICTURE.TMP" For Binary As iFile
   
   sChunkHolder = Space$(CHUNK_SIZE)
   lChunkCount = LOF(iFile) \ CHUNK_SIZE
   lChunkRemainder = LOF(iFile) Mod CHUNK_SIZE
   
   For i = 1 To lChunkCount
      Get iFile, , sChunkHolder
      fdObject.AppendChunk (sChunkHolder)
   Next
   If lChunkRemainder > 0 Then
      sChunkHolder = Space$(lChunkRemainder)
      Get iFile, , sChunkHolder
      fdObject.AppendChunk (sChunkHolder)
   End If
   
   Close iFile
   On Error Resume Next
   Kill App.Path & "\PICTURE.TMP"
   On Error GoTo 0

End Sub
=============================================================================

0
 

Author Comment

by:umeshkhoje
ID: 1448297
Hi Martin,

Thanks for your valuable advice. I gave a similar advice to the person who has designed the database but he was not ready to
believe it. My database size may go to thousands of records. But the number of tables are very few.
I can provide your answer as a support to my statement. This is really helping me. Thanks once again.

Umesh
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

679 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