Adding a new record to a table with an image/OLE object field

[PhilJeary]

Hi,

I have a table in SQL with an image data type field which can contain files.
I can manually add records to this in Access (field appears as OLE Object data type).

Im trying to add records to this field using VB/VBA.

Im attempting to add a record using the .addnew method. I've tried assigning a file to the field directly and i've tried assigning a file to an OLE object on a form (that bit worked) and then assigning that object to the field in question (that didnt).

I know the code is incorrect but this is what Im attempting:

With objRec
.AddNew
!CompanyID = 2
!email.CreateEmbed "C:\test.rtf", "package"
.Update
End With

Where email is the image field.

Any helps or ideas on where to look would be much appreciated.

 

[PhilJeary]

!email.CreateEmbed "C:\test.rtf", "package"
should have been
!email.CreateEmbed "C:\untitled.msg", "package"

I changed what I was using from a word document to an email.

 

[PhilJeary]

Alternatively, if somebody knows some SQL code to add records it would be much appreciated.

 

[CCLINT]

Some people use the ADO Stream, I use the ADO Append Chunk.

See thread709-391834 and thread222-541549

 

[PhilJeary]

I get the following error:

A problem occured while Microsoft Access was communicating with the OLE server

Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again in Microsoft Access.

I made a VB app and used the code below, it does put something there as the field is no longer emptpy, but I can't access it by double clicking on the field as I did with the ones I put there manually. Is it only accesible through code or has something gone wrong?

CODE:
Option Explicit
Private WithEvents rsADO As ADODB.Recordset
Private conn As ADODB.Connection

Private Sub form_load()
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
' conn.Open "Provider=MSDASQL;Driver={SQL Server};Server=BROMLEYSQL2000;Uid=tempweb10;Pwd=serve10tempweb;Database=CleanListDVLP"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0" _
& ";Data Source=" & "C:\texttester.mdb" _
& ";Jet OLEDB:Engine Type=5"
Set rsADO = New ADODB.Recordset
rsADO.CursorLocation = adUseServer
rsADO.CursorType = adOpenKeyset
rsADO.LockType = adLockOptimistic
rsADO.Source = "SELECT * FROM table1"
Set rsADO.ActiveConnection = conn
rsADO.Open , , , , Options:=adCmdText Or adAsyncFetch
End Sub

Private Sub Command3_Click()
SavePictToDb App.Path & "\g.bmp"
End Sub

Private Sub SavePictToDb(sPictFile As String)
Dim lSize As Long
Dim lOffset As Long
Dim bytChunkData() As Byte
Dim iFileNum As Integer
Dim sTempFile As String
iFileNum = FreeFile
Open sPictFile For Binary As #iFileNum
ReDim bytChunkData(FileLen(sPictFile))
Get #iFileNum, , bytChunkData
Close #iFileNum
rsADO.AddNew
rsADO("email&quot.AppendChunk bytChunkData
rsADO.Update
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
rsADO.Close
conn.Close
Set rsADO = Nothing
Set conn = Nothing
End Sub


Any help would be much appreciated. Regards,
Phil

 

[CCLINT]

It's a conflict with your Anti-Virus program. Look at the options settings there at what could be causing the problem, such as script blocking.

 

[PhilJeary]

Oh well, back to the drawing board!

Thanks for your help CCLINT.

Incidentally, my overall goal is to have a database with an internal email function which must support rich text (bold etc) and saving the email in a table.

I thought i'd have a go at storing a file, but i'm also having trouble with creating the file or capturing it from Outlook.

I think i'm going to start off simple with a basic form, a send function, and storing the message in a memo field and work up from there.

The system must support Lotus Notes as well as the MS products, just to make it a bit more difficult!

Any ideas on tutorials of how to do this, or addons that can be bought would be much appreciated. 4 days of trawling books and the internet has deflated the enthusiasm a bit!

 

[CCLINT]

Please start a new thread on this in order to keep the topics seperated.

 

[CCLINT]

>It's a conflict with your Anti-Virus program. Look at the options settings there at what could be causing the problem, such as script blocking.

PhilJeary, did you verify this?
What happens when you turn off script blocking?

> but I can't access it by double clicking on the field as I did with the ones I put there manually

Isn't the prupose to display the data in your VB program and not MS ACCESS DB window?

So, if you extract the data from the DB using the ADO Stream or GetChunk, what does the data look like then?

I may have mis-understood something you mentioned here....

 

[PhilJeary]

I tried turning my virus scanner off completely but the message was the same.

I tried in Access just for test purposes as thats one of the places where i'd be opening the history files from, however, I tried the code version of retrieving the info and got:

Invalid Picture

Just for info, in the field when open in Access it had "Long binary data" written in the box. When adding files manually it had the document type or package.