• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

Save to Sharepoint List

I need an example of how to save a record to a Sharepoint List.  Can someone provide an example?

Thanks, Lynn
Sub SaveToSpList()
'
'---------------------------------------------------------------------------------------
' Procedure : SaveToSpList
' DateTime  : 12/28/2011
' Purpose   : Write out to (Save-to)a Sharepoint site in a List ("form" with fields)
' Parameters:
'             strSharepointConnection - a function that builds the Sharepoint connection
'                                       string.  Has parameters of it's own. See function
'                                       definition.
'---------------------------------------------------------------------------------------
    'Access table recordset
    Dim db As DAO.Database
    Dim AccessRs As New ADODB.Recordset
    Set db = CurrentDb
    
    'Sharepoint connection
    Dim SPCon As New ADODB.Connection
    
    'Sharepoint Recordset
    Dim SPRs As New ADODB.Recordset
    Dim SPFld As ADODB.Field
    Dim strSQL As String
        
      On Error GoTo SaveToSpList_Error
    
    'Get Access report record
    strSQL = "SELECT tblReport.* FROM tblReport"
    'open recordset holder for new Access report record
    Set AccessRs = db.OpenRecordset(strSQL)
    
    'make Sharepoint recordset
    strSQL = "SELECT * FROM list WHERE 1 = 2"
    SPCon = strSharepointConnection(strURL, strListID, 2)
    SPCon.Open
        
    SPRs.Open strSQL, SPCon, adOpenDynamic, adLockOptimistic
      
    'Are there any Access records?
    If Not AccessRs.BOF And Not AccessRs.EOF Then
        AccessRs.MoveFirst
    Else
       Exit Function
    End if   

    'Are there any Sharepoint records?
    If Not SPRs.BOF And Not SPRs.EOF Then
       'do nothing
    Else
       Exit Function
    End if   
     
        Do Until AccessRs.EOF
            SPRs.AddNew
                'Assign Access field values to fields of Sharepoint List
                SPRs.Fields(SPFld.[System]) = AccessRs.Fields("rSystem")     'fails here
                SPRs.Fields(SPFld.[System Status]) = AccessRs.Fields("rSystem_Status")
                SPRs.Fields(SPFld.[Report ID]) = AccessRs.Fields("rReport_ID")
                SPRs.Fields(SPFld.[Report Name]) = AccessRs.Fields("rReport_Name")
                SPRs.Fields(SPFld.[Report Status]) = AccessRs.Fields("rReport_Status")
                SPRs.Fields(SPFld.[Delivery Method]) = AccessRs.Fields("rDelivery_Method")

                Next SPFld ' next SP slot for a new report
            
            SPRs.Update
            
            AccessRs.MoveNext   'next new Report to send to SP
        Loop
        
        SPRs.Close
        SPCon.Close
        Set SPRs = Nothing
        Set SPCon = Nothing
    End If
    
    AccessRs.Close
    Set AccessRs = Nothing
    
    MsgBox "Done"
End Function

Open in new window

0
Lambel
Asked:
Lambel
  • 4
1 Solution
 
LambelAuthor Commented:
In the code above, on line 28, it doesn't compile because (I believe) there is something wrong
with the way I am referring to the Field in the sharepoint list.
0
 
LambelAuthor Commented:
Correction: Make that line 59 (not 28).
0
 
clarkscottCommented:
I deal with Sharepoint Lists in access... but I created an accdb and linked to the Sharepoint files (just like an Access table).
Instead of connecting to the Sharepoint file directly, I simply access the Access.accdb and deal with the "linked table" just like as if it were Access.

Works well.

Scott C
0
 
LambelAuthor Commented:
clarkscott: Thanks, I was able to link to the sharepoint list, but I need to generate the connection with VBA.  I keep getting runtime error 2147467259 "cannot connect to the Sharepoint site".  "Try again later".
I've attached my connection string.  Does this look right??
Any ideas?
Provider=Microsoft.ACE.OLEDB.12.0;
WSS;
IMEX=1;
RetrieveIds=Yes;
DATABASE=http:\\xxxwss\sites\ab\abss\dbc-asg\InternalTechSupport\ScheduledReports;
LIST={F22C98D50-1616-44E9-8E61-B0A6A3A94BD0};

Open in new window

0
 
LambelAuthor Commented:
I never got the vba connection working. I ended up linking the sharepoint list to my database file.   Thank you for your reply, clarkscott.

Lynn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now