Display field (Long Binary Data) in a linked form (1 Viewer)

emillerd · Jul 29, 2019

I have attached a .zip with 2 databases. Millco.mdb is the source database and OST Updater.mdb is the database I have created with a link to the source.
What I am trying to accomplish is to display the Notes field from the source in my linked database form.

I am able to display the other fields fine but the Notes field in the source "Bids" table is listed as Long Binary Data. I can not figure out how to get this to display the data.

Record 40 (UID 162) should read Avoca CSD in the Notes field.

I do not want to modify the Millco.mdb as this is a database created by a commercial program that I assume requires the data stay as it is.

Any ideas on how I can get the field to display?

Thanks for any suggestions!

isladogs · Jul 29, 2019

Normally a Notes field would be text or memo (long text).
Yours is the little used OLE Object datatype which isn't appropriate
This link may help you understand its purpose https://www.brainbell.com/tutorials/ms-office/Access_2003/Using_OLE_Object_Fields.htm

Unfortunately converting the field to text/memo produces unreadable characters such as those seen when data is corrupted.
Another possible solution would have been to create a query and use CStr(Notes) as a query field. However that has the same outcome.
I've never used OLE Object so never need to do this conversion. Perhaps this very old link will help https://www.tek-tips.com/viewthread.cfm?qid=136601
Also look at the similar threads at the bottom of this page

emillerd · Jul 29, 2019

Thank you.
I tried to switch the data type to text and memo but the application that created the source database crashed when I tried to open it. Must be it looks there for an OLE object which is odd since it is the only field in the table that is OLE.
It's not a deal breaker if I can't get it to work but it would make things a little easier.

isladogs · Jul 29, 2019

Did you try doing CStr(Notes) as a query field? Were any of the other links useful?

theDBguy · Jul 29, 2019

emillerd said:
Thank you.
I tried to switch the data type to text and memo but the application that created the source database crashed when I tried to open it. Must be it looks there for an OLE object which is odd since it is the only field in the table that is OLE.
It's not a deal breaker if I can't get it to work but it would make things a little easier.

Hi. An OLE object field is typically used to store file information as stream data. For example, to store an image or document file in a database table, it is converted into a bunch of 1s and 0s to represent the file content and reconverted back from 1s and 0s to represent the file again. You may have to find out what "file type" the commercial program is representing in the Notes field. Just a thought...

emillerd · Jul 30, 2019

I will try the CStr(Notes) this weekend when I can spend a little time to play with it and research the command's syntax. I haven't been keeping up with software knowledge and have lost most of what I did know

isladogs · Jul 30, 2019

That is the syntax!
Attached is the modified version of the Milco database with a query including the CStr(Notes) as a field. See Query1. As I said you can't use it as the output looks like 'chinese' characters similar to what you see in corrupted data

emillerd · Jul 30, 2019

Thank you for doing that!
Those are the same results I got when I change the data type to text or memo.

I will have to go through and see if I can find what format it is trying to save the field in. Then maybe I can convert it back to view it. The software views it just fine so it must be able to be done. I just need to find out how they did it.

Thanks for all of your help

arnelgp · Jul 31, 2019

its really text that is on those Notes.
you should investigate which program it was created.
I use web browser control to display them after extracting them to a text file (see current event of the form).

emillerd · Jul 31, 2019

That works great!
I was able to convert your example to a .mdb and it kept the correct data.
Now this weekend all I need to do is research the activex control to make it work on my OST Updater.mdb. I added a WebBrowser box but now I need to figure out how to have it show the correct data. I'll poke around your form a little and see if I can figure it out.

emillerd · Jul 31, 2019

Perfect!
The web control works great after I modified your code to reflect the control name.
Thank you!

For future use if anyone else runs into this:
1) In the form create an ActiveX Web browser control
2) In the VB Codes section it should read as the following;
Option Compare Database
Option Explicit

Private Sub Form_Current()

Const adTypeBinary As Integer = 1

Dim objStream As Object
Dim strFile As String

strFile = Environ("temp") & "\myNote.txt"
Set objStream = CreateObject("ADODB.Stream")

If Dir(strFile) <> "" Then Kill strFile

With objStream
.Type = adTypeBinary
.Open
If Len(Me.Recordset.Fields("Notes").Value) > 0 Then
.Write Me.Recordset.Fields("Notes").Value
.SaveToFile strFile
End If
.Close
End With
Set objStream = Nothing
If Dir(strFile) <> "" Then
Me.WebBrowser2.Object.Navigate2 strFile
Else
Me.WebBrowser2.Object.Navigate2 "ABOUT:BLANK"
End If
End Sub

Note: In the example above, the field I want to view is Notes and the WebBrowser control on my form is called WebBrowser2. You would need to change the names to reflect your project.

Credit is due to arnelgp for showing me how to accomplish this.

Display field (Long Binary Data) in a linked form (1 Viewer)

emillerd

New member

Attachments

isladogs

MVP / VIP

emillerd

New member

isladogs

MVP / VIP

theDBguy

I’m here to help

emillerd

New member

isladogs

MVP / VIP

Attachments

emillerd

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

emillerd

New member

emillerd

New member

Similar threads

Users who are viewing this thread