DAO OLE Object vs. ADO and SQL BLOB

  • 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

     

  • 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.

  • 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.

     

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

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