Results 1 to 3 of 3

Update a record on a linked Sharepoint list

  1. #1
    PoolHallJunkie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    14

    Update a record on a linked Sharepoint list

    Alright, to get around the 2GB limit on Access files, I've created a sharepoint list that will house 3 pts of data and 1 or more attachments. I've gotten this code to work with local tables, however when I changed the references to the sharepoint list I keep getting this error:

    Run-time error '3175':

    Date is out of range or is in an invalid format



    on this line:

    Code:
    rst.Update
    I don't even have a date field in the list (except for the required "Created" and "Modified" fields which I can't change)

    here's the code, can anyone help me out?

    Code:
    Private Sub cmdImport_Click()
        Dim dbs As Database
        Dim rst As Recordset2
        Dim arst As Recordset2
        Dim qrst As Recordset
        Dim fld As Field2
        Dim aFile
            
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Bid Evidence")
        
        If Me.txtSCode = "" Then
        
            rst.AddNew
            rst.Fields("VRM") = Me.VRMName
            rst.Fields("Comments") = Me.txtVRMComments
            rst.Fields("Title") = "Temp"
            rst.Update
            
            Set qrst = dbs.OpenRecordset("BidEvidenceSourceID")
            rst.Fields("Title") = qrst.Fields("Sourcecode")
            Me.txtSCode = rst.Fields("Sourcecode")
            
            Set fld = rst("Attachments")
            Set arst = fld.Value
        
        On Error GoTo Proc_Error
        
            For aFile = 0 To Me.Attachments.ListCount - 1
                With arst
                    .AddNew
                    .Fields("FileData").LoadFromFile (Me.Attachments.ItemData(aFile))
                    .Update
                End With
            Next
            arst.Close
            rst.Update
            
            Set rst = Nothing
            Set dbs = Nothing
        Else
            MsgBox "Whoa There Nelly! You've already imported that one!" & vbCrLf & _
                    "Click the 'New' Button to import another file"
        End If
            
    Proc_Exit:
          Exit Sub
    Proc_Error:
          MsgBox "Error " & Err.Number & " in cmdImport.Click" & vbCrLf & Err.Description
          Resume Proc_Exit
    End Sub

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,602
    I believe that working with Sharepoint Lists from Access can only be done using Macros, VBA code is not allowed. Unfortunately, few experienced Access developers use Sharepoint or Macros, which is probably why 25 members before me read your post but didn't reply; sorry!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    ssanfu is offline VIP
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,752
    It sounds like you are storing attachments within Access.
    This is not a good idea because..... well you found out
    Attachments take up lots and lots of space.

    You should be storing a link (path) to where the attachments are stored on a drive/folder. It could be a hyperlink to go directly to the attachment when clicked.
    I don't use hyperlinks or attachments, so I am at the end pf my knowledge now.

    (I also don't use macros or Sharepoint)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 0
    Last Post: 09-03-2014, 01:37 PM
  2. Replies: 0
    Last Post: 08-28-2014, 11:45 AM
  3. Replies: 0
    Last Post: 01-17-2013, 06:43 AM
  4. Refresh Linked Sharepoint List to Show New Records
    By gopherking in forum Programming
    Replies: 1
    Last Post: 03-21-2012, 06:59 AM
  5. Replies: 2
    Last Post: 01-05-2012, 10:52 AM

Posting Permissions

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