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

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