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]

chuwa:

You need to accomplish this using the ADO GetChunk/AppendChunk method..

Here is a link to some source demonstrating this technique.

Hope this helps!

http://support.microsoft.com/support/kb/articles/q194/9/75.asp

--NipsMG

 

[Guest_imported]

Thanks for your reply.
I took a look at that code and that seems to be the way. The problem is that now everytime I make a rs.Update after I made a rs.AddNew the program encounters a runtime error, so I have no way to see if this is working.
Maybe I need to do something else when I use the AddNew method on a recordset...
Any idea on why this is happening?

 

[Guest_imported]

Thanks for your reply.
I took a look at that code and that seems to be the way. The problem is that now everytime I make a rs.Update after I made a rs.AddNew the program encounters a runtime error, so I have no way to see if this is working.
Maybe I need to do something else when I use the AddNew method on a recordset...
Any idea on why this is happening?

 

[NipsMG]

It totally depends on what properties your recordset has.

For updates, I USUALLY do this

Dim rs as new adodb.Recordset

rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic

rs.Open <sql statement>, <connection variable>

Your recordset may be defaulting to adLockReadOnly in which case you would get a runtime error.. Also, are you using a Connection or a Command to get your recordset? I find the only way I'm ever able to update a recordset is if I'm using a command object:

  dim cmdCommand as new adodb.Command
  cmdCommand.CommandType = adCmdTable
  cmdCommand.CommandText = "TABLE_NAME"
  cmdCommand.ActiveConnection = <connection object>
  set rs = cmdCommand.Execute

or by using a recordset's Open method:

  rs.Open "select * from MY_TABLE", cnnConnection

Let me know if this is of any help.

--NipsMG

 

[Guest_imported]

Well, I was doing just that way, using rs.Open String_SQL, connection and the cursorType and LockType properties were also the same, so I'm really not seeing where the problem is.
At least I've managed to see if the getChunk/AppendChunk methods were working so I did write to my MS Access table, but I had to open an existing record and write on that one. So far I'm not able to add new records to my table by code, but I now I can insert ole obj on fields.

I'm going to make some more experiences with the AddNew method to see if I can figure whats going on.

Thanx for your help on the AppendChunk I was way far from that one

 

[Guest_imported]

Well, I'm not managing to work with this preety well...
I mean I can use AppendChunk to append data from a file created with the getchunk method. But what I need is to insert on a table field an OLE obj that is on my form. I suppose the way to do this would beto use getchunk method to write to a file and the read from that file to the table field right? I suppose there is no way of directly inserting an embedded OLE obj on a table field is there?
So the deal is... how can I use the getchunk method to create a file from my embedded OLE obj data because I can find no way of making this work.

Any help is appreciated.

 

[NipsMG]

Ok I believe I know the answer to this one.

Declare a byte array.

Dim bytes() as byte

Then use the getchunk method and store the result in a byte array.

bytes = rs.getchunk()

Open a file....

Open <yourfilename.ext> for binary access write as #1.

Write the contents of the byte array to the file....

Put #1, ,bytes()

Close #1

This *should* work correctly.. I know this is the basic concept, however I'm not sure if you're required to supply getchunk() with the amount of bytes to return.. If you are, I'm pretty sure you can pass it rs.fields(i).actualSize or something to that effect.

I hope this helps!

--NipsMG

 

[daimaou]

hmm... I think he is not trying to write from a recordset to a file. So that code wouldn't work and I believe getchunk will only work to convert recordset field contents (Like OLE or memo fields) to a file. I'm not sure if you can use getchunk to write the content of an embedded OLE into a binary file, but I think its not possible, at least not directly.
I also tryed to do this, but I opted to store the OLE content on .OLE files (with the SaveToFile method) and on the recordset I store the path to those files.
Anyway I'd still be interested on knowing how to do this...

 

[NipsMG]

Well, if you can store the OLE content on .OLE files.. then you can write the .OLE files to the database using getchunk()/AppendChunk()


--NipsMG

 

[Guest_imported]

Hi NipsMG. Thanx for getting back on me again. The code you've written is basically what I've done so far, and that will work to write the content of a recordstet field(OLE object) into a long binary file. But that's not exactly what I want... I mean, I don't have the OLE obj on a recordset, I have my OLE obj embedded on one of my forms and I need to write it's content into a recordset.
I suppose I must first write it into a file(with getchunk maybe) and them read the file to the recordset(with the appendchunk). The problem is how do I write the contents of an embedded OLE obj into a file? Or is it possibly to write directly from the embedded OLE obj to the recordset field?

I think the problem is that the getchunk method can only apply to a field type variable, so doing something like OLE1.object.getchunk won't work. Maybe I could make a cast to OLE1.object to be a ADODB.field type, but I'm not sure if it's possible to do this.

Any ideas?

 

[daimaou]

NipsMG you can't use the AppendChunk method to append a .OLE file to a recordset it just won't work because the format it's stored. Appendchunk needs a file created by getchunk. You can't use appendchunk with a file created by the SaveToFile method, it simply won't work. I've been there myself
But honestly I think this should be a easy thing to do I don't know why this gets to be so complicated...

 

[NipsMG]

Ok Ok here it is.. FINALLY...

You're both right.. you can't use the GetChunk method to accomplish this.. but.. you can use the ADO Stream Object!

In Your Code Module:

Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Public Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long

Public Function GetTempFilePath() As String
    Dim sPath As String, sTempFile As String
    Dim Result As Long, sTempPrefix As String
    
    'Put what you want your temp file prefix to be
    sTempPrefix = "AppName"
    
    'initialize the string and get the temp path
    sPath = Space(255)
    Result = GetTempPath(255, sPath)
    
    'strip Whitespace & null char off of path
    sPath = Trim(sPath)
    sPath = Mid(sPath, 1, Len(sPath) - 1)
    
    'Get the full path & file name
    sTempFile = Space(255)
    Result = GetTempFileName(sPath, sTempPrefix, 0&, sTempFile)
    
    'Strip Whitespace & null char off of temp filename
    sTempFile = Trim(sTempFile)
    sTempFile = Mid(sTempFile, 1, Len(sTempFile) - 1)
    
    GetTempFilePath = sTempFile
End Function

Public Function SaveFileToDB(ByVal sFileName As String, ByRef rstRecordset As ADODB.Recordset) As Boolean
  On Local Error GoTo ProcError
    'Initialize Stream Object
    Dim sStream As New ADODB.Stream
    sStream.Type = adTypeBinary
    rstRecordset.AddNew
    sStream.Open
    sStream.LoadFromFile sFileName
    rstRecordset.Fields("MyFile").Value = sStream.Read
    'update the database
    rstRecordset.Update
    sStream.Close
    'clear the TempFile
    Kill sFileName
    SaveFileToDB = True
ProcExit:
    Exit Function
ProcError:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Error!"
    SaveFileToDB = False
    GoTo ProcExit
End Function

Public Function GetFileFromDB(ByVal rstRecordset As ADODB.Recordset) As String
    On Local Error GoTo ProcError
    Dim sStream As New ADODB.Stream

   'GetTempFilenmae
    sTempFileName = GetTempFilePath()
    
   'Save data to file
    sStream.Type = adTypeBinary
    sStream.Open
    sStream.Write rstRecordset.Fields("MyFile").Value
    sStream.SaveToFile sTempFileName, adSaveCreateOverWrite
    sStream.Close
    GetFileFromDB = sTempFileName
ProcExit:
        Exit Function
ProcError:
    'MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Error!"
    MsgBox Err.Number & ": " & Err.Description
    GoTo ProcExit
End Function

In your form:

Private rstRecordset As New ADODB.Recordset
Private cnnConnection As New ADODB.Connection

Private Sub cmdGetFromDB_Click()
    sTempFileName = GetFileFromDB(rstRecordset)
    Open sTempFileName For Binary Access Read As #1
    OLE1.ReadFromFile 1
    Close #1
End Sub

Private Sub cmdSaveToDB_Click()
    Dim sTempFile As String
    sTempFile = GetTempFilePath
    
    Open sTempFile For Binary Access Write As #1
    OLE1.SaveToFile 1
    Close #1
    
    SaveFileToDB sTempFile, rstRecordset
End Sub

Private Sub Form_Load()
    cnnConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\db2.mdb"
    cnnConnection.Open
    
    rstRecordset.LockType = adLockBatchOptimistic
    rstRecordset.CursorType = adOpenKeyset
    
    rstRecordset.Open ("select * from Table1"), cnnConnection
End Sub

You're welcome..

--NipsMG

 

[NipsMG]

NOTE:.. You can do this with ANY TYPE OF FILE.......

 

[daimaou]

NipsMG nice going, I never thought on that one. But I can't make that work here, since it's not recognizing the ADODB.Stream type... I'm using a reference to ActiveX data objects 2.1, I know the latest is 2.6 I think.
Please let me know wich one have you used(Have you checked if this is working btw?). Maybe it will need a version higher than 2.1?
I think I should get service pack 5 if this is so.

 

[NipsMG]

the Stream object was implemented in ADO2.5 (Sorry, I should have mentioned that...)..

go to

http://www.microsoft.com/data/

and download Mdac 2.5 or 2.6

Then it will work for you.

--NipsMG

 

[daimaou]

Hey there!
Well, I got Mdac 2.6 and now the stream object is recognized , but... it doesn't work at all. Everytime I try to make a Save to DB I get an error on line:

OLE1.SaveToFile 1

Saying that the file couldn't be open, though everything seems ok, I mean I don't see any particular reason for this not to work, except that I'm not sure you can use the saveToFile method to write into a file opened for binary access writing. What is even more strange is that if you don't edit the OLE object and press the SavetoDB button it won't give any error, will go through all the code, but when you check the DB there's no record inserted in it...
This sure sounds strange. Any idea why this isn't working for me at all?

 

[NipsMG]

Uhmm.. unless you can't write to your temp directory..... no

Maybe for some reason the GetTempPath and GetTempFileName API Functions aren't working?

Either that or the Binary Access Write is causing you problems.. I actually just tried:

Open "C:\temp.ole" for output as #1
ole1.savetofile 1
Close #1

and that seemed to work.. Other than that, I'm not quite sure... it worked fine here for me...
--NipsMG

 

[daimaou]

Ok, I actually found something...
The GetTempPath and GetTempFileName API are working ok, I found that if I use:

Open "C:\temp.ole" for output as #1
or
Open sTempFile For Binary Access Write As #1

I couldn't write into the file(getting that error message) so I tryed and used only

Open sTempFile For Binary As #1

And now it seems to be working ( this is preety strange and I really have no explanation for this one, but anyway...).

The problem now is that I can't insert the object on the table.
I mean, the SaveFileToDB function is called and all the code runs without error, but when I do rstRecordset.AddNew it simply won't add a new record to the table, maybe I'm not declaring rstRecordset well, I don't know, but the strange part is that I don't get any error messages, the code just runs from beginning to end with no prob, the temp file is created, but data isn't inserted on the table. Why would this happen?

 

[daimaou]

Ok I think I got it now. I've been playing around with the locktype property of the recordsets and I managed to make it work I think. For what I've tested by using the adLockBatchOptimistic this won't work because the code won't add the new records to the table. I've changed this the adLockOptimistic and changed the Jet OLEDB drivers and now it seems to be working preety well. I will make a couple more tests, but I think now it's finally working.

Nice work on this one NipsMG.