downloading OLE Object /BLOB data (.doc/.xls files) from SQL Server table to local machine through M

[FA]

I have a table which has a filed as OLE Object in which i have
either .doc or .xls files stored in SQL Server.
I using MS Access as a front and which has linked tables with sql
server. i have created a form called frmfiledownload. The field name
in the database is called filedata datatype OLE Object.

I need to to be able to dowload that file on my loacl machine. I need
to have a filedata field showing the file name and when i can click a
button it should download that file on my machine.

If anyone has done something like that please please do send me your
code sample.

thanks bunch in advance.

 

[Graham R Seach]

This should get you started:
http://support.microsoft.com/kb/103257

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Stephen Lebans]

Or if the OP has embedded OLE objects vs BLOBs see:
http://www.lebans.com/oletodisk.htm
NEW - Feb 06/2006 ExtractInventoryOLE.zip A2K or higher ONLY! This
version saves the entire contents of a table containing OLE Objects to disk.
Does NOT require the original application that served as the OLE server to
insert the object. Supports all MS Office documents, PDF, All images
inserted by MS Photo Editor, MS Paint, and Paint Shop Pro. Also supports
extraction of PACKAGE class including original Filename. Contains function
to produce a full Inventory of the OLE field including LINKED path and
Filenames. Uses Structured Storage API's to read the actual contents of the
field
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[FA]

Or if the OP has embedded OLE objects vs BLOBs see:http://www.lebans.com/oletodisk.htm
NEW - Feb 06/2006 ExtractInventoryOLE.zip A2K or higher ONLY! This
version saves the entire contents of a table containing OLE Objects to disk.
Does NOT require the original application that served as the OLE server to
insert the object. Supports all MS Office documents, PDF, All images
inserted by MS Photo Editor, MS Paint, and Paint Shop Pro. Also supports
extraction of PACKAGE class including original Filename. Contains function
to produce a full Inventory of the OLE field including LINKED path and
Filenames. Uses Structured Storage API's to read the actual contents of the
field
--

HTH
Stephen Lebanshttp://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.







- Show quoted text -

Thanks both of you friends. I think i am going to use the following
function on my form open event procedure. I need to adjust the codes
in the function so it only reads the OLE object and display the
content in the field name FileData. Let say if i have a form called
frmFile and in that form i have a OLE Object bound field called
FileData. The record source of the form is table dbo_file. How can i
use the following function to read the content of field FileData and
display it on the the FileData field on the form. It would be nice to
have if i can double click the field and it allows me to save it on my
machine.
Function ReadBLOB(Source As String, T As Recordset, sField As String)
Dim NumBlocks As Integer, SourceFile As Integer, i As
Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
' On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
FileLength \ 1000) ' Put first record in
edit mode.
' T.MoveFirst
' T.Edit
T.AddNew
' Read the leftover data, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Read the remaining blocks of data, writing them to the
table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i /
1000)
Next i ' Update the record and terminate function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter)
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
MsgBox "ReadBLOB Error " & Err & " : " & Error$
Exit Function
End Function

thanks

 

[FA]

Thanks both of you friends. I think i am going to use the following
function on my form open event procedure. I need to adjust the codes
in the function so it only reads the OLE object and display the
content in the field name FileData. Let say if i have a form called
frmFile and in that form i have a OLE Object bound field called
FileData. The record source of the form is table dbo_file. How can i
use the following function to read the content of field FileData and
display it on the the FileData field on the form. It would be nice to
have if i can double click the field and it allows me to save it on my
machine.
Function ReadBLOB(Source As String, T As Recordset, sField As String)
Dim NumBlocks As Integer, SourceFile As Integer, i As
Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
' On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source ForBinaryAccess Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
FileLength \ 1000) ' Put first record in
edit mode.
' T.MoveFirst
' T.Edit
T.AddNew
' Read the leftoverdata, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Read the remaining blocks ofdata, writing them to the
table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i /
1000)
Next i ' Update the record and terminate function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter)
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
MsgBox "ReadBLOB Error " & Err & " : " & Error$
Exit Function
End Function

thanks- Hide quoted text -

- Show quoted text -

Forgot to mention, I do not need to append any binary data into the
SQL Server table. The data is already there in SQL Server 2005 table,
i just need to view that data in Access form. The field type in SQL
Server table is binary.

 

[FA]

Forgot to mention, I do not need to append any binary data into the
SQL Server table. The data is already there in SQL Server 2005 table,
i just need to view that data in Access form. The field type in SQL
Server table is binary.- Hide quoted text -

- Show quoted text -

Guys: I have got the working code which is downloading the file on my
c drive:
Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=fm-dutram01;" & _
"Initial Catalog=PRATDev;User Id=PRATDev;Password=T00lt1p07"

'Set rs = New ADODB.Recordset
'rs.Open "Select * from tblFile where Id =" & "'" & Me![ID] & "'"",
cn, adOpenKeyset, adLockOptimistic"

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value
mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite

'rs.Close
cn.Close
End Sub

I need a little addition, instead of downloading it directly into C:
\ , i would like to open up a common file dailog where user can choose
where they want to save the file with the same file name.

Your help would be greatly appreciated.

 

[FA]

Forgot to mention, I do not need to append any binary data into the
SQL Server table. The data is already there in SQL Server 2005 table,
i just need to view that data in Access form. The field type in SQL
Server table is binary.- Hide quoted text -

- Show quoted text -

Guys: I have got the following codes working, i just need a little bit
of help in prompting user to save the file in thier desired place
instead of C:\.

Your help would be greatly appreciated.
thanks

Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="


Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value
mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite


cn.Close
End Sub

 

[Douglas J. Steele]

Guys: I have got the following codes working, i just need a little bit
of help in prompting user to save the file in thier desired place
instead of C:\.

Your help would be greatly appreciated.
thanks

Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="


Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value
mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite


cn.Close
End Sub

Use the code in http://www.mvps.org/access/api/api0001.htm to invoke the
Windows Save File dialog and store it in a variable, then change the line of
code

mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite


to use that information rather than "C:\" & Me![FileName]

 

[FA]

Guys: I have got the following codes working, i just need a little bit
of help in prompting user to save the file in thier desired place
instead of C:\.

Your help would be greatly appreciated.
thanks

Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value
mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite

cn.Close
End Sub

Use the code inhttp://www.mvps.org/access/api/api0001.htmto invoke the
Windows Save File dialog and store it in a variable, then change the line of
code

mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite

to use that information rather than "C:\" & Me![FileName]

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -

I am tring the following but it is giving me compile error : "
Ambiguous name detected: ahtAddFilterItem"

I have copied the codes from that website and saved it into a module.
It compiled until i used it in the following function.
Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="



Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value


Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "All FIles(*)", "*")

strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)




mstream.SaveToFile strSaveFileName

cn.Close
End Sub

 

[FA]

news:[email protected]...

Guys: I have got the following codes working, i just need a little bit
of help in prompting user to save the file in thier desired place
instead of C:\.
Your help would be greatly appreciated.
thanks
Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value
mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite
cn.Close
End Sub

Use the code inhttp://www.mvps.org/access/api/api0001.htmtoinvoke the
Windows Save File dialog and store it in a variable, then change the line of
code

mstream.SaveToFile "C:\" & Me![FileName], adSaveCreateOverWrite

to use that information rather than "C:\" & Me![FileName]

- Show quoted text -

I am tring the following but it is giving me compile error : "
Ambiguous name detected: ahtAddFilterItem"

I have copied the codes from that website and saved it into a module.
It compiled until i used it in the following function.
Private Sub cmdExtract_Click()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value

Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "All FIles(*)", "*")

strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

mstream.SaveToFile strSaveFileName

cn.Close
End Sub- Hide quoted text -

- Show quoted text -

It worked !!!!!!!!!!! thanks alot I got the following codes working.
Only one tiny trouble, when the user click on cancel instead of save
in the dialog box, it gives an error message " Arguments are of the
wrong type, are of accesptable range, or are in confilict with one
another" Run time error '3001'


Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=;" & _
"Initial Catalog=;User Id=;Password="



Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write Me.FileData.Value

Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "All Files (*)", "*")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, FileName:=Me!
[FileName], _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)






mstream.SaveToFile strSaveFileName

 

[krissco]

Check the value returned by the common dialog:
strSaveFileName

If the value stored is not acceptable, then don't call this function:
mstream.SaveToFile strSaveFileName


something like:

strSaveFileName = blah blah blah

if len(strsavefilename & "") > 0 then
mstream.SaveToFile strSaveFileName
else
msgbox "HEY! Gimme File Name!!! MMM...."
end if


-Kris