Save Image to Field

[Chicho]

Hello, Iam trying to save an image to a Field in a Recordset, but dont really
Know how.
can you help me?

[Gary Johnson]

"Chicho" <chicho_chicho@hotmail.com> wrote:
>
>Hello, Iam trying to save an image to a Field in a Recordset, but dont really
>Know how.
>can you help me?

Once you have a recordset with the image field try something along the lines
of this...

BlockSize = 1024
Set oField = Rs("ImageField")
FileLen = oField.ActualSize
NumBlocks = FileLen / BlockSize
LeftOver = FileLen Mod BlockSize
'For ASP Change the Img to Response.BinaryWrite
Img = oField.GetChunk(LeftOver)
For Ctr = 1 to NumBlocks
Img = Img & field.GetChunk(BlockSize)
Next
Rs.Close

Hope this helps!

Gary Johnson
Sr Database Engineer

[D. Patrick Hoerter]

Gary,

He's trying to SAVE the image, not retrieve it.

Chicho,

You'll need to open the file for Binary access, and read it a chunk at a
time (say, 2K or so), and use the ADO AppendChunk method to append to the
field in your recordset.

Regards,
D. Patrick Hoerter

Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>
>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>
>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
really
>>Know how.
>>can you help me?
>
>Once you have a recordset with the image field try something along the
lines
>of this...
>
>BlockSize = 1024
>Set oField = Rs("ImageField")
>FileLen = oField.ActualSize
>NumBlocks = FileLen / BlockSize
>LeftOver = FileLen Mod BlockSize
>'For ASP Change the Img to Response.BinaryWrite
>Img = oField.GetChunk(LeftOver)
>For Ctr = 1 to NumBlocks
> Img = Img & field.GetChunk(BlockSize)
>Next
>Rs.Close
>
>Hope this helps!
>
>Gary Johnson
>Sr Database Engineer
>

[Justin]

This code is almost what I'm chasing but I am stuck on what Img is supposed
to be. It is an image control, an ole object, a file ..... hmmmm

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>Gary,
>
> He's trying to SAVE the image, not retrieve it.
>
>Chicho,
>
> You'll need to open the file for Binary access, and read it a chunk
at a
>time (say, 2K or so), and use the ADO AppendChunk method to append to the
>field in your recordset.
>
>Regards,
>D. Patrick Hoerter
>
>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>>
>>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>>
>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
>really
>>>Know how.
>>>can you help me?
>>
>>Once you have a recordset with the image field try something along the
>lines
>>of this...
>>
>>BlockSize = 1024
>>Set oField = Rs("ImageField")
>>FileLen = oField.ActualSize
>>NumBlocks = FileLen / BlockSize
>>LeftOver = FileLen Mod BlockSize
>>'For ASP Change the Img to Response.BinaryWrite
>>Img = oField.GetChunk(LeftOver)
>>For Ctr = 1 to NumBlocks
>> Img = Img & field.GetChunk(BlockSize)
>>Next
>>Rs.Close
>>
>>Hope this helps!
>>
>>Gary Johnson
>>Sr Database Engineer
>>
>

[D. Patrick Hoerter]

Justin,

In that code snippet provided, I assume that Img is a byte array.

Regards,
D. Patrick Hoerter


Justin wrote in message <395cec36$1@news.devx.com>...
>
>This code is almost what I'm chasing but I am stuck on what Img is supposed
>to be. It is an image control, an ole object, a file ..... hmmmm
>
>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>Gary,
>>
>> He's trying to SAVE the image, not retrieve it.
>>
>>Chicho,
>>
>> You'll need to open the file for Binary access, and read it a chunk
>at a
>>time (say, 2K or so), and use the ADO AppendChunk method to append to the
>>field in your recordset.
>>
>>Regards,
>>D. Patrick Hoerter
>>
>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>>>
>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>>>
>>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
>>really
>>>>Know how.
>>>>can you help me?
>>>
>>>Once you have a recordset with the image field try something along the
>>lines
>>>of this...
>>>
>>>BlockSize = 1024
>>>Set oField = Rs("ImageField")
>>>FileLen = oField.ActualSize
>>>NumBlocks = FileLen / BlockSize
>>>LeftOver = FileLen Mod BlockSize
>>>'For ASP Change the Img to Response.BinaryWrite
>>>Img = oField.GetChunk(LeftOver)
>>>For Ctr = 1 to NumBlocks
>>> Img = Img & field.GetChunk(BlockSize)
>>>Next
>>>Rs.Close
>>>
>>>Hope this helps!
>>>
>>>Gary Johnson
>>>Sr Database Engineer
>>>
>>
>

[Justin]

I'm still having problems.

The code noted below is suppposed to save the image of Andrew Fuller found
in the employees table of the northwind database.

I have created a data environment (DataEnvironment1) that points to the northwind
database (Connection1), one form, and one button (Command1).

The problem I have got is that image file saved cannot be opened.

I suspect that I am either not transfering bytes correctly or I am saving
some sort of ole object and not the actual data file.

If anyone could shed some light would be appreciated.

Dim cnn As ADODB.Connection

Private Sub Command1_Click()
Dim oFiled As ADODB.Field

Dim FileLen As Long

Dim Image() As Byte

Set cnn = DataEnvironment1.Connection1
Set rs = New ADODB.Recordset

F = FreeFile
Open "C:\temp\test.bmp" For Binary As #F

cnn.CursorLocation = adUseServer
cnn.Open
SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
rs.Open SQL, cnn, adOpenStatic, adLockReadOnly

Set oField = rs!Photo

FileLen = oField.ActualSize
NumBlocks = FileLen / 1024
LeftOver = FileLen Mod 1024
Image = oField.GetChunk(LeftOver)
Put #F, , Image
For Ctr = 1 To NumBlocks
Image = oField.GetChunk(1024)
Put #F, , Image
Next
Close #F
rs.Close
cnn.Close
End Sub

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>Justin,
>
> In that code snippet provided, I assume that Img is a byte array.
>
>Regards,
>D. Patrick Hoerter
>
>
>Justin wrote in message <395cec36$1@news.devx.com>...
>>
>>This code is almost what I'm chasing but I am stuck on what Img is supposed
>>to be. It is an image control, an ole object, a file ..... hmmmm
>>
>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>Gary,
>>>
>>> He's trying to SAVE the image, not retrieve it.
>>>
>>>Chicho,
>>>
>>> You'll need to open the file for Binary access, and read it a chunk
>>at a
>>>time (say, 2K or so), and use the ADO AppendChunk method to append to
the
>>>field in your recordset.
>>>
>>>Regards,
>>>D. Patrick Hoerter
>>>
>>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>>>>
>>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>>>>
>>>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
>>>really
>>>>>Know how.
>>>>>can you help me?
>>>>
>>>>Once you have a recordset with the image field try something along the
>>>lines
>>>>of this...
>>>>
>>>>BlockSize = 1024
>>>>Set oField = Rs("ImageField")
>>>>FileLen = oField.ActualSize
>>>>NumBlocks = FileLen / BlockSize
>>>>LeftOver = FileLen Mod BlockSize
>>>>'For ASP Change the Img to Response.BinaryWrite
>>>>Img = oField.GetChunk(LeftOver)
>>>>For Ctr = 1 to NumBlocks
>>>> Img = Img & field.GetChunk(BlockSize)
>>>>Next
>>>>Rs.Close
>>>>
>>>>Hope this helps!
>>>>
>>>>Gary Johnson
>>>>Sr Database Engineer
>>>>
>>>
>>
>

[D. Patrick Hoerter]

Justin,

You aren't pulling the "Leftover" bytes from the field. You process the
whole number of chunks, but you still need to pull the last little bit,
after the loop.

Regards,
D. Patrick Hoerter

Justin wrote in message <395dec16$1@news.devx.com>...
>
>I'm still having problems.
>
>The code noted below is suppposed to save the image of Andrew Fuller found
>in the employees table of the northwind database.
>
>I have created a data environment (DataEnvironment1) that points to the
northwind
>database (Connection1), one form, and one button (Command1).
>
>The problem I have got is that image file saved cannot be opened.
>
>I suspect that I am either not transfering bytes correctly or I am saving
>some sort of ole object and not the actual data file.
>
>If anyone could shed some light would be appreciated.
>
>Dim cnn As ADODB.Connection
>
>Private Sub Command1_Click()
> Dim oFiled As ADODB.Field
>
> Dim FileLen As Long
>
> Dim Image() As Byte
>
> Set cnn = DataEnvironment1.Connection1
> Set rs = New ADODB.Recordset
>
> F = FreeFile
> Open "C:\temp\test.bmp" For Binary As #F
>
> cnn.CursorLocation = adUseServer
> cnn.Open
> SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
> rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
>
> Set oField = rs!Photo
>
> FileLen = oField.ActualSize
> NumBlocks = FileLen / 1024
> LeftOver = FileLen Mod 1024
> Image = oField.GetChunk(LeftOver)
> Put #F, , Image
> For Ctr = 1 To NumBlocks
> Image = oField.GetChunk(1024)
> Put #F, , Image
> Next
> Close #F
> rs.Close
> cnn.Close
>End Sub
>
>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>Justin,
>>
>> In that code snippet provided, I assume that Img is a byte array.
>>
>>Regards,
>>D. Patrick Hoerter
>>
>>
>>Justin wrote in message <395cec36$1@news.devx.com>...
>>>
>>>This code is almost what I'm chasing but I am stuck on what Img is
supposed
>>>to be. It is an image control, an ole object, a file ..... hmmmm
>>>
>>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>>Gary,
>>>>
>>>> He's trying to SAVE the image, not retrieve it.
>>>>
>>>>Chicho,
>>>>
>>>> You'll need to open the file for Binary access, and read it a chunk
>>>at a
>>>>time (say, 2K or so), and use the ADO AppendChunk method to append to
>the
>>>>field in your recordset.
>>>>
>>>>Regards,
>>>>D. Patrick Hoerter
>>>>
>>>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>>>>>
>>>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>>>>>
>>>>>>Hello, Iam trying to save an image to a Field in a Recordset, but dont
>>>>really
>>>>>>Know how.
>>>>>>can you help me?
>>>>>
>>>>>Once you have a recordset with the image field try something along the
>>>>lines
>>>>>of this...
>>>>>
>>>>>BlockSize = 1024
>>>>>Set oField = Rs("ImageField")
>>>>>FileLen = oField.ActualSize
>>>>>NumBlocks = FileLen / BlockSize
>>>>>LeftOver = FileLen Mod BlockSize
>>>>>'For ASP Change the Img to Response.BinaryWrite
>>>>>Img = oField.GetChunk(LeftOver)
>>>>>For Ctr = 1 to NumBlocks
>>>>> Img = Img & field.GetChunk(BlockSize)
>>>>>Next
>>>>>Rs.Close
>>>>>
>>>>>Hope this helps!
>>>>>
>>>>>Gary Johnson
>>>>>Sr Database Engineer
>>>>>
>>>>
>>>
>>
>

[Justin]

Good try, but I don't think so. I am actually grabbing the left over bytes
first, before I run the loop, I apologise for the sloppy coding. Just to
be sure I checked to see if it would make a difference be grabbing full blocks
first and then the remaining bits, but sadly no... The upshot of it all
is that when I manually save the image using access, I get different file
sizes. Well sort of. I am not sure what the properties window of Win 98
is telling me but here it is.

My coding saves the picture as:
Size: 21.1KB (21,626 bytes), 24,576 bytes used

Manually saving the picture gives:
Size: 21.0KB (21,536 bytes), 24,576 bytes used

It appears that I am writing too many bytes!!

But here is something that is even weirder,
I delete the image found in the northwind database assigned to Fuller, and
add in a jpg image photo.jpg using access 97. I note the file sizes of the
database Nwind.mdb. They are as follows:

Nwind database with bitmap picture deleted:
Size: 1.53MB (1,607,680 bytes), 1,613,824 bytes used

Nwind database with jpg picture added:
Size: 2.00MB (2,097,152 bytes), 2,097,152 bytes used

I run the program again (I changed the code to save test.jpg instead of test.bmp)
and I get the following results:

My coding saves the picture as:
Size: 541KB (554,822 bytes), 557,056 bytes used

The original picture size is:
Size: 8.03KB (8,229 bytes), 16,384 bytes used

As you can see this is a big difference in file sizes, but here is something
to consider, the database increases in size by approximately half a meg and
the file extracted from the database is also approximately half a meg, yet
the file added is only 8k! My hypothesis is that the data that is stored
in the ole field is not just a binary file but instead a serialised object.
In my particular case it would be a Microsoft photo-editor object, and unfortunately
it is not taking advantage of the compression algorithms used in JPEG files.

I am now going to write a program that is going to use the append chunk function
and write a file directly to an ole field and then extract it back using
the same program below. I suspect this will work as I am not using the insert
object dialog box in access.

However, this doesn't yet solve my original problem, how on earth do you
get an embedded file out of an ole record and display it in an ole control
using ADO.

Please don't give up trying to help me solve this problem because I am well
and truly stumped.

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>Justin,
>
> You aren't pulling the "Leftover" bytes from the field. You process
the
>whole number of chunks, but you still need to pull the last little bit,
>after the loop.
>
>Regards,
>D. Patrick Hoerter
>
>Justin wrote in message <395dec16$1@news.devx.com>...
>>
>>I'm still having problems.
>>
>>The code noted below is suppposed to save the image of Andrew Fuller found
>>in the employees table of the northwind database.
>>
>>I have created a data environment (DataEnvironment1) that points to the
>northwind
>>database (Connection1), one form, and one button (Command1).
>>
>>The problem I have got is that image file saved cannot be opened.
>>
>>I suspect that I am either not transfering bytes correctly or I am saving
>>some sort of ole object and not the actual data file.
>>
>>If anyone could shed some light would be appreciated.
>>
>>Dim cnn As ADODB.Connection
>>
>>Private Sub Command1_Click()
>> Dim oFiled As ADODB.Field
>>
>> Dim FileLen As Long
>>
>> Dim Image() As Byte
>>
>> Set cnn = DataEnvironment1.Connection1
>> Set rs = New ADODB.Recordset
>>
>> F = FreeFile
>> Open "C:\temp\test.bmp" For Binary As #F
>>
>> cnn.CursorLocation = adUseServer
>> cnn.Open
>> SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
>> rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
>>
>> Set oField = rs!Photo
>>
>> FileLen = oField.ActualSize
>> NumBlocks = FileLen / 1024
>> LeftOver = FileLen Mod 1024
>> Image = oField.GetChunk(LeftOver)
>> Put #F, , Image
>> For Ctr = 1 To NumBlocks
>> Image = oField.GetChunk(1024)
>> Put #F, , Image
>> Next
>> Close #F
>> rs.Close
>> cnn.Close
>>End Sub
>>
>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>Justin,
>>>
>>> In that code snippet provided, I assume that Img is a byte array.
>>>
>>>Regards,
>>>D. Patrick Hoerter
>>>
>>>
>>>Justin wrote in message <395cec36$1@news.devx.com>...
>>>>
>>>>This code is almost what I'm chasing but I am stuck on what Img is
>supposed
>>>>to be. It is an image control, an ole object, a file ..... hmmmm
>>>>
>>>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>>>Gary,
>>>>>
>>>>> He's trying to SAVE the image, not retrieve it.
>>>>>
>>>>>Chicho,
>>>>>
>>>>> You'll need to open the file for Binary access, and read it a chunk
>>>>at a
>>>>>time (say, 2K or so), and use the ADO AppendChunk method to append to
>>the
>>>>>field in your recordset.
>>>>>
>>>>>Regards,
>>>>>D. Patrick Hoerter
>>>>>
>>>>>Gary Johnson wrote in message <395a2d10$1@news.devx.com>...
>>>>>>
>>>>>>"Chicho" <chicho_chicho@hotmail.com> wrote:
>>>>>>>
>>>>>>>Hello, Iam trying to save an image to a Field in a Recordset, but
dont
>>>>>really
>>>>>>>Know how.
>>>>>>>can you help me?
>>>>>>
>>>>>>Once you have a recordset with the image field try something along
the
>>>>>lines
>>>>>>of this...
>>>>>>
>>>>>>BlockSize = 1024
>>>>>>Set oField = Rs("ImageField")
>>>>>>FileLen = oField.ActualSize
>>>>>>NumBlocks = FileLen / BlockSize
>>>>>>LeftOver = FileLen Mod BlockSize
>>>>>>'For ASP Change the Img to Response.BinaryWrite
>>>>>>Img = oField.GetChunk(LeftOver)
>>>>>>For Ctr = 1 to NumBlocks
>>>>>> Img = Img & field.GetChunk(BlockSize)
>>>>>>Next
>>>>>>Rs.Close
>>>>>>
>>>>>>Hope this helps!
>>>>>>
>>>>>>Gary Johnson
>>>>>>Sr Database Engineer
>>>>>>
>>>>>
>>>>
>>>
>>
>

[Justin]

Wahoo,
I am finally getting somewhere,
As I suspected the OLE field that I was attempting to write to file was infact
a serialised object and not simply a binary file.

I can store a Long Binary Object in an OLE field using the appendchunk method,
thus storing a file in the database in its original format. ie: If I write
an 8k jpg file to the database using appendchunk, then I am simply storing
a whole heap of one's and zero's to total storage space of 8k! and not 512K
as an OLE Photo Editor Object.

When I use the GetChunk Method I am simply transferring those one's and zero's
back to a binary file. Thus maintaining the original format.

There are a couple of issues to note:
1. The file stored in the OLE field can no longer be accessed in Access with
an OLE control in the usual fashion as it is looking for an OLE Object and
not a binary file.
2. If the OLE field in the database has already been stretched by a larger
object and you simply update the field with a smaller one then you will get
two end of file symbols in that binary field. When you write the field back
to disk the file size will be as large as the field. This should not affect
things however as most programs will read the file until it reaches the first
end of file symbol. To ensure the database remains compact, I would suggest
trying to delete the contents of the field first, then updating the field
with new file.

Additionally, the previous code issued in the last message had a few logic
errors in it. Due to the way VB handles rounding in it's divisor and mod
operators, inconsistencies with calculations in establishing the number of
blocks and the amount of data left over were found. I have resolved this
problem by using integer division and doing away with the mod operator and
using instead simple operators.

The test application is as follows:
I created one form with two buttons: Save and Load
The save button saves a jpg image "Photo.jpg" to Andrew Fullers photo field
in the Nwind database.
The load button loads the Long Binary Object from Andrew Fullers photo field
and saves it as "test.jpg".
I created one data environment called "dnvMainDataBase"
and one connection that points to the Nwind database "cnnNorthWind".

My Code is as follows:
Code Behind Form1 -

Dim cnn As ADODB.Connection

Private Sub cmdLoad_Click()
Set cnn = dnvMainDatabase.cnnNorthWind
Set rs = New ADODB.Recordset
cnn.Open
cnn.CursorLocation = adUseServer
SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
FileToBlob "c:\temp\photo.jpg", rs!Photo
rs.Update
MsgBox "The jpg file has updated Andrew Fullers photo"
rs.Close
cnn.Close
End Sub

Private Sub cmdSave_Click()
Set cnn = dnvMainDatabase.cnnNorthWind
Set rs = New ADODB.Recordset
cnn.CursorLocation = adUseServer
cnn.Open

SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"

rs.Open SQL, cnn, adOpenStatic, adLockReadOnly

BlobToFile rs!Photo, "c:\temp\test.jpg"

rs.Close
cnn.Close
End Sub

Code Behind Module1-

Option Explicit

Sub BlobToFile(fld As ADODB.Field, _
ByVal FName As String)
'
' Assumes file does not exist and write access
' to the path is granted (Something to note with
' NT based systems}.
' Data cannot exceed approx. 2Gb in size.
'
Dim F As Long, bData() As Byte, sData As String
F = FreeFile
Open FName For Binary As #F
If fld.Type = adLongVarBinary Then
WriteFromBinary F, fld, fld.ActualSize
Else
MsgBox "This is not an OLE Field"
End If
Close #F
End Sub

Sub WriteFromBinary(ByVal F As Long, _
fld As ADODB.Field, _
FieldSize As Long)
Dim Data() As Byte
Dim NumBlocks As Long
Dim LeftOver As Long
Dim Ctr As Long
NumBlocks = FieldSize \ 16384
LeftOver = FieldSize - NumBlocks * 16384
If NumBlocks > 1 Then
For Ctr = 1 To NumBlocks
Data = fld.GetChunk(16384)
Put #F, , Data
Next
End If
Data = fld.GetChunk(LeftOver)
Put #F, , Data
End Sub

Sub FileToBlob(ByVal FName As String, fld As ADODB.Field)
'
' Assumes file exists and can be accessed
' Assumes calling routine does the UPDATE
' File cannot exceed approx. 2Gb in size
'
Dim F As Long, Data() As Byte, FileSize As Long
F = FreeFile
Open FName For Binary As #F
FileSize = LOF(F)
If fld.Type = adLongVarBinary Then
ReadToBinary F, fld, FileSize
Else
MsgBox "This is not an OLE field"
End If
Close #F
End Sub

Sub ReadToBinary(ByVal F As Long, _
fld As ADODB.Field, _
ByVal FileSize As Long)
Dim Data() As Byte
Dim NumBlocks As Long
Dim LeftOver As Long
Dim Ctr As Long
NumBlocks = FileSize \ 16384
LeftOver = FileSize - NumBlocks * 16384
If NumBlocks > 1 Then
For Ctr = 1 To NumBlocks
Data = InputB(16384, F)
fld.AppendChunk Data
Next
End If
Data = InputB(LeftOver, F)
fld.AppendChunk Data
End Sub

[Douglas J. Steele]

As I told you in the Microsoft forum (microsoft.public.vb.database.ado), it
can't be done.

When you run from VB, your only connection to the database is through the
Jet engine, which doesn't know about user-defined functions.

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


"George Handlin" <handling@nospam.home.com> wrote in message
news:3ae1445e@news.devx.com...
> How do I get the results from an Access (2k) query that uses a custom
> function
> (in the db) in VB6? The query runs perfectly in Access, but I get an
> Undefined Function in Expression error when trying to access it from VB.
> Tried it as both a straight recordset and as the result from a command
> object. I've tried having the function in both the mdb and VB, neither
> worked.
>
> Thanx...
>
> --
> -George Handlin, MCP
> http://www.jbconline.com
> ref: 587968
> icq: 50614665
>
>

[George Handlin]

Thanx...

"Douglas J. Steele" <djsteele@canada.com> wrote in message
news:3ae15ca5@news.devx.com...
> As I told you in the Microsoft forum (microsoft.public.vb.database.ado),
it
> can't be done.
>
> When you run from VB, your only connection to the database is through the
> Jet engine, which doesn't know about user-defined functions.
>
> --
>
> Doug Steele, Microsoft Access MVP
> Beer, Wine and Database Programming. What could be better?
> Visit "Doug Steele's Beer and Programming Emporium"
> http://I.Am/DougSteele/
>
>
> "George Handlin" <handling@nospam.home.com> wrote in message
> news:3ae1445e@news.devx.com...
> > How do I get the results from an Access (2k) query that uses a custom
> > function
> > (in the db) in VB6? The query runs perfectly in Access, but I get an
> > Undefined Function in Expression error when trying to access it from VB.
> > Tried it as both a straight recordset and as the result from a command
> > object. I've tried having the function in both the mdb and VB, neither
> > worked.
> >
> > Thanx...
> >
> > --
> > -George Handlin, MCP
> > http://www.jbconline.com
> > ref: 587968
> > icq: 50614665
> >
> >
>
>

[Justin Simpson]

NNTP-Posting-Host: 209.1.14.192
Message-ID: <39658f32$1@news.devx.com>
Date: 7 Jul 2000 01:05:06 -0700
X-Trace: 7 Jul 2000 01:05:06 -0700, 209.1.14.192
Lines: 210
Path: news.devx.com!209.1.14.192
Xref: news.devx.com vb.database.ado:5682


Someone asked me how do you create the data environment mentioned below.

Here it is:

Adding a Data Environment Designer to a VB Project

1. Choose File -> New Project
2. Double-click the Standard EXE icon to start a typical, traditional VB
Project
3. Choose Project->More ActiveX Designers->Add Data Environment

If you don't see Add Data Environment continue on. If you do see Add Data
Environment Skip to Step 8.

4 Choose Project->Components
5. Click the Designers tab.
6. Click the check box next to Data Environment.
7. Click OK

8. Now you can choose Project->Add Data Environment
9. Change the Data Environment Name to dnvMainDataBase
10. Change Connection1 to cnnNorthWind
11. Right click the connection object.
12. Click Properties on the context menu
13. On the provider tab in the data link properties dialog box, click
Microsoft Jet 4 OLE DB Provider (or use whatever version is available.)
14. Click the Connection Tab
15. Click the button with the three dots on the Connections Tab.
16. Browse your hard drive until you locate the datbase that you want to
use- ie Nwind.mdb
17. Click OK.

Data Environment is now created (phew!!)

Recomended book:
Visual Basic 6 Database Programming for Dummies
ISBN: 0-7645-0625-0




"Justin" <baseline2600@yahoo.com> wrote:
>
>Wahoo,
>I am finally getting somewhere,
>As I suspected the OLE field that I was attempting to write to file was
infact
>a serialised object and not simply a binary file.
>
>I can store a Long Binary Object in an OLE field using the appendchunk method,
>thus storing a file in the database in its original format. ie: If I write
>an 8k jpg file to the database using appendchunk, then I am simply storing
>a whole heap of one's and zero's to total storage space of 8k! and not 512K
>as an OLE Photo Editor Object.
>
>When I use the GetChunk Method I am simply transferring those one's and
zero's
>back to a binary file. Thus maintaining the original format.
>
>There are a couple of issues to note:
>1. The file stored in the OLE field can no longer be accessed in Access
with
>an OLE control in the usual fashion as it is looking for an OLE Object and
>not a binary file.
>2. If the OLE field in the database has already been stretched by a larger
>object and you simply update the field with a smaller one then you will
get
>two end of file symbols in that binary field. When you write the field
back
>to disk the file size will be as large as the field. This should not affect
>things however as most programs will read the file until it reaches the
first
>end of file symbol. To ensure the database remains compact, I would suggest
>trying to delete the contents of the field first, then updating the field
>with new file.
>
>Additionally, the previous code issued in the last message had a few logic
>errors in it. Due to the way VB handles rounding in it's divisor and mod
>operators, inconsistencies with calculations in establishing the number
of
>blocks and the amount of data left over were found. I have resolved this
>problem by using integer division and doing away with the mod operator and
>using instead simple operators.
>
>The test application is as follows:
>I created one form with two buttons: Save and Load
>The save button saves a jpg image "Photo.jpg" to Andrew Fullers photo field
>in the Nwind database.
>The load button loads the Long Binary Object from Andrew Fullers photo field
>and saves it as "test.jpg".
>I created one data environment called "dnvMainDataBase"
>and one connection that points to the Nwind database "cnnNorthWind".
>
>My Code is as follows:
>Code Behind Form1 -
>
>Dim cnn As ADODB.Connection
>
>Private Sub cmdLoad_Click()
> Set cnn = dnvMainDatabase.cnnNorthWind
> Set rs = New ADODB.Recordset
> cnn.Open
> cnn.CursorLocation = adUseServer
> SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
> rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
> FileToBlob "c:\temp\photo.jpg", rs!Photo
> rs.Update
> MsgBox "The jpg file has updated Andrew Fullers photo"
> rs.Close
> cnn.Close
>End Sub
>
>Private Sub cmdSave_Click()
> Set cnn = dnvMainDatabase.cnnNorthWind
> Set rs = New ADODB.Recordset
> cnn.CursorLocation = adUseServer
> cnn.Open
>
> SQL = "SELECT * FROM Employees WHERE LastName='Fuller'"
>
> rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
>
> BlobToFile rs!Photo, "c:\temp\test.jpg"
>
> rs.Close
> cnn.Close
>End Sub
>
>Code Behind Module1-
>
>Option Explicit
>
>Sub BlobToFile(fld As ADODB.Field, _
> ByVal FName As String)
> '
> ' Assumes file does not exist and write access
> ' to the path is granted (Something to note with
> ' NT based systems}.
> ' Data cannot exceed approx. 2Gb in size.
> '
> Dim F As Long, bData() As Byte, sData As String
> F = FreeFile
> Open FName For Binary As #F
> If fld.Type = adLongVarBinary Then
> WriteFromBinary F, fld, fld.ActualSize
> Else
> MsgBox "This is not an OLE Field"
> End If
> Close #F
>End Sub
>
>Sub WriteFromBinary(ByVal F As Long, _
> fld As ADODB.Field, _
> FieldSize As Long)
> Dim Data() As Byte
> Dim NumBlocks As Long
> Dim LeftOver As Long
> Dim Ctr As Long
> NumBlocks = FieldSize \ 16384
> LeftOver = FieldSize - NumBlocks * 16384
> If NumBlocks > 1 Then
> For Ctr = 1 To NumBlocks
> Data = fld.GetChunk(16384)
> Put #F, , Data
> Next
> End If
> Data = fld.GetChunk(LeftOver)
> Put #F, , Data
>End Sub
>
>Sub FileToBlob(ByVal FName As String, fld As ADODB.Field)
> '
> ' Assumes file exists and can be accessed
> ' Assumes calling routine does the UPDATE
> ' File cannot exceed approx. 2Gb in size
> '
> Dim F As Long, Data() As Byte, FileSize As Long
> F = FreeFile
> Open FName For Binary As #F
> FileSize = LOF(F)
> If fld.Type = adLongVarBinary Then
> ReadToBinary F, fld, FileSize
> Else
> MsgBox "This is not an OLE field"
> End If
> Close #F
>End Sub
>
>Sub ReadToBinary(ByVal F As Long, _
> fld As ADODB.Field, _
> ByVal FileSize As Long)
> Dim Data() As Byte
> Dim NumBlocks As Long
> Dim LeftOver As Long
> Dim Ctr As Long
> NumBlocks = FileSize \ 16384
> LeftOver = FileSize - NumBlocks * 16384
> If NumBlocks > 1 Then
> For Ctr = 1 To NumBlocks
> Data = InputB(16384, F)
> fld.AppendChunk Data
> Next
> End If
> Data = InputB(LeftOver, F)
> fld.AppendChunk Data
>End Sub
>
>