Results 1 to 3 of 3
  1. #1
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17

    excel table linked: can it be modified?

    I have an excel workbook, one of the sheets contains a long list of people, whose data I use in the access project. The file is linked to access via
    Code:
    doCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "LibroSoci", _
                ExcelPath & "Libro Soci.xlsm", True, "LibroSoci!"
    where LibroSoci is the sheet I am interested in. Most of the time the table is used to get info (e.g. filling a combo box in a form), but in rare occasions I would like to chenge a couple of fields in a record:

    Code:
     sql1 = "SELECT  * FROM LibroSoci WHERE [Numero tessera] = " & NTes
        Set rstLibroSoci = CurrentDb.OpenRecordset(sql1)
        rstLibroSoci.Edit
        If Month(nDate) > 8 Then
                                rstLibroSoci![Quota associativa] = Year(nDate) + 1
                            Else
                                rstLibroSoci![Quota associativa] = Year(nDate)
        End If
        
        If Me.Nuova_TessElett <> "" Then rstLibroSoci![Tessera Elettronica  n] = Me.Nuova_TessElett
        rstLibroSoci.Update
        
        rstLibroSoci.Close
        Set rstLibroSoci = Nothing



    This code does not work, complaining that the table is readonly. Maybe I am missing something basic here. Linking a table implies it being readonly? And is there any different way to do what I do? And, is the original excel workbook open to edit in Excel?
    Thanks for your patience in explaining something maybe elementary I am overlooking.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no.
    either alter the excel doc IN excel. (but you cant have it open in access at the time)
    or
    import the data from excel and work in Access. then keep importing new excel data.

  3. #3
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17
    Thanks. I read somewhere that Microsoft removed the possibility of modifying excel data from access due to some copyright reasons, starting from office 2003. I have to think of something: importing the data, modifying in access and re-export at the end can be a solution, except for the fact that the excel file might be changed in the meantime, and re-exporting the file from access might overwrite the other changes. I have to think about it. Thanks for your attention

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

Similar Threads

  1. Update a Linked Excel Table
    By PicoTTS in forum Access
    Replies: 2
    Last Post: 03-12-2020, 05:58 PM
  2. Replies: 5
    Last Post: 05-25-2016, 12:43 PM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Create a linked table from excel using VBA
    By stigmatized in forum Import/Export Data
    Replies: 2
    Last Post: 08-04-2012, 01:21 AM
  5. Excel Linked Table not Updating
    By BillH in forum Import/Export Data
    Replies: 1
    Last Post: 06-09-2011, 08:37 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
  •  
Other Forums: Microsoft Office Forums