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

Dan can you give examples of problems and / or give reasons you feel that
this will be difficult? Does unpacking the files from the dbms cause data
loss or corruption? Any more so than disk i/o, user error (I remember
deleting the wrong files every now and then).

I can envision the database size limits for example, constraining how many
documents can be stored. Also, I think while the code works for sql server,
I may not find a way to get it done using ADO and an Access backend.
What other problems are you concerned with?

I have tried DAO + VBA and have had no success either so far, even though
code samples indicate that should be productive. I have tried ADO and oleDB
field type, and that doesn't "fail" but is resulting in an empty field in
the backend. I am starting to wonder whether something is going on. If I
put the path in windows explorer, the file opens fine - so I cannot imagine
it having anything to do with the path unless, having spaces within it.
However, I have tried both these

rstAttachment.Fields("FileData").LoadFromFile = strPathDoc

and

rstAttachment.Fields("FileData").LoadFromFile = Chr(34) & strPathDoc &
Chr(34)

neither of which work.

The ADO Stream method doesn't have any problem reading to populate the
stream, it is after assignment to the oleDB field that I am seeing nothing
in the backend, despite that the record is added.


On Nov 19, 2014 7:36 PM, "Dan Waters" <df.waters at outlook.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>