OLEs and BLOBs

[Guest]

I am currently transferring a database from Oracle to an Access Data Project.
There is a field which contains OLEs and BLOBs stored within the database.

Any ideas on how I could extract these files programatically ?

Thanks.

 

[Alex White MCDBA MCSE]

Well if you are going to an ADP you are going to use SQL right?

if so the image field type is what you are looking for.

Here is some code to get file into SQL, I guess it is quite simple getting
the data out of Oracle, worst case write the file to the OS and then back
in, I am guessing you are storing files?

Dim adoConn As New ADODB.Connection
Dim adoDocument As New ADODB.Recordset
adoConn.Open "File Name=c:\to.udl"
Dim mStream As ADODB.Stream
Dim sql As String
sql = "Select Doc_Embed from TblCandidate_Contact_History Where
Candidate_Contact_History_ID=" & ' your primary key
Set adoDocument = New ADODB.Recordset
adoDocument.Open sql, adoConn, adOpenKeyset, adLockOptimistic
Set mStream = New ADODB.Stream
With mStream
.Type = adTypeBinary
.Open
.LoadFromFile myDoc
End With
With adoDocument
.MoveFirst
.Fields("Doc_Embed").Value = mStream.Read
.Update
.Close
End With
mStream.Close
adoConn.Close

 

[Guest]

Thanks for your help Alex, but I'm not able to get very far with your
information.
The first problem is with the Stream. What reference library should I use
for this ?
Although you seem to use msado21.tlb, it is not avaialable to me.

More specifically, I want to extract data from a SQL Server image data type
and store it into a file. The Oracle data was converted to SQL Server but I
don't want to store it in the database.

Thank you.

 

[Alex White MCDBA MCSE]

Hi Scott,

The Reference you need is Microsoft ActiveX Data Objects 2.8 Library

below is just a cut and paste from my work, it will save the document to the
temp folder, this function is passed an id, the resulting document will be
id.doc, you will need another field for document type as mine is only setup
for word documents.

Public Function ReadFromDB(strID As String) As String
On Error GoTo Err_ReadFromDB
Dim adoDocument As New ADODB.Recordset
Dim mStream As ADODB.Stream
Dim SQL As String
Dim strTemp As String
strTemp = Environ("Temp")
SQL = "Select * from TblCandidate_Contact_History Where
Candidate_Contact_History_ID=" & strID
Set adoDocument = New ADODB.Recordset
adoDocument.Open SQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.Write adoDocument.Fields("Doc_Embed").Value
mStream.SaveToFile strTemp & "\" & strID & ".doc", adSaveCreateOverWrite
ReadFromDB = strTemp & "\" & strID & ".doc"
adoDocument.Close
Exit_ReadFromDB:
Exit Function
Err_ReadFromDB:
DisplayError "modSQLDoc", "ReadFromDB", Err.Description, Err.Number, Erl
Resume Next
End Function


any problems post back,

 

[Guest]

Thanks Alex, I was able to use your code.
I save the file to my hard drive, but the .doc files have symbols instead
of text.
The .pdf files are not able to be opened.

Any ideas ? Thanks.