DAO OLE Object vs. ADO and SQL BLOB

Question

DAO OLE Object vs. ADO and SQL BLOB

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
March 21, 2005 at 11:11 am

#66073

I upsized an Access DB to SQL. I knew one of the tables contained OLE Objects (Word docs). They upsized OK to SQL as BLOB's, I can even open them within the Access DB (linked tables to SQL).
The problem I'm having is the code that sets up a new Word doc in the database for a new Agency. The original DAO code (below) copies a template document from the "ProfileForms" table to a new row in the [Agency Info] table, then opens the document.
It doesn't work in the upsized database. I suspect that the DAO code that simply copies the BLOB field from the template to the new record doesn't work. The "[Doc].Action=acOLEActivate" urps with an "object is null or does not exist" error.
I've never worked with BLOB's in SQL. I always store docs in the files system and put pointers in the database. I can't rewrite this application to work that way in this case.
My other concern is that the target recordset is "Me.Recordset". Can I use ADO with this construct or am I limited to DAO because it is the data source for the form?
Can anyone give me equivalent code for ADO that will copy a BLOB field in a recordset row to a BLOB field in another recordset row? I Googled it but the only examples I find are File->BLOB and BLOB->File. The BLOB fields are the "Capis" and "EAP" fields.
TIA
Here's the DAO code:
Dim db As Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim PF As DAO.Recordset
Dim frm As [Form_Agency Info]
Dim loc As String
Set rst = Me.Recordset
Set PF = db.OpenRecordset("ProfileForms", dbOpenDynaset)
Set frm = Forms![Agency Info]
PF.MoveFirst
rst.FindFirst "[ID] = " & frm.ID
If rst.NoMatch = True Then
    rst.AddNew
    rst.Fields("ID") = frm.ID
    rst.Fields("Capis") = PF.Fields("Capis")
    rst.Fields("Doc") = PF.Fields("EAP")
    rst.Update

End If
[Doc].Verb = acOLEVerbOpen
[Doc].Action = acOLEActivate

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

Goto http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/BLOB.asp and have a look at the stream code.

Here's the ADO code:

Dim rst As ADOB.Recordset Dim PF As ADOB.Recordset Dim frm As [Form_Agency Info] Dim loc As String
Set rst = Me.Recordset Set PF = OpenRecordset "ProfileForms", CurrentProject.Connection, adOpenKeySet, adLockOptimistic Set frm = Forms![Agency Info]
PF.MoveFirst rst.Find "[ID] = " & frm.ID If rst.EOF Then
rst.AddNew rst.Fields("ID") = frm.ID rst.Fields("Capis") = PF.Fields("Capis") rst.Fields("Doc") = PF.Fields("EAP") rst.Update End If
[Doc].Verb = acOLEVerbOpen [Doc].Action = acOLEActivate

There isn't a lot of difference.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Thanks. THe article ref'd didn't really get into the use of Stream except for the usual getting data into and out of a BLOB. But you're saying ADO will copy a BLOB field to another BLOB field with no problem? I was going to give it a try yesterday just to see but our entire data center was down all day. We had other priorities.

This also clears up something else about which I was unsure, whether or not you could set an ADO Recordset to Me.Recordset.

Thanks.

DAO OLE Object vs. ADO and SQL BLOB

Cookies on SQLServerCentral