[AccessD] Insert an ADO Stream (or other) into MICROSOFT ACCESS OLE Object field

Hi Bill,

I've done this many times.  I do not keep the documents in the database, I
just keep the path to where the file is stored.  Storing and using the file
path is relatively easy.

I'm going to guess that what you're trying to do is difficult, and has a
potential for problems in production use.

HTH,
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Wednesday, November 19, 2014 16:56 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Insert an ADO Stream (or other) into MICROSOFT ACCESS OLE
Object field

I was toying with the idea of creating a backend document management
database in MS Access. I was going to zip files and then import them into
the database table with ADO, using the ADO Stream object.

All the below code (which doesnt have the ZIPPING code - but that will be
added later) runs fine, and there is no error message. But when I check the
contents of the OLE Object field in the record, it is empty.

Which makes me quite unhappy, I wrote a lot of code and built a userform
dialog assuming this was going to work.

I think these streams used to be able to be stored in dbMemo type fields - I
am sure I had at one time been successful.

I don't think I could even try to think of how to do this with ADO using an
Attachments field instead, since I dount ADO knows what that is. In any
case, I tried making the field type attachment in Access and the code did
not run - runtime error at the assignment of the .Value.

I don't think I can get this to run using inlineSQL ("Insert into blah blah"
because I don't know what I would use for the "Values ( )" clause.

Has anyone succeeded where I am failing?

Public Sub Upload()
Dim stmFileStream As New ADODB.Stream
Dim RS As New ADODB.Recordset
Dim oConn As ADODB.Connection
Const strPath = "C:\Users\wbenson\Documents\Development\Document Database
Project\Doc1.docx"
Set oConn = MyConn 'Trust me, this connection works

With stmFileStream
    .Open
    .Type = adTypeBinary
    .LoadFromFile strPath
End With

With RS
    .ActiveConnection = oConn
    .LockType = adLockOptimistic
    .CursorType = adOpenDynamic
    .Open "SELECT * FROM Revision Where 1 = 2"
    .AddNew
    .Fields("File").Value = stmFileStream.Read
    .Fields("Path") = strPath
    .Update
    .Close
End With
stmFileStream.Close
Set stmFileStream = Nothing
Set RS = Nothing
End Sub
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com