Bill Benson
bensonforums at gmail.com
Thu Nov 20 13:37:32 CST 2014
Thank you David, I will keep it in the back of my mind that you have this
solution - however, I was just working on a proof of concept... that giving
files a round trip between an Access backend and disk, using only VBA, was
doable.
I think I have shown myself it is not easily done, if doable at all.
On Thu, Nov 20, 2014 at 12:43 PM, David McAfee <davidmcafee at gmail.com>
wrote:
> I'm joining in late and missed the first part of this, but I also believe
> that storing links to files is the better way to do it.
>
> I think I have an adp (or maybe even an mdb) sample that I can send you
> that might help.
>
> To insert the attachment:
>
> 1. Grab a PK to relate it to (in one of my examples, I had to attach
> pics/pdfs to a machine ID).
> 2. Create a guid.
> 3. Copy the file to a secure place on the network (preferably unmapped) ,
> renaming it with the guid as the new name (and the old suffix).
> 4. Insert a record with a replated PK as the FK, the guid, the date/time is
> then stamped as well as the entry userID.
>
> I'm a bit tied up at the moment, but if you want I can dig it up, or make a
> new one.
>
> David McAfee
>
>
>
>
> On Thu, Nov 20, 2014 at 9:26 AM, Dan Waters <df.waters at outlook.com> wrote:
>
> > Hi Bill,
> >
> > As you mentioned, database size is a significant issue. Also, I would
> > worry
> > about two people opening the stored file at the same time, and they both
> > want to edit the document. This would have to be carefully tested to see
> > what happens and how you could counter any issues.
> >
> > I would also guess that the performance could be very slow - Access is
> not
> > the fastest kid on the block and a document would probably be much larger
> > than anything else.
> >
> > Overall, there is just the principle that it's best to let Windows do its
> > thing and handle files!
> >
> > If you get it working - good luck!
> > Dan
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> > Sent: Thursday, November 20, 2014 11:02 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [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
> > >
> > --
> > 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>