IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Paul Murray: email@example.com
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.
You now have a simple form displaying the list of filenames in the filename table.
The purpose of this step is to ensure that the ole properties of the control get set correctly - the OLE class, the verb etc.
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.
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.
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.
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:  268-960
smail: JT Software, PO Box 169, Belconnen, ACT, 2616, Australia
Have your say - comment on this article.
What did you think of 'Reducing OLE Linking Overhead'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...