Results 1 to 6 of 6
  1. #1
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128

    Link to Excel; number of records change and Excel can't update

    I have created links from Excel to Access to allow data to update automatically (yes, I have to use Excel for my "reports" because this is what our customers only know how to use). One of the tables which is created is a list of color options, which you can see below.




    The problem is that, as you can see, we also will then discontinue a particular color. But when Excel goes to refresh the table which is linked . . .


    I get the following error:


    The same error takes place if I attempt to add a color to the list we offer.

    Is there some way to link the data, but write code that allows for the insertion of rows/deletion of rows if the size of the table changes?

    Thanks!!
    Last edited by crobaseball; 03-22-2014 at 07:59 PM. Reason: extra image

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you link to an Excel Workbook or a Worksheet in a workbook, you will not be able to edit the workbook.

  3. #3
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Quote Originally Posted by ItsMe View Post
    If you link to an Excel Workbook or a Worksheet in a workbook, you will not be able to edit the workbook.
    I'm a little confused. I can edit the workbook. The data can also update. But I can't add or delete records from the query. Is this what you mean?

    If that IS what you mean, I have to imagine there's a way to work with this, whether with VBA code which adds or deletes rows, or by some other means. Thoughts?

  4. #4
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    I just figured it out. For anyone else looking for this answer: (in Excel 2010) select the table which the link has created. Under the Data tab, click "Properties". It will have 3 options. You have to select "Insert entire rows for new data; clear unused cells". This option does not come up when you are creating the link, so it's an extra step you have to perform to keep new records refreshing correctly.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am not sure what it is you are working with or what the objective is. Post #2 is a simple statement about linking to files. I took another look at post #1 and that first screenshot is not a linked excel file.

    Perhaps your objective is related to Importing data, using Excel to Import from an Access table to an Excel spreadsheet.

  6. #6
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Quote Originally Posted by ItsMe View Post
    I am not sure what it is you are working with or what the objective is. Post #2 is a simple statement about linking to files. I took another look at post #1 and that first screenshot is not a linked excel file.

    Perhaps your objective is related to Importing data, using Excel to Import from an Access table to an Excel spreadsheet.
    You're absolutely right, I was referring to an Excel worksheet which imported and was linked to an Access query. I didn't make that clear; sorry about that! Like I said, the way to have the data update the way I was looking for was under the preferences in Excel.

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

Similar Threads

  1. Update records on only ONE FIELD from excel data?
    By stildawn in forum Import/Export Data
    Replies: 3
    Last Post: 11-19-2013, 04:55 PM
  2. Link excel to access
    By medaccess in forum Programming
    Replies: 7
    Last Post: 04-28-2013, 07:03 PM
  3. Replies: 5
    Last Post: 03-13-2013, 02:11 PM
  4. Nz function and link to Excel
    By thart21 in forum Queries
    Replies: 5
    Last Post: 06-23-2011, 10:48 AM
  5. link to excel with chart
    By rickscr in forum Programming
    Replies: 8
    Last Post: 04-25-2011, 06:22 PM

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