verdante
asked on
Viewing documents stored in MS Access 2010
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
thx
Verdy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?
Often this means problems with Access, or with Windows. Are you sure you're fully up to date with both?
ASKER
I'm all up to date with updates.
How did you create the control?
ASKER
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.
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.
ASKER
OK then thanks for your help. I'll see how I go contacting them, cheers Verdy
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
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
ASKER
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.Record set")
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
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.Record
strSQL = "SELECT doc_file_name, blob FROM blobtable where id = " & id.Value
rst.Open strSQL, CurrentProject.Connection,
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
ASKER
Was the right suggestion and I could have asked for more detail but this rating reflects the path the solution took.
ASKER
cheers