D
Danny J. Lesandrini
I need to extract some PDF files from a SQL Server BLOB field, linked in an
Access MDB file. I found a couple different code approaches, both of which
are represented below, and both of which I've tested out.
I keep getting this error when I try to open the file that is created.
Adobe Reader could not open <file name> because it is either not a
supported file type or because the file has been corrupted.
I know the file IS a PDF file, because if I double click on the OLE field in
the linked table from the Access MDB, the PDF opens up fine. I've tried it
with several variations:
* From an Access 2003 database
* From an Access 2000 database
* With the DAO.Recordset.GetChunk code (below)
* With the ADO.Recordset.GetChunk code (not shown)
* With the ADO.Stream object (commented out below)
I also tried it on a few different backup copies of the SQL Server database,
from and on different servers. Sometimes through a VPN connection and
sometimes directly from the server.
I'm out of options here. Any chance someone sees what's wrong with my
approach and/or has an alternate solution for getting files out of a BLOB field?
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com
Function ProcessEventRows()
On Error GoTo Err_WriteBLOB
Dim intNumBlocks As Integer
Dim intDestFile As Integer
Dim intI As Integer
Dim lngFileLength As Long
Dim lngLeftOver As Long
Dim strFileData As String
Dim varRetVal As Variant
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strField As String
Dim strDestination As String
Dim strSQL As String
Dim strErr As String
Dim lEID As Long
Dim lAID As Long
Dim strPath As String
Dim strFileName As String
If MsgBox("Do you want to clear the log and process ALL event files?", vbQuestion + vbYesNo, "Continue?") = vbNo Then Exit
Function
Set dbs = CurrentDb
strSQL = "DELETE FROM tblAttachmentLog"
dbs.Execute strSQL
strSQL = "SELECT AbsenceID, EventID, FilePath, OldName, AltName, Attachment FROM qryEventLog"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
lEID = rst!EventID
lAID = rst!AbsenceID
strPath = rst!FilePath
strFileName = Trim(Nz(rst!OldName, ""))
If strFileName = "" Then strFileName = strAltName
strFileName = lAID & "-" & lEID & strFileName & ".pdf"
strDestination = strPath & strFileName
If Dir(strDestination) <> "" Then Kill strDestination
'Get the size of the field.
strField = "Attachment"
lngFileLength = rst(strField).FieldSize
'Cancel if field is empty.
If lngFileLength = 0 Then
' can't process this one ...
Stop
Else
' alternate code example for extracting BLOBs ... didn't work either !
'Dim mStream As ADODB.Stream
'Set mStream = New ADODB.Stream
'mStream.Type = adTypeBinary
'mStream.Open
'mStream.Write rst.Fields(strField).Value
'mStream.SaveToFile strDestination, adSaveCreateOverWrite
'Set mStream = Nothing
strSQL = "INSERT INTO tblAttachmentLog (EventID, FilePath, FileName, FileSize) " & _
"VALUES (" & lEID & ",'" & strPath & "','" & strFileName & "'," & lngFileLength & ")"
dbs.Execute strSQL
'Calculate number of blocks to write and the leftover bytes.
intNumBlocks = lngFileLength \ BLOCKSIZE
lngLeftOver = lngFileLength Mod BLOCKSIZE
'Create pointer for to destination file.
intDestFile = FreeFile
Open strDestination For Output As intDestFile
Close intDestFile
'Open the destination file.
Open strDestination For Binary As intDestFile
strFileData = rst(strField).GetChunk(0, lngLeftOver)
Put intDestFile, , strFileData
'Read the leftover chunks and write it to output file.
For intI = 1 To intNumBlocks
DoEvents
strFileData = rst(strField).GetChunk((intI - 1) * BLOCKSIZE + lngLeftOver, BLOCKSIZE)
Put intDestFile, , strFileData
Next intI
Put intDestFile, , strFileData
Close intDestFile
End If
rst.MoveNext
Loop
Exit_Here:
Set dbs = Nothing
Set rst = Nothing
Exit Function
Err_WriteBLOB:
' log error here --
Resume Next
End Function
Access MDB file. I found a couple different code approaches, both of which
are represented below, and both of which I've tested out.
I keep getting this error when I try to open the file that is created.
Adobe Reader could not open <file name> because it is either not a
supported file type or because the file has been corrupted.
I know the file IS a PDF file, because if I double click on the OLE field in
the linked table from the Access MDB, the PDF opens up fine. I've tried it
with several variations:
* From an Access 2003 database
* From an Access 2000 database
* With the DAO.Recordset.GetChunk code (below)
* With the ADO.Recordset.GetChunk code (not shown)
* With the ADO.Stream object (commented out below)
I also tried it on a few different backup copies of the SQL Server database,
from and on different servers. Sometimes through a VPN connection and
sometimes directly from the server.
I'm out of options here. Any chance someone sees what's wrong with my
approach and/or has an alternate solution for getting files out of a BLOB field?
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com
Function ProcessEventRows()
On Error GoTo Err_WriteBLOB
Dim intNumBlocks As Integer
Dim intDestFile As Integer
Dim intI As Integer
Dim lngFileLength As Long
Dim lngLeftOver As Long
Dim strFileData As String
Dim varRetVal As Variant
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strField As String
Dim strDestination As String
Dim strSQL As String
Dim strErr As String
Dim lEID As Long
Dim lAID As Long
Dim strPath As String
Dim strFileName As String
If MsgBox("Do you want to clear the log and process ALL event files?", vbQuestion + vbYesNo, "Continue?") = vbNo Then Exit
Function
Set dbs = CurrentDb
strSQL = "DELETE FROM tblAttachmentLog"
dbs.Execute strSQL
strSQL = "SELECT AbsenceID, EventID, FilePath, OldName, AltName, Attachment FROM qryEventLog"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
lEID = rst!EventID
lAID = rst!AbsenceID
strPath = rst!FilePath
strFileName = Trim(Nz(rst!OldName, ""))
If strFileName = "" Then strFileName = strAltName
strFileName = lAID & "-" & lEID & strFileName & ".pdf"
strDestination = strPath & strFileName
If Dir(strDestination) <> "" Then Kill strDestination
'Get the size of the field.
strField = "Attachment"
lngFileLength = rst(strField).FieldSize
'Cancel if field is empty.
If lngFileLength = 0 Then
' can't process this one ...
Stop
Else
' alternate code example for extracting BLOBs ... didn't work either !
'Dim mStream As ADODB.Stream
'Set mStream = New ADODB.Stream
'mStream.Type = adTypeBinary
'mStream.Open
'mStream.Write rst.Fields(strField).Value
'mStream.SaveToFile strDestination, adSaveCreateOverWrite
'Set mStream = Nothing
strSQL = "INSERT INTO tblAttachmentLog (EventID, FilePath, FileName, FileSize) " & _
"VALUES (" & lEID & ",'" & strPath & "','" & strFileName & "'," & lngFileLength & ")"
dbs.Execute strSQL
'Calculate number of blocks to write and the leftover bytes.
intNumBlocks = lngFileLength \ BLOCKSIZE
lngLeftOver = lngFileLength Mod BLOCKSIZE
'Create pointer for to destination file.
intDestFile = FreeFile
Open strDestination For Output As intDestFile
Close intDestFile
'Open the destination file.
Open strDestination For Binary As intDestFile
strFileData = rst(strField).GetChunk(0, lngLeftOver)
Put intDestFile, , strFileData
'Read the leftover chunks and write it to output file.
For intI = 1 To intNumBlocks
DoEvents
strFileData = rst(strField).GetChunk((intI - 1) * BLOCKSIZE + lngLeftOver, BLOCKSIZE)
Put intDestFile, , strFileData
Next intI
Put intDestFile, , strFileData
Close intDestFile
End If
rst.MoveNext
Loop
Exit_Here:
Set dbs = Nothing
Set rst = Nothing
Exit Function
Err_WriteBLOB:
' log error here --
Resume Next
End Function