the Sweet Smell of Burning Fur (plonq) wrote,
the Sweet Smell of Burning Fur

  • Location:
  • Mood:

A vexing problem so easily solved

I have experienced this random, recurring glitch between Excel and Access that has been driving me up the wall for months. A lot of what I do involves using MS Access to process data from various sources, which I then (try to) link to data sheets in Excel for the final processing steps. Nine times out of ten the process works flawlessly, but sometimes it will randomly decide that it is not going to work with a particular database or table.

There is no consistent rule for when it decides to fail, with the possible exception that it appears to fail more regularly on databases that are stored locally, rather than ones stored on a network share. I have researched the problem now and again, sifting through on-line help forums, but I found a fairly common theme.

1) Somebody describes a problem almost identical to what I have been experiencing. "The Excel query appears to be corrupting the table names on some of my databases."
2) A user offers up a solution that appears to have no bearing on the problem. "Maybe you need to eat more cheese."
3) First user accepts that solution. "Worked great! Thanks!!!"

I ran into the problem again today, and once again I decided to wade into the heartbreak of forum inanity to see if anyone actually had a useful solution to offer. I was about to give up again in disgust when I noticed an off-hand comment in a thread of otherwise useless advice:

I just went into Excel and selected Help-->Detect and Repair and the problem went away.

Well... shit. It worked like a damn. For some reason I thought the solution would be much more complex than that.
Tags: excel
  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.