Solved

Saving OLE Control contents to the Access table ole field.

Posted on 1998-12-04
4
283 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 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

10 Experts available now in Live!

Get 1:1 Help Now