Results 1 to 10 of 10
  1. #1
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7

    MS Access 2010 VBA - Upload to SharePoint 2013 Doc Lib

    I have written VBA code that will upload a document to SharePoint 2010/2013. Then I get the record via ADODB using the document Name and update the metadata. This all working great, however this created 2 revisions as Versioning is on, which is what I need. I would like to know how I can upload a docyment AND set the metadata in one transaction via MS Access VBA 2010?

    This is a sub-set of the code.

    Code:
    Set objXML = New XMLHTTP 'CreateObject("Microsoft.XMLHTTP")
    Dim bytBinary() As Byte
    Dim strSharePointUrl As String
    Dim strSharePointFileName As String
    Dim strFileName As String
    
    ReDim bytBinary(lngFileLength)
    Open strFullfileName For Binary As #1
    Get #1, , bytBinary
    Close #1
    
    ' Convert to variant to PUT.
    varBinData = bytBinary
    strTargetURL = strSharePointFileName
    
    ' Put the data to the server, false means synchronous.
    objXML.Open "PUT", strTargetURL, False ', username, password
    
    On Error Resume Next
    ' Send the file in. Note that the Name field in SharePoint is updated with the filename.
    objXML.send varBinData
    
    'IMORTANT: The WILDCARD is % and NOT * because this is a SQL Table and ADO Connection!
    rst.Open "SELECT * FROM [" & strListName & "] WHERE [Name] Like '%" &      strFileName & "%'", cnn, adOpenDynamic, adLockOptimistic
    
    'Note that Manditory fields will cause the List/Library Item to check Out the item
    'Not sure how to check in at the moment.
    If Not rst.EOF Then
    'Debug.Print "ID:" & rst("ID") & " Name:" & rst("Name")
    
    'SharePoint Item Update
    rst("Name") = strFileName
    rst("Title") = !Title
    rst("Originator") = !Originator
    etc....
    Thanks
    Dave

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What do you mean by uploading a document and setting the metadata?

    Your code creates a file and I assume then adds data from your recordset to that file or updates specific things about that file (name for instance). Unless you are using built in process creation of a file (xml is basically a text file) is always going to be split into two parts, creation and population. If you're using a built in process you might be able to do it as a single step (I've never exported to xml).

    Look at

    https://msdn.microsoft.com/en-us/lib.../ff193212.aspx

  3. #3
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7
    What I mean is I am uploading to a SharePoint Document Library. A SharePoint Doc Lib must have a document and metadata, i.e. fields used to store information about the document. When you do a manual upload to SharePoint via SharePoint you upload the Doc and then you are presented with the metadata form to fill in the fields you need. These fields can be required of not and can be any sort of data that is defined when the Document Library is created. This is all done in a single transaction so that one 1 revision is created. When you change the metadata or upload a new document, a new revision is created. The latest revision is always displayed, however you can always go into the Version History and change which open should be boss, hence making it the newest revision.

    What I am trying to accomplish is the uploading and changing of the metadata in a single transaction, via VBA. I don't want Version 0.1 and 0.2 when I upload for the first time. Another issue is that I can't upload the document by itself if any of the metadata fields are required. It will not accept it, which is even more reason I need to do it in one transaction.

    Hope this makes sense. Bottom line really is I need to find a Web Service or something where I can send the document and the metadata in one shot.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I haven't ever used sharepoint so I still have questions as we try to work through this.

    The basic gist of it is (as it stands)

    You upload the file, but the sharepoint information on your sharepoint table isn't being stored in the way you want, so after the upload happens you are having to modify the existing sharepoint record.

    What you actually want is to upload the file and have your vba code fill in the 'correct' metadata from the start.

    Is that correct?

  5. #5
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7
    No, that's not what I mean. The code is working as expected just fine. The problem is that I need to upload the Doc AND the metadata in ONE transaction. Right now I have to do it in TWO. I have to do it on ONE transaction so that I can avoid creating an additional Revision. I'm currently seeking a way of doing this. If you have never used SharePoint then I think you'll not be able to assist? I really need help from someone who has done this successfully in the past with MS Access 2010 and SharePoint 2010/2013.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The method you're using is is just manipulating a recordset which I've done ... hundreds of times.... I'm trying to help you work through it, not saying I have the answers at all, but the basic premise is an easy one. If the upload is occurring but creating a record on a table somewhere that has information you do not want, you can change it to what you want. If you have the appropriate permissions to the table. Do not get hung up on 'one transaction', you should (from what I've read pursuing this) be able to modify an existing sharepoint record to reflect what you want. And your code appears to be doing that (modifying the recordset), you just need to find the most recent record for the file you just uploaded and modify that record.

  7. #7
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7
    Thanks, but you have lost the point I am making. An understanding of SharePoint is a must for answering this question otherwise you will be going in circles. Thanks for your help anyway.

  8. #8
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Dave, I don't believe this is possible. I understand SharePoint's version control to be designed specifically to capture both content and metadata updates.

    There is no direct interface for loading a document with metadata from Access 2010.

    See: http://sharepointfieldnotes.blogspot...d-setting.html

  9. #9
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7
    Thanks for the response. I think I'm going to have to create a dll if I'm to do it in one shot.

  10. #10
    dave.stuart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    7
    I found the solution to my problem soon after I posted this, so 2 years later this is what I wrote. Hope it helps someone out there.
    I've included a few references and some comments that might also help someone one day too.

    Code:
                    ' http://stackoverflow.com/questions/3...repoint-doclib
                    
                    ' Find Front Page Server Extentions version here! Currently 15.0.0.4517
                    ' http://websrvstg:2015/_vti_pvt/service.cnf
                    ' Good Atricle found here: http://thuansoldier.net/?p=4298
                    
                    ' http://websrvstg:2015/_vti_pvt/service.cnf -> returns this
                    ' vti_encoding:SR|utf8-nl
                    ' vti_extenderversion:SR|15.0.0.4517
                    
                    ' http://websrvstg:2015/_vti_inf.html -> View Source gets this
                    ' <!-- FrontPage Configuration Information
                    ' FPVersion = "15.00.0.000"
                    ' FPShtmlScriptUrl = "_vti_bin/shtml.dll/_vti_rpc"
                    ' FPAuthorScriptUrl = "_vti_bin/_vti_aut/author.dll"
                    ' FPAdminScriptUrl = "_vti_bin/_vti_adm/admin.dll"
                    ' TPScriptUrl = "_vti_bin/owssvr.dll"
                    ' -->
                    ' http://webdesign.about.com/library/b...ding_table.htm
                    ' %3a as a Colon :
                    ' %2f is a slash /
                    ' %5f is unserscore _
                    ' %7C is Pipe |
                    ' %3b is semi colon ;
                    ' %3bSW%7c is ;SW|
                    
                    ' Adding Metadata explained below.
                    ' Inside the above script, the metadata "vti_title" is added.
                    ' You can add further metadata properties, by adding them inside the meta_info=[...] brackets.
                    ' E.g. ";meta_info=[vti_title%3bSW%7c" + Escape(title) + ";project%3bSW%7c" + Escape(project) + "]]".
                    ' You must have a custom field called project in the document library.
                    ' "SW" for String
                    ' "BW" for Boolean (true/false)
                    ' "IW" for Integer
                    ' "TR" for Date. Must use dd mmm yyyy hh:nn:ss format even if it's a Date Only field
                    
                    strMetaData = "vti_title" & "%3b" & "SW" & "%7c" & URLEncode(!Title, True) 'FixStringForSQL(strFileName)
                    strMetaData = strMetaData & ";Originator" & "%3b" & "SW" & "%7c" & URLEncode(!Originator, True)
                    strMetaData = strMetaData & ";Project" & "%3b" & "IW" & "%7c" & CStr(m_colProjects.Item(Trim(!ProjectNumber)))
                    
                    If !TransmittalNumber <> "" Then
                        strMetaData = strMetaData & ";Transmittal+Number" & "%3b" & "SW" & "%7c" & URLEncode(!TransmittalNumber, True)
                    End If
                    
                    If Len(Nz(!TransmittalDate, "")) > 0 Then
                        strMetaData = strMetaData & ";Transmittal+Date" & "%3b" & "TR" & "%7c" & Format(ToUTC(Trim(!TransmittalDate)), "dd mmm yyyy hh:nn:ss")
                    End If
                    
                    strMetaData = strMetaData & ";Vendor+Data+ID" & "%3b" & "SW" & "%7c" & URLEncode(!VendorDataID, True)
                    strMetaData = strMetaData & ";Facility+Code" & "%3b" & "IW" & "%7c" & CStr(m_colFacilityCodes.Item(!FacilityCode))
                    
                    If !DocumentType <> "" Then
                        strMetaData = strMetaData & ";Document+Type" & "%3b" & "IW" & "%7c" & CStr(m_colDocumentTypes.Item(!DocumentType))
                    End If
                    
                    strMetaData = strMetaData & ";Document+Revision" & "%3b" & "SW" & "%7c" & !DocumentRevision
                    
                    If Nz(!RevisionDate, "") <> "" Then
                        strMetaData = strMetaData & ";Revision+Date" & "%3b" & "TR" & "%7c" & Format(ToUTC(!RevisionDate), "dd mmm yyyy hh:nn:ss")
                    End If
                    
                    strMetaData = strMetaData & ";Approval+Status" & "%3b" & "SW" & "%7c" & !ApprovalStatus
                    strMetaData = strMetaData & ";PO+Number" & "%3b" & "SW" & "%7c" & URLEncode(!PONumber, True)
                    strMetaData = strMetaData & ";PO+Issuer" & "%3b" & "SW" & "%7c" & URLEncode(!POIssuer, True)
                    
                    If Nz(!TagNumber, "") <> "" Then
                        strMetaData = strMetaData & ";Tag+Number" & "%3b" & "SW" & "%7c" & URLEncode(Nz(!TagNumber, ""), True)
                    End If
                    
                    strMetaData = strMetaData & ";Cross+Reference" & "%3b" & "SW" & "%7c" & URLEncode(Nz(!CrossReference, ""), True)
                                                
                    strHeader = "method=put+document%3a" & str_vti_extenderversion & _
                    "&service_name=%2f" & _
                    "&document=[document_name=" & FixStringForSQL(Replace(strListName, " ", "") & "/" & strFileName) & _
                    ";meta_info=[" & strMetaData & "]]" & _
                    "&put_option=overwrite,createdir,migrationsemantics" & _
                    "&comment=" & _
                    "&keep%5fchecked%5fout=false" & vbLf
                    
                    ByteArray = StringToByteArray(strHeader)
                    
                    Set Stream1 = New ADODB.Stream
                    Stream1.Open
                    Stream1.Type = adTypeBinary
                    Stream1.Write ByteArray
                    
                    Set Stream2 = New ADODB.Stream
                    Stream2.Open
                    Stream2.Type = adTypeBinary
                    Stream2.LoadFromFile strFullfileName
                    Stream2.CopyTo Stream1
                    Stream1.position = 0
                    Stream2.Close
                    
                    strTargetURL = strSharePointFileName
                
                    ' Upload the data to the server, false means synchronous.
                    Set objXML = New MSXML2.XMLHTTP
                    objXML.Open "POST", strSharePointUrl + "/_vti_bin/_vti_aut/author.dll", False
                    objXML.setRequestHeader "Content-Type", "application/x-vermeer-urlencoded"
                    objXML.setRequestHeader "X-Vermeer-Content-Type", "application/x-vermeer-urlencoded"
                    objXML.setRequestHeader "User-Agent", "FrontPage"
                    
                    On Error Resume Next
                    objXML.send Stream1
                    
                    Stream1.Close
                    
                    If Err.Number <> 0 Then
                        'Release the handle on the files
                        Set objXML = Nothing
                        Set Stream1 = Nothing
                    
                        ErrorNumber = Err.Number
                        ErrorMessage = Err.Description
                        .Edit
                        !ErrorMessage = !ErrorMessage & "File:" & strFileName & " (" & Format(dblFileSize, "#,###") & " KB) Error:" & ErrorMessage
                        .Update
                        g_rstADODB.Close
                        GoTo ExitHereVendorData
                    End If
                    On Error GoTo ErrorHandler
                    
                    'Debug.Print "Upload: " & objXML.Status & " " & objXML.StatusText '& " :: " & objXML.responseText
                    If objXML.Status = 200 Then
                        If InStr(objXML.responseText, "successfully") = 0 And Len(objXML.responseText) > 0 Then
                            If objXML.responseText <> "" Then
                                .Edit
                                !ErrorMessage = !ErrorMessage & "File:" & strFileName & " Status:" & objXML.Status & " XML Error:" & objXML.responseText
                                .Update
                            
                                g_rstADODB.Close
                                GoTo ExitHereVendorData
                            Else
                                .Edit
                                !ErrorMessage = !ErrorMessage & "File:" & strFileName & " Status:" & objXML.Status & " Error: No Error returned. Check File in SharePoint!"
                                .Update
                                'Carry on!
                            End If
                        Else
                        
                            ' Check In Document whether it needs it or not!
                            strHeader = "method=checkin+document%3a" & str_vti_extenderversion & _
                            "&service_name=%2f" & _
                            "&document=[document_name=" & FixStringForSQL(Replace(strListName, " ", "") & "/" & strFileName) & _
                            "&comment=MS Access Check In" & _
                            "&keep%5fchecked%5fout=false" & vbLf
                            
                            Set objXML = New MSXML2.XMLHTTP
                            objXML.Open "POST", strSharePointUrl + "/_vti_bin/_vti_aut/author.dll", False
                            objXML.setRequestHeader "Content-Type", "application/x-vermeer-urlencoded"
                            objXML.setRequestHeader "X-Vermeer-Content-Type", "application/x-vermeer-urlencoded"
                            objXML.setRequestHeader "User-Agent", "FrontPage"
                            objXML.send strHeader
                            
                            'Debug.Print "CheckIn: " & objXML.Status & " " & objXML.StatusText
                            
                            'At ths point we know the SharePoint Metadata was updated so update the temporary record
                            .Edit
                            !MetadataUpdated = True
                            !UpdateDate = Now
                            .Update
                    
                        End If
                        
                    Else
                        .Edit
                        !ErrorMessage = !ErrorMessage & "File:" & strFileName & " Status:" & objXML.Status & " XML Error:" & objXML.responseText
                        .Update
                        g_rstADODB.Close
                        GoTo ExitHereVendorData
                    End If
                    
                    'Clear Memory
                    Set objXML = Nothing
                    Set Stream1 = Nothing
                    Set Stream2 = Nothing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-13-2015, 02:14 PM
  2. Slow upload from Access to SharePoint
    By PJ Crittenden in forum SharePoint
    Replies: 2
    Last Post: 09-19-2014, 09:34 AM
  3. Replies: 0
    Last Post: 08-11-2014, 12:25 PM
  4. Replies: 7
    Last Post: 10-28-2013, 08:49 AM
  5. Access 2013 Web App with Sharepoint 2013
    By miguel.escobar in forum Access
    Replies: 7
    Last Post: 06-17-2013, 09:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums