Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    linking table (2000)

    Hi all -
    I am linking to other excel files in this database without a problem. In a new link today I link to an excel file. Everything is fine till I open up the linked table in access and change a value in a field. After I close it and open it back up, the 9 field linked table is now 2 fields. If I delete that link and try to link to that same excel file, all I see in the wizard is 2 fields now. If I open the excel file in excel, all 9 fields are still there. If I save that excel file with a new file name, I can link to it with all 9 fields again. I can go into my other linked files and change values without problems.
    I don't know if this is related or not. I get this excel file from a website. I click a button and I can save the excel file. If I try to link to this excel file I get an error that the file is missing or not in the right format when I try to link it to access. So I open the file in excel and do a save as a new name. Then I can link access to that file.
    Anyone have any ideas? Thanks for looking. John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking table (2000)

    Does the Excel file have merged cells? This might confuse Access.

    Otherwise, could you post a small demo workbook? Replace sensitive data by dummy data.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: linking table (2000)

    Hi Hans -
    Attached is a small excel spreadsheet.
    One other note - if I open the excel spreadsheet and make a change, close and save, then open the access link, the fields return. Thanks. John
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking table (2000)

    I can't explain this, but I recommend inserting a row with field names in the Excel worksheet. Then re-link the Excel table in Access, and specify that the first row contains field names. Access doesn't seem to drop fields then.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: linking table (2000)

    That fixes the problem. The other linked tables had the first row with the header names.
    I feel better now knowing that I am not crazy (or not too crazy) and you were able to duplicate the problem.
    Thank you for your help. John

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: linking table (2000)

    Well -
    I got the Excel file linked BUT when I save the new Excel file for the next day with the same name, Access cannot find the file when I click from Access on the link. I can go out and get external data again, but that seems like a long way to get what I want. Is the only way to keep Access linked to the Excel file, is to copy the new data and paste it into the linked Excel file replacing the old data? Thanks, John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking table (2000)

    As a test, I created an Excel workbook with some data, saved it and linked it in Access.
    I then started Excel again, created a new workbook with differently structured data, but the same worksheet name, and saved under the same name in the same location, allowing Excel to overwrite the original file. Access opened the linked table (with different fields) without blinking an eyelash.
    Next, I created a new workbook, entered some data and changed the worksheet name, then saved it under the same name in the same location. Now, Access refused to open the linked table, complaining that (original sheet name) was invalid.

    So try to make sure that the worksheet name doesn't change. If the problem persists, would it be feasible to copy the new data into the existing workbook?

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: linking table (2000)

    For anyone who was following this thread -
    I ran compact and repair to the access database. I have been linking to the excel file for the past week without any of the problems I was experiencing earlier. Thanks to all that help us with our access problems.

Posting Permissions

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