Reducing OLE Linking Overhead

MS-Access Tips for Serious Users

Provided by Paul Murray

Reducing OLE Linking Overhead

This tip concerns the situation where you have a number of files which you wish to display in an ole frame in your access app. The problem is that even with linked ole objects the space overhead is considerable, in the order of kilobytes. This is unnacceptable in applications where you may have hundereds or thousands of OLE objects stored in a table eg: a sound or clipart library.

The solution is simple: store only the filenames in question. To display them, insert the filename into the SourceDoc property of an unbound OLE control on a form.

For example:

• create a table of filenames
• populate it with the filenames in question. (see help on the Dir() function)
• create a form based on the table
• give it a header and footer
• pull the filename into the form detail section

You now have a simple form displaying the list of filenames in the filename table.

• create an unbound object frame on the form in the footer area. At this point, a dialog box pops up.
• select 'create from file'
• check the 'link' checkbox (ie - turn it on)
• type in the filename or use browse to enter one of the files in question
• hit ok

The purpose of this step is to ensure that the ole properties of the control get set correctly - the OLE class, the verb etc.

• clear the 'source doc' property of the control you have just created (so that it doesn't load that particular file every time the form loads)
• rename the control to something meaningful (I will use 'Pic')
• set 'Enabled' to true, & 'locked' to false

Now you will need to put in the code that sets the SourceDoc of the picture. Open the code window of the form (View:Code...) and enter this subroutine:

   Sub LinkPic ()
      On Error GoTo LinkPic_Err
      Me!Pic.SourceDoc = Me!FileName
      Me!Pic.Action = OLE_CREATE_LINK
      Exit Sub

   LinkPic_Err:
      MsgBox Error$
      Exit Sub
   End Sub

This is the code that does the real work. You will want to include a call to this subroutine in Form_Current and Filename_AfterUpdate.

Problems:

I suspect that Access does some caching on linked OLE fields in tables, which is why the overhead is so high. This method involves no overhead, but is slow - every time you change records the disk spins.

Suggestions:

• Rather than have the picture come up automatically each time you change records, have a 'view' button which when pressed loads the image by calling LinkPic
• keep a cache table of current images.

To do this, the picture on the form should actually be a subform based on a table 'CurrentPics:(filename:string;Pic:Ole)'. The subform should have two controls, an embedded object (pic) linked to CurrentPics.Pic, and a command button named 'Load'. The subform should have no record selectors, navigation buttons or scroll bars, and should open in 'single form' view.

Embed this subform in the main form and link it in the 'filename' field. In the oncurrent propery, if the filename is not null then disable or if me.parent!filename is null then hide the 'load' button. This is to cover the case where the current filename on the main form has already been loaded into our cache table, or when the parent form is at the new record.

Otherwise, when the Load button is pressed, load the pic in me.parent!filename into the bound ole control. This will be the same as the example above, except that you will need to enter the OLE Class for the object - 'PBrush' for a bitmap. See the registration info editor (regedit.exe) for a list of classes on your machine. If you choose not to make the picture editable, then it may further increase speed to have this object embedded rather than linked, like so:

Sub Load_Click ()
    Me!pic.Class = "PBrush"
    Me!pic.SourceDoc = Me.Parent!Filename
    Me!pic.Action = OLE_CREATE_EMBED
End Sub

You will not need to put the filename in the table, as this is done automatically by having the filename field linked to the parent form.

Problems:

If you use this method, Be sure to 'delete * from currentpics' when you close the main form. This will be nessesary to stop the DB bloating over time.

This tip was supplied, by Paul Murray, 21 June 1995.

Who is Paul Murray? (blatant plug). I have been working in MS Access for nearly 3 years, and have been a participant on the comp.databases.ms-access newsgroup. I also work in word/excel, and have 10 years experience in C, as well as C++/windows. I am looking for a full-time position, preferably in Canberra, Australia, but I would be willing to relocate (Brisbane would be nice). To contact me:

phone: [015] 268-960
smail: JT Software, PO Box 169, Belconnen, ACT, 2616, Australia