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

Thanks but for my project, it's ACCESS OR NOTHING in this prototype,
because I can create and modify an Access backend with nothing but dll's
that come with Windows.

So far no one seems to have a solution with ADO stream for MS Access?
On Nov 19, 2014 11:25 PM, "Doug Murphy" <dw-murphy at cox.net> wrote:

> Bill,
>
> I did this for a client who did directed that the documents would be stored
> in a sql server back-end. They originally were using the document fields in
> an accdb back end but it was slowwwwww, and growing rapidly.
>
> Following is the procedure that did the storage of a file. We use it for
> all
> kinds of files, just need to save the name and file type extension so the
> file can be rehydrated when they want to retrieve
>
> Doug
>
> ----------------------------------------------------------------------------
> -------------------------------
>
>
>
> '---------------------------------------------------------------------------
> ------------
> '
> Public Sub subFileStorage(sPath As String, lProjID As Long, iDocumentType
> As
> Integer)
>     Dim cn As New ADODB.Connection
>     Dim cnStr As String
>     Dim rs As New ADODB.Recordset
>     Dim sT As New ADODB.Stream
>     Dim sDocName As String
>      On Error GoTo subFileStorage_Error
>
>      'Work with the document, get name parts and load stream
>      sDocName = Mid(sPath, InStrRev(sPath, "\") + 1)
>      Call subDocInfo(sDocName)
>      sT.Type = adTypeBinary
>      sT.Open
>      sT.LoadFromFile sPath
>
>      'PUT IN ACTUAL SERVER AND DATABASE NAMES.
>      'Open the connection to sql server
>      'cnStr = "Provider=sqloledb;Data Source=2003.local;Initial
> Catalog=TestDatabase_beSQL;User Id=xxx;Password=xxxxx;"
>      cnStr = "Provider=sqloledb;Data Source=2003.local;Initial
> Catalog=RLProjectDatabase_beSQL;User Id=xxx;Password=xxxxx;"
>      'USE A TRUSTED CONNECTION
>      'cnStr = "Provider=sqloledb;Data Source=myServerAddress;Initial
> Catalog=myDataBase;Integrated Security=SSPI;"
>      cn.Open cnStr
>
>      'Open the recordset
>      rs.Open "SELECT DocumentName, DocumentExtension, DocumentTypeID,
> Document, ProjectID FROM tblDocuments", cn, adOpenForwardOnly,
> adLockOptimistic
>      rs.AddNew
>      rs.Fields("DocumentName") = sDocumentName
>      rs.Fields("DocumentExtension") = sFileExtension
>      rs.Fields("DocumentTypeID") = iDocumentType
>      rs.Fields("ProjectID") = lProjID
>      rs.Fields("Document").Value = sT.Read
>      rs.Save
>
> subFileStorage_Exit:
>    On Error GoTo 0
>    sT.Close
>    rs.Close
>    cn.Close
>    Set sT = Nothing
>    Set rs = Nothing
>    Set cn = Nothing
>    Exit Sub
>
> subFileStorage_Error:
>
>     MsgBox "Error " & Err.Number & " (" & Err.Description & " at Line
> Number
> = " & Erl & ") in procedure subFileStorage of Module basDocumentStorage"
>     Resume subFileStorage_Exit:
>     Resume
> End Sub
>
> ----------------------------------------------------------------------------
> -------------------------------
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Wednesday, November 19, 2014 2: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
>