Dan Waters
df.waters at outlook.com
Thu Nov 20 11:26:59 CST 2014
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