?
Solved

Viewing documents stored in MS Access 2010

Posted on 2013-01-30
12
Medium Priority
?
239 Views
Last Modified: 2013-02-05
I have inherited a MS Access 2010 database that has files stored in a table. The column data type is OLE object. How can I create an interface so users can view these documents?
thx
Verdy
0
Comment
Question by:verdante
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 38837498
You can sometimes use a Bound Object Frame to do this (the Bound Object Frame is a control available in Access). To do that, drop the Bound object frame on a form, and set the ControlSource of that control to y our OLE Object.

If that doesn't work, you'll have to use something like the Stream method.

What did the people you inherited this from do?
0
 

Author Comment

by:verdante
ID: 38838175
I understand the application allowed people to upload and store documents but I just have the backend. I'll try your suggestion and let you know how I go.

cheers
0
 

Author Comment

by:verdante
ID: 38839245
Hi

I've tried to create the form control and I get this error message:

"A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX control. Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again in Microsoft Access."

Does that sound resolvable? I've searched for a solution on the net but haven't come up with anything concrete.
thx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 85
ID: 38839451
How did you create the control?

Often this means problems with Access, or with Windows. Are you sure you're fully up to date with both?
0
 

Author Comment

by:verdante
ID: 38841480
I'm all up to date with updates.
0
 
LVL 85
ID: 38841750
How did you create the control?
0
 

Author Comment

by:verdante
ID: 38841823
I auto generated the form then I dragged a new Bound Object Frame control onto the form and set its control source to the column containing the documents. I should have mentioned (sorry) the error occurs when I double click on the contents of the object.
0
 
LVL 85
ID: 38841907
I should have mentioned (sorry) the error occurs when I double click on the contents of the object.
Then that generally means that you don't have the necessary programs installed on the workstation to open the OLE files, or that the data in the OLE fields is corrupted. Do you know what sort of objects are stored in the OLE files?

I'd contact the person who supplied you with the database, and ask them for a copy of the UI they were using. That would be the simplest way to determine the best way to handle those files.
0
 

Author Comment

by:verdante
ID: 38841978
OK then thanks for your help. I'll see how I go contacting them, cheers Verdy
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38857290
Yes, sadly, OLE datatypes are famous for these types of issues.

I would :
1. Make a copy of the database
2. Run the Compact/repair utility (and even create a new database and import all the objects,  and even do a decompile)
3. Then try creating a form from this table.

IIRC, simply doublecliking the OLE field in the form will open the document.

But there's the rub...
The OLE field may open the document for viewing or editing, based on the program it is associated with...

..or you may simply get the the same error...
:-(

Again, just some side info that may be of some value...
;-)

JeffCoachman
0
 

Author Comment

by:verdante
ID: 38857846
Couldn't contact ex dba. I eventually opted for the stream method.  So I will award points to LSMConsulting.  The sub looks like this:

Private Sub Extract_Click()


'Extracts specified BLOB to file from table

    Dim strSQL As String
    Dim rst As Object    'ADODB.Recordset
    Dim objStream As Object    'ADODB.Stream

    Set rst = CreateObject("ADODB.Recordset")
    strSQL = "SELECT doc_file_name, blob FROM blobtable where id = " & id.Value
    rst.Open strSQL, CurrentProject.Connection, 1, 3
    If rst.RecordCount = 0 Then
        GoTo CloseUp
    End If

    Set objStream = CreateObject("ADODB.Stream")
    With objStream
        .Type = 1    'adTypeBinary
        .Open
        .Write rst.Fields("cdoc").Value
        If Not IsNull(rst!doc_file_name) Then
            strFileName = "filepath" & rst!doc_file_name
            MsgBox ("File saved as " & strFileName)
        End If
        .SaveToFile strFileName, 2    'adSaveCreateOverWrite
    End With

    ExtractBLOB = True

CloseUp:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set objStream = Nothing

End Sub
0
 

Author Closing Comment

by:verdante
ID: 38857851
Was the right suggestion and I could have asked for more detail but this rating reflects the path the solution took.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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