David McAfee
davidmcafee at gmail.com
Thu Nov 20 11:43:24 CST 2014
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
>