Results 1 to 7 of 7

Linked tables from Access not updating in Excel

This is a discussion on Linked tables from Access not updating in Excel within the Excel Questions forums, part of the Question Forums category; In Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Linked tables from Access not updating in Excel

    In Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.

    When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.

    Any suggestions would be greatly appreciated! I don't know VB code or macros very well. I'm hoping to understand how this is happening so I can avoid it.

    Thanks!

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    1,465

    Default Re: Linked tables from Access not updating in Excel

    Could you provide some more data to help diagnose the problem:
    1. What type of connection are you using? ie: entire table direct from access or via Microsoft Query or ?. If its MSQuery, is the column missing when viewed in MSQuery?
    2. Is it always the same column that doesn't refresh & is it either the 1st or last column, and what data type column is it?
    3. When you delete & reimport a new set of data into Access, are you deleting the entire table or just the data rows? -ie: are you certain one of the field names hasn't changed in this process (even a spacebar at the end)?
    4. Go into Data-Properties and uncheck the box to "Preserve column layout". This should at least put the columns back in the original order when you do a refresh.

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Linked tables from Access not updating in Excel

    Thanks for the quick reply.

    1. The connection is query directly from Access. I can see all the columns and data in Access.
    2. Yes, it's always the same column and it's in the middle of my table. It's a date field.
    3. I delete the entire table in Access using a macro to import the new data. So the field isn't be renamed.
    4. I deselected Preserve column layout per your suggestion and that seems to have worked. It overwrote the formula columns I inserted in the table (added in Excel), but I could add them to the end of the table instead, I think.

    Any ideas why this is happening? Is it some type of bug? Is it because I added additional columns with formulas in Excel?

    Do you think Preserve column layout being deselected will prevent this in the future? Or how can I identify this occurs again? My files have thousands of rows and it might be difficult to notice.

    Thanks again!

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    1,465

    Default Re: Linked tables from Access not updating in Excel

    I don't think unchecking "Preserve" box will prevent future problems. This is a puzzling situation.

    One possibility is one entry in the date field is not in date format in Access. The next time it happens, (before you rename the date field in Access), go into Access and export the table/query from Access to Excel (ie: initiate it from Access so it'll be a 1-time export to a new excel workbook. Then see if the date column exported into excel and if it did, examine the data in the date field using sorts and autofilters to see if any cell entry is not being treated as a date in excel. If it does not export to excel properly, then the problem most likely resides in the Access data.

    Another remote possibility is the Data-Connection in Excel or maybe the entire workbook in excel has a minor corruption that's causing the problem. I've experience this before where the connection doesn't work properly (but it wasn't an intermittent problem). My fix in those instances was to create a new workbook and recreate the connection from scratch. If it happens again, (before you rename the date field in Access), create a new workbook connection and see it the date field comes over. If it comes over, your original workbook is corrupted. If it doesn't come over, I'd suspect something is wrong with at least one date in the date column.

    One final thought, you mentioned by renaming the field name in the Access query fixes the problem. What was it originally named and what new name did you give it?

  5. #5
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Linked tables from Access not updating in Excel

    The column that’s not refreshing is called “Complete Date without Time”. It’s an expression that uses “Complete Date” (this field has date and time, but I use the expression to get only the date). The “Complete Date” has a criteria where I adjust the date range each month (for example, I use Between #12/1/2011# to #12/31/2011).



    Here’s the formula: Complete Date without Time: Format$([Table].[Complete Date],'mm/dd/yyyy')


    I tried your suggestions today. I exported the query from Access to Excel and can see the “Complete Date without Time” looks like text when I use autofilter (it lists each date individually instead of grouping by month). All rows were populated though.



    I also started a new workbook. Everything looks good so far. I tried replicating the issue by deleting and reimporting different data files into Access, then refreshed the table in Excel. I tried adjusting the date criteria to see if that triggers it. That field is still refreshing properly. You’re probably right, my file is probably corrupt. I wish I knew what was causing it to become corrupt. Maybe the expression being exported and/or the conditions in the query being adjusted caused the issue… I wish I knew so I could avoid on the new file I create.


    When I renamed the field, I just added a 2 behind the field name (“Complete Date without Time2).

  6. #6
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    1,465

    Default Re: Linked tables from Access not updating in Excel

    If the file became corrupt, it's likely you'll never figure out why. I've encounted corrupt files a number of times and when I recreated it in a new workbook the problem did not recur.

    I don't see any reason your formula would cause the problem. I'd suspect a corrupt file as the most likely culprit.

    One other thought - I've had situations where a data refresh worked perfectly for several years and then suddenly it stopped working, but if I copied the file to another computer the data refresh worked normally. With the help of our IT person, we finally discovered it was due to updates Microsoft made to Windows and Office 2007. However, I never encountered one that occurred intermittently.

  7. #7
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Linked tables from Access not updating in Excel

    Thanks for your help, this helped me learn a few things even if I don't know exactly why it's happening. At least I know to watch for it. Thanks again!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com