Access, OLE & VB.NET

[Stan Smith]

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_database/microsoftaccess/article.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

 

[Gman]

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]

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