March 21, 2005 at 11:11 am
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
March 22, 2005 at 1:41 am
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 PF As ADOB.Recordset
Dim frm As [Form_Agency Info]
Dim loc As String
Set PF = OpenRecordset "ProfileForms", CurrentProject.Connection, adOpenKeySet, adLockOptimistic
Set frm = Forms![Agency Info]
rst.Find "[ID] = " & frm.ID
If rst.EOF Then
rst.Fields("ID") = frm.ID
rst.Fields("Capis") = PF.Fields("Capis")
rst.Fields("Doc") = PF.Fields("EAP")
rst.Update
End If
[Doc].Action = acOLEActivate
There isn't a lot of difference.
March 22, 2005 at 7:12 am
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