SQL problem 2

[Guest_imported]

I have a table on MS Access where one of the fields is an OLE object.
Now I have a VB form with an OLE object embedded and I need to insert the contents of that OLE object to my table on MS Access.
How can I do this? Let's say my OLE obj on the VB form is called OLE1 and my table Table1. I think I could do something like:

DB.Execute "INSERT INTO Table1(Myfield) VALUES(' & OLE1.Object & "')"

But it gives me an error saying "Object doesn't support this property or Method". How can I do this, anyone knows what am I missing here?

Thanks.

 

[NipsMG]

Thanks! s-)


--NipsMG

 

[mhm]

Hi Experts,

Would you be kind enough to tell me what I'm missing in order for me to save a Bitmap Image into an OLE Object field.

Here's my problem. I'm using VB6 with ADO 2.6 & Access2000. When I try to save the corresponding value of "C:\Pictures\Pic00001.JPG" using Stream or AppendChunk into an OLE Object Field, the saved value is always Long Binary Data. What I need is to save the picture as a Bitmap Image in an OLE Object field so then I can print the picture using CR8.

I'll be waiting for your inputs. Thank you.

 

[NipsMG]

MHM,

Interesting..

As I understand it, you want to use the OLE Object Field like a normal field, dropping it in the report and having the image display in it. I'm not sure it can be done that way.

The only thing I can suggest, is to use the CR8 Report Design Component in Visual Basic. Retrieve the image at runtime using the StreamObject, save it to file, then point the RDC to it, and generate the report.

--NipsMG

 

[mhm]

NipsMG,

Thanks for the prompt reply.

You're right. I really wanted to use the OLE Object Field just like an ordinary field. Drop it in the report and viola! it should print the picture for each record. I just wonder why we can't do this?

As an example, when I'm in Access and inserted a .JPG file in an OLE Object Field, Access saved it as a bitmap and I can also print the OLE Object Field in CR8. That's neat.

In this scenario, if Access is able to save the .JPG file as bitmap, why can't we do it in VB? Any Idea?

My concern now is saving the .JPG in an Access OLE Object Field as a Bitmap not as Long Binary Data.

Help! Thanks again.

 

[NipsMG]

That's interesting..

Have you explored using the Access Object Library? I imagine there's got to be a method to store OLE objects. The only downfall to this, I BELIEVE, is that access needs to be installed on all computers you distribute this too (Don't quote me on this). This isn't really a problem if you're not trying to develop a distributable application.

-- NipsMG

 

[mhm]

NipsMG,

Thanks. I did explore Access Object Library already and I didn't find any object that is suitable to my requirement. Any tips?

To give you a background, the Access 2000 Database is stored in a File Server as Back-End Database, and I'm using VB6 as the Front-End. It's a Client/Server application.

I only manually inserted .JPG file in an Access OLE Object Field as an example to show the capability of Access in storing such image as a Bitmap (again, not Long Binary Data). In reality, my Access database does not permanently store any Bitmap image but instead its full path and filename which redirect to the actual image upon invoking VB Function - "loadpicture" (within VB Form). It's a text field anyhow, that's why there's no way of printing the actual image using the same field.

My logic is... in order to print the actual images instead of its path and filename, I created a temporary table that has an OLE Field, then save the actual image into the OLE Field. In this case, I can just drop the OLE Field unto the CR8 then presto... It's suppose to print. And... once it is printed, I'll just delete all the records in the temporary table before the succeeding prints.

To my dismay however, when I tried to save a .JPG file into an OLE Field either by using Stream of AppendChunk, the data is saved in a format of Long Binary Data (not Bitmap). If I can only use VB6 to save .JPG in an Access OLE field as a Bitmap, my headaches will be over. Help!

 

[mhm]

I'm still having headache because of this OLE Field problem. Please extend your help. Thanks in advance.

 

[NipsMG]

Got it!

*Pats self on back* s-)


Create a 32-bit ODBC DSN that points to your access database. (In win98, it's in ControlPanel.. in win2k it's in Administrative Tools)


Add a Microsoft Remote Data Control to a form.
Add an OLE Object.
Add a TextBox called txtFileName.
Create a button captioned "Load Picture" named cmdOpen


Point MSRDC1 to the Access DSN.
Bind the txtFileName box to the field in your table that holds the filename

Bind the OLE control to the OLE field in your database

Put the following code in cmdOpen_Click()

    OLE1.Action = 14
    txtFileName = OLE1.SourceDoc

Setting Action to 14 will force the OLE control to bring up an "Insert Object Dialog Box" from which you can select any kind of OLE Object and it will insert it into the control.. And.. since the control is bound to the database, will insert that object into the database itself. The filename will be updated also as txtFileName is also bound.

This worked for me using VB6sp3 and Access2000.

Let me know if you have any more questions.

--NipsMG s-)

 

[mhm]

Dear NipsMG,

Thanks a lot for the prompt reply. I'll test it.

However, my problem is that... I am using a disconnected database in N-tier application. I'm using Class Modules within an ActiveX Project (it's DLL). I used this class to connect to database and at the same time manipulate the records set using SQL statements such as UPDATE, INSERT, SELECT, etc.

For example: (function to save record)

Public Function Add_Picture(EmpID As Long, EmpPicture As String) As Boolean
Dim strSQL As String

On Error GoTo BadInsert

strSQL = "INSERT INTO TempTable (EmpID, EmpPicture)"
strSQL = strSQL & " VALUES"
strSQL = strSQL & "(" & EmpID & ", "
strSQL = strSQL & "'" & EmpPicture & "')"

'cnEmployee is an ADODB variable used to connect to database.
cnEmployee.Execute strSQL

Add_Picture = True
Exit Function

BadInsert:
Debug.Print Err.Number & Err.Description
Add_Picture = False
Err.Clear
End Function

My objective is to be able to insert a picture in an Access's OLE Field instead of it's filename so then I can print it straight from CR. What I did was, I changed the EmpPicture Field into OLE Field, and tried using AppendChunk and/or Stream, yet it still saves as Long Binary Data. I need it to be saved as Bitmap.

Users are not required to insert new picture everytime they print. When printing, VB should automatically get all the path and filenames (Text Field) from the main table which correspond to the actual pictures then save each actual picture unto OLE Field of TempTable as Bitmap, then print using CR.

I know there must be a solution to this.
Thanks a lot. Hope to hear from you soon.

 

[No1MrEd]

Dear NipsMG, In August you wrote


OLE1.Action = 14
txtFileName = OLE1.SourceDoc


I am trying to get a complete list of the OLE.Action values and what they accomplish. Where can I find the list?

Thanks in advance

 

[No1MrEd]

I found the list I was asking for last month. Thought I would post it for the benefit of others.

http://msdn.microsoft.com/library/

en-us/off2000/html/acproAction.asp?/frame=true