469,330 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Access, OLE & VB.NET

I couldn't decide which newsgroup to post this in so I thought I would start
here.

I can create an "OLE Object" field manually in Microsoft Access and insert
an Excel spreadsheet or a Word document, etc. into the field.

I would like to be able to programmatically do the same thing. Basicaly I
want to be able to add and retrieve and reconstitute Excel spreadsheets and
Word documents, etc. I don't need to be able to programmatically create the
Access database and I'm pretty sure that I can do that with other code I
have already written.

Can someone point me in the right direction? I have already looked at the
following link and while I have a little experience with C++ it would be
much more preferable to write the app in VB.NET.

http://www.codeguru.com/Cpp/data/mfc...icle.php/c1123

Thanks.

Stan

Stan Smith
ACT! Certified Consultant
ADS Programming Services
2320 Highland Avenue South
Suite 290
Birmingham, AL 35205
205-222-1661
www.adsprogramming.com
ssmith_at_adsprogramming.com

Mar 15 '06 #1
2 2717
This may help - it's VB6 code but should give you an idea. I need to
upgrade it to VB.NET too actually.

It's a function I use for inserting image files into Access - should
work for any binary file. I found a few examples on ms.com and elsewhere
but none of them seemed to work that well. I rewrote the below function
and it seems to work ok.

HTH

'This function updates the first row in a recordset with the passed 'file.
'The recordset should contain the primary key of the record as well as
'the BLOB field otherwise you get a "missing key" error.

Private Const BlockSize = 32000

Function fcnLoadBlobIntoDatabase(SourceFile As String, _
r As Recordset, sField As String) As Boolean

Dim NumBlocks As Integer, F As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData() As Byte

On Error GoTo Err_ReadBLOB

'open the source file for access
F = FreeFile
Open SourceFile For Binary Access Read As F

' Get the length of the file.
FileLength = LOF(F)
If FileLength = 0 Then Exit Function

' Calculate the number of blocks to read and leftover bytes.
LeftOver = FileLength Mod BlockSize
NumBlocks = (FileLength - LeftOver) \ BlockSize

'Now load read the file in blocks and load into FileData
Do While i <= NumBlocks
'size FileData as per blocksize of the same size
'as the remaining data
If i < NumBlocks Then
ReDim FileData(BlockSize - 1)
Else
ReDim FileData(LeftOver - 1)
End If

'Read the data from the file into FileData
Get F, , FileData

'append to our BLOB field
r(sField).AppendChunk (FileData)
'increment our block counter
i = i + 1
Loop

' Update the record - we're done
r.Update

fcnLoadBlobIntoDatabase = True

Err_ReadBLOB:
r.close
On Error Resume Next
Close F
End Function

And for getting the BLOB out again.

Function fcnGetBlobFromDatabase(r As Recordset, sField As String,
DestinationFile As String) As Boolean

Dim NumBlocks As Integer, F As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData() As Byte

On Error GoTo Err_WriteBLOB

' Get the size of the field.
FileLength = r(sField).ActualSize
If FileLength = 0 Then Exit Function

' Calculate number of blocks to write and leftover bytes.
LeftOver = FileLength Mod BlockSize
NumBlocks = (FileLength - LeftOver) / BlockSize

' Remove any existing destination file.
F = FreeFile
Open DestinationFile For Output As F
Close F

' Open the destination file.
Open DestinationFile For Binary As F

'Loop through the blob
Do
FileData() = r(sField).GetChunk(BlockSize)
Put F, , FileData()
Loop Until UBound(FileData) < BlockSize - 1

fcnGetBlobFromDatabase = True

Err_WriteBLOB:
On Error Resume Next
Close F

End Function
Stan Smith wrote:
I couldn't decide which newsgroup to post this in so I thought I would start
here.

I can create an "OLE Object" field manually in Microsoft Access and insert
an Excel spreadsheet or a Word document, etc. into the field.

I would like to be able to programmatically do the same thing. Basicaly I
want to be able to add and retrieve and reconstitute Excel spreadsheets and
Word documents, etc. I don't need to be able to programmatically create the
Access database and I'm pretty sure that I can do that with other code I
have already written.

Can someone point me in the right direction? I have already looked at the
following link and while I have a little experience with C++ it would be
much more preferable to write the app in VB.NET.

http://www.codeguru.com/Cpp/data/mfc...icle.php/c1123

Thanks.

Stan

Stan Smith
ACT! Certified Consultant
ADS Programming Services
2320 Highland Avenue South
Suite 290
Birmingham, AL 35205
205-222-1661
www.adsprogramming.com
ssmith_at_adsprogramming.com

Mar 16 '06 #2
Gman,

Thanks. I'll give it a try and see what happens.

Stan

Stan Smith
ACT! Certified Consultant
ADS Programming Services
2320 Highland Avenue South
Suite 290
Birmingham, AL 35205
205-222-1661
www.adsprogramming.com
ssmith_at_adsprogramming.com

See what 40 Crystal Reports can do for ACT! 6 at
www.crystalclearreports.com
"Gman" <nah> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
This may help - it's VB6 code but should give you an idea. I need to
upgrade it to VB.NET too actually.

It's a function I use for inserting image files into Access - should work
for any binary file. I found a few examples on ms.com and elsewhere but
none of them seemed to work that well. I rewrote the below function and it
seems to work ok.

HTH

'This function updates the first row in a recordset with the passed 'file.
'The recordset should contain the primary key of the record as well as
'the BLOB field otherwise you get a "missing key" error.

Private Const BlockSize = 32000

Function fcnLoadBlobIntoDatabase(SourceFile As String, _
r As Recordset, sField As String) As Boolean

Dim NumBlocks As Integer, F As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData() As Byte

On Error GoTo Err_ReadBLOB

'open the source file for access
F = FreeFile
Open SourceFile For Binary Access Read As F

' Get the length of the file.
FileLength = LOF(F)
If FileLength = 0 Then Exit Function

' Calculate the number of blocks to read and leftover bytes.
LeftOver = FileLength Mod BlockSize
NumBlocks = (FileLength - LeftOver) \ BlockSize

'Now load read the file in blocks and load into FileData
Do While i <= NumBlocks
'size FileData as per blocksize of the same size
'as the remaining data
If i < NumBlocks Then
ReDim FileData(BlockSize - 1)
Else
ReDim FileData(LeftOver - 1)
End If

'Read the data from the file into FileData
Get F, , FileData

'append to our BLOB field
r(sField).AppendChunk (FileData)
'increment our block counter
i = i + 1
Loop

' Update the record - we're done
r.Update

fcnLoadBlobIntoDatabase = True

Err_ReadBLOB:
r.close
On Error Resume Next
Close F
End Function

And for getting the BLOB out again.

Function fcnGetBlobFromDatabase(r As Recordset, sField As String,
DestinationFile As String) As Boolean

Dim NumBlocks As Integer, F As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData() As Byte

On Error GoTo Err_WriteBLOB

' Get the size of the field.
FileLength = r(sField).ActualSize
If FileLength = 0 Then Exit Function

' Calculate number of blocks to write and leftover bytes.
LeftOver = FileLength Mod BlockSize
NumBlocks = (FileLength - LeftOver) / BlockSize

' Remove any existing destination file.
F = FreeFile
Open DestinationFile For Output As F
Close F

' Open the destination file.
Open DestinationFile For Binary As F

'Loop through the blob
Do
FileData() = r(sField).GetChunk(BlockSize)
Put F, , FileData()
Loop Until UBound(FileData) < BlockSize - 1

fcnGetBlobFromDatabase = True

Err_WriteBLOB:
On Error Resume Next
Close F

End Function
Stan Smith wrote:
I couldn't decide which newsgroup to post this in so I thought I would
start here.

I can create an "OLE Object" field manually in Microsoft Access and
insert an Excel spreadsheet or a Word document, etc. into the field.

I would like to be able to programmatically do the same thing. Basicaly
I want to be able to add and retrieve and reconstitute Excel spreadsheets
and Word documents, etc. I don't need to be able to programmatically
create the Access database and I'm pretty sure that I can do that with
other code I have already written.

Can someone point me in the right direction? I have already looked at
the following link and while I have a little experience with C++ it would
be much more preferable to write the app in VB.NET.

http://www.codeguru.com/Cpp/data/mfc...icle.php/c1123

Thanks.

Stan

Stan Smith
ACT! Certified Consultant
ADS Programming Services
2320 Highland Avenue South
Suite 290
Birmingham, AL 35205
205-222-1661
www.adsprogramming.com
ssmith_at_adsprogramming.com


Mar 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by cisco | last post: by
reply views Thread by TheCoder | last post: by
4 posts views Thread by RichB | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.